Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Создание временной таблицы по образу и подобию постоянной  [new]
Asid_new
Member

Откуда: Москва
Сообщений: 52
Господа, доброго времени суток!
Подскажите плз, каким образом создать временную таблицу со структурой, аналогичной имеющейся постоянной таблице. Просто в рамках хранимой процедуры происходит обработка файлов, содержащих выгруженные различные формы отчетности, которые раскладываются в соответствующие им таблицы БД. А мне необходимо еще выполнить кое-какие преобразования над ключевыми полями. Поэтому без временных таблиц - никак! А городить в БД для каждой таблицы аналогичную "временную" очень не хочется.
Заранее благодарен!
27 окт 13, 15:38    [15037685]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6254
select top 0 * into #tmp from mytable
27 окт 13, 16:28    [15037750]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
komrad
Member

Откуда:
Сообщений: 5910
Сон Веры Павловны
select top 0 * into #tmp from mytable


либо

select * into #tmp from mytable where 1=0
27 окт 13, 21:28    [15038561]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
imato
Member

Откуда: Москва
Сообщений: 80
Привет.
Если нужно сложнее, то предлагаю две процедурки.

1. Определение таблицы

ALTER proc [archiv].[prc_table_body_difinition]
@db varchar(255),
@schema varchar(255),
@table varchar(255),
@difinition varchar(max) out

as

/* test
declare @difinition varchar(max)
exec archiv.prc_table_body_difinition 'dwh', 'dbo', 'dim_card', @difinition out
print @difinition
*/

set nocount on
select @difinition=''

declare @table_full_name varchar(2000) = '['+@db+'].['+@schema+'].['+@table+']' ,
@com varchar(max) = ''

if (select object_id(@table_full_name)) is not null
begin

select @com=
'
declare @columns varchar(max) = ''
(''
, @difinition varchar(max) = ''create table ''+'''+@table_full_name+'''

select @columns=@columns+c.name+'' ''+t.name
+case
when c.collation_name is not null then ''(''+cast(c.max_length as varchar)+'')''
when c.collation_name is null and t.scale=0 then ''''
when c.collation_name is null and t.scale>0 then ''(''+cast(c.precision as varchar)+'', ''+cast(c.scale as varchar)+'')''
else cast(c.precision as varchar) end
+case when c.is_nullable=0 then '' not null'' else '' null'' end
+'',
''
from ['+@db+'].[sys].[columns] c
join ['+@db+'].[sys].types t on t.system_type_id=c.system_type_id
where c.[object_id]=object_id('''+@table_full_name+''')
and c.is_computed=0
order by c.column_id

select @columns = left(@columns, len(@columns)-4)+'')''

select @difinition=@difinition + @columns +'';''
select @difinition'

exec dbo.prc_variable_from_exec @com, @difinition out

end

2. Создание индексов

ALTER proc [archiv].[prc_table_index_difinition]
@db varchar(255),
@schema varchar(255),
@table varchar(255),
@key_only bit = 0,
@cluster_only bit = 0,
@difinition varchar(max) out

as

/* test
declare @difinition varchar(max)
exec archiv.prc_table_index_difinition 'dwh', 'dbo', 'dim_product', @difinition out
print @difinition
*/

set nocount on
select @difinition=''
declare @table_full_name varchar(2000) = '['+@db+'].['+@schema+'].['+@table+']' ,
@com varchar(max) = ''

if (select object_id(@table_full_name)) is not null
begin

select @com=
'
declare @difinition varchar(max) = ''''

select identity(int, 1, 1) as id,
i.name, i.type, i.is_unique, i.is_primary_key, i.fill_factor,
tc.name column_name, c.index_column_id, c.key_ordinal, c.is_descending_key, c.is_included_column
into #dif
from ['+@db+'].[sys].[indexes] i
join ['+@db+'].[sys].[index_columns] c
on c.[object_id]=i.[object_id]
and c.[index_id]=i.[index_id]
join ['+@db+'].[sys].[columns] tc
on tc.object_id=i.object_id
and c.column_id=tc.column_id
where i.[object_id]=object_id('''+@table_full_name+''')
and (i.type_desc=''CLUSTERED''
or c.partition_ordinal=0)
order by i.index_id, c.index_column_id

create table #columns
(name varchar(255),
columns varchar(max))

create table #columns_included
(name varchar(255),
columns varchar(max))

declare @i int = 1

while @i<=(select max(id) from #dif)
begin

-- columns
if (select name from #columns where name=(select name from #dif where id=@i)) is null
insert into #columns
select name, ''(''+column_name+'',''
from #dif
where id=@i
else
update c
set c.columns=columns+d.column_name
+case when d.index_column_id=1 then '' desc '' else '''' end
+'',''
from #dif d
join #columns c on c.name=d.name
where id=@i
and d.is_included_column=0

-- columns included
if (select name from #columns_included where name=(select name from #dif where id=@i)) is null
insert into #columns_included
select name, ''(''+column_name+'',''
from #dif d
where id=@i
and d.is_included_column=1
else
update c
set c.columns=columns+d.column_name
+case when d.index_column_id=1 then '' desc '' else '''' end
+'',''
from #dif d
join #columns_included c on c.name=d.name
where id=@i
and d.is_included_column=1

set @i=@i+1

end

update #columns
set columns=left(columns, len(columns)-1)+'')''

update #columns_included
set columns=left(columns, len(columns)-1)+'')''


select @difinition=@difinition+
''alter table ''+'''+@table_full_name+'''+'' add constraint ''+t.name+'' primary key''
+case when t.type=1 then '' clustered '' when t.type=2 then '' nonclustered '' end
+'' ''+c.columns+'';
''
from
(select distinct name, type, is_primary_key, is_unique
from #dif) t
join #columns c on c.name=t.name
left join #columns_included ci on ci.name=t.name
where t.is_primary_key=1
and
(0='+cast(@cluster_only as varchar)+'
or t.type='+cast(@cluster_only as varchar)+')


select @difinition=@difinition+
''create ''
+case when t.is_unique=1 then '' unique '' else '''' end
+case when t.type=1 then '' clustered '' when t.type=2 then '' nonclustered '' end
+'' index ''+t.name+'' on ''+'''+@table_full_name+'''
+'' ''+c.columns+isnull('' include '' +ci.columns, '''')+'';
''
from
(select distinct name, type, is_primary_key, is_unique
from #dif) t
join #columns c on c.name=t.name
left join #columns_included ci on ci.name=t.name
where t.is_primary_key=0
and 0='+cast(@key_only as varchar)+'
and
(0='+cast(@cluster_only as varchar)+'
or t.type='+cast(@cluster_only as varchar)+')

drop table #dif
drop table #columns
drop table #columns_included

select @difinition
'
exec dbo.prc_variable_from_exec @com, @difinition out

end
28 окт 13, 09:40    [15039645]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
Гость333
Member

Откуда:
Сообщений: 3683
imato
предлагаю две процедурки

Коли предлагаете — так хоть опишите, что они делают.

imato
@difinition
...
'['+@db+'].['+@schema+'].['+@table+']'

Это печально. Дальше не читал.
28 окт 13, 11:41    [15040227]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
Glory
Member

Откуда:
Сообщений: 104751
Asid_new
А мне необходимо еще выполнить кое-какие преобразования над ключевыми полями. Поэтому без временных таблиц - никак!

А зачем во временной таблицы нужны все поля, если преобразования будут только над некоторыми из них ?
28 окт 13, 11:53    [15040290]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
imato
Привет.
Если нужно сложнее, то предлагаю две процедурки.

1. Определение таблицы

+ archiv.prc_table_body_difinition
ALTER proc [archiv].[prc_table_body_difinition]
@db varchar(255),
@schema varchar(255),
@table varchar(255),
@difinition varchar(max) out

as

/* test
declare @difinition varchar(max) 
exec archiv.prc_table_body_difinition 'dwh', 'dbo', 'dim_card', @difinition out
print @difinition
*/

set nocount on
select @difinition=''

declare @table_full_name varchar(2000) = '['+@db+'].['+@schema+'].['+@table+']' ,
	@com varchar(max) = ''
	
if (select object_id(@table_full_name)) is not null
begin

	select @com=
		'
	declare @columns varchar(max) = ''
(''		
		, @difinition varchar(max) = ''create table ''+'''+@table_full_name+'''

	select @columns=@columns+c.name+'' ''+t.name
		+case 
			when c.collation_name is not null then ''(''+cast(c.max_length as varchar)+'')''
			when c.collation_name is null and t.scale=0 then ''''
			when c.collation_name is null and t.scale>0 then ''(''+cast(c.precision as varchar)+'', ''+cast(c.scale as varchar)+'')''
			else cast(c.precision as varchar) end
		+case when c.is_nullable=0 then '' not null'' else '' null'' end
		+'', 
''
	from ['+@db+'].[sys].[columns] c
	join ['+@db+'].[sys].types t on t.system_type_id=c.system_type_id
	where c.[object_id]=object_id('''+@table_full_name+''')
		and c.is_computed=0
	order by c.column_id

	select @columns = left(@columns, len(@columns)-4)+'')''

	select @difinition=@difinition + @columns +'';''	
	select @difinition'

	exec dbo.prc_variable_from_exec @com, @difinition out

end


2. Создание индексов

+ archiv.prc_table_index_difinition
ALTER proc [archiv].[prc_table_index_difinition]
@db varchar(255),
@schema varchar(255),
@table varchar(255),
@key_only bit = 0,
@cluster_only bit = 0,
@difinition varchar(max) out

as

/* test
declare @difinition varchar(max) 
exec archiv.prc_table_index_difinition 'dwh', 'dbo', 'dim_product', @difinition out
print @difinition
*/

set nocount on
select @difinition=''
declare @table_full_name varchar(2000) = '['+@db+'].['+@schema+'].['+@table+']' ,
	@com varchar(max) = ''
	
if (select object_id(@table_full_name)) is not null
begin

	select @com=
	'
	declare @difinition varchar(max) = ''''

	select identity(int, 1, 1) as id, 
		i.name, i.type, i.is_unique, i.is_primary_key, i.fill_factor,
		tc.name column_name, c.index_column_id, c.key_ordinal, c.is_descending_key, c.is_included_column
	into #dif
	from ['+@db+'].[sys].[indexes] i
	join ['+@db+'].[sys].[index_columns] c 
		on c.[object_id]=i.[object_id]
		and c.[index_id]=i.[index_id]
	join ['+@db+'].[sys].[columns] tc
		on tc.object_id=i.object_id
		and c.column_id=tc.column_id
	where i.[object_id]=object_id('''+@table_full_name+''')
		and (i.type_desc=''CLUSTERED''
			or c.partition_ordinal=0)
	order by i.index_id, c.index_column_id

	create table #columns
	(name varchar(255),
	columns varchar(max))

	create table #columns_included
	(name varchar(255),
	columns varchar(max))

	declare @i int = 1

	while @i<=(select max(id) from #dif)
	begin

		-- columns
		if (select name from #columns where name=(select name from #dif where id=@i)) is null
		insert into #columns
		select name, ''(''+column_name+'',''
		from #dif
		where id=@i
		else 
		update c
		set c.columns=columns+d.column_name
			+case when d.index_column_id=1 then '' desc '' else '''' end
			+'',''
		from #dif d
		join #columns c on c.name=d.name
		where id=@i 
			and d.is_included_column=0

		-- columns included
		if (select name from #columns_included where name=(select name from #dif where id=@i)) is null
		insert into #columns_included
		select name, ''(''+column_name+'',''
		from #dif d
		where id=@i
			and d.is_included_column=1
		else 
		update c
		set c.columns=columns+d.column_name
			+case when d.index_column_id=1 then '' desc '' else '''' end
			+'',''
		from #dif d
		join #columns_included c on c.name=d.name
		where id=@i 
			and d.is_included_column=1

		set @i=@i+1

	end

	update #columns
	set columns=left(columns, len(columns)-1)+'')''

	update #columns_included
	set columns=left(columns, len(columns)-1)+'')''


	select @difinition=@difinition+ 
		''alter table ''+'''+@table_full_name+'''+'' add constraint ''+t.name+'' primary key''
		+case when t.type=1 then '' clustered '' when t.type=2 then '' nonclustered '' end
		+'' ''+c.columns+'';
''
	from 
		(select distinct name, type, is_primary_key, is_unique
		from #dif) t
	join #columns c on c.name=t.name
	left join #columns_included ci on ci.name=t.name
	where t.is_primary_key=1
		and 
			(0='+cast(@cluster_only as varchar)+'
			or t.type='+cast(@cluster_only as varchar)+')


	select @difinition=@difinition+ 
		''create ''
		+case when t.is_unique=1 then '' unique '' else '''' end
		+case when t.type=1 then '' clustered '' when t.type=2 then '' nonclustered '' end
		+'' index ''+t.name+'' on ''+'''+@table_full_name+'''
		+'' ''+c.columns+isnull('' include '' +ci.columns, '''')+'';
''
	from 
		(select distinct name, type, is_primary_key, is_unique
		from #dif) t
	join #columns c on c.name=t.name
	left join #columns_included ci on ci.name=t.name
	where t.is_primary_key=0
		and 0='+cast(@key_only as varchar)+'
		and 
			(0='+cast(@cluster_only as varchar)+'
			or t.type='+cast(@cluster_only as varchar)+')

	drop table #dif
	drop table #columns
	drop table #columns_included

	select @difinition
	'
	exec dbo.prc_variable_from_exec @com, @difinition out

end
Оформляйте правильно свой ... пост.
28 окт 13, 14:07    [15041205]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
Asid_new
Member

Откуда: Москва
Сообщений: 52
Коллеги, всем большое спасибо! Мой мозг немного потерялся, поэтому Ваши советы были весьма полезными. GLORY, потому что данные вставляются с помощью Bulk Insert, а он для корректной работы требует соответстви структур файла и таблицы.
28 окт 13, 22:32    [15043431]     Ответить | Цитировать Сообщить модератору
 Re: Создание временной таблицы по образу и подобию постоянной  [new]
Glory
Member

Откуда:
Сообщений: 104751
Asid_new
GLORY, потому что данные вставляются с помощью Bulk Insert, а он для корректной работы требует соответстви структур файла и таблицы.

Откуда вылез bulk insert ?
Вы заявили, что надо "создать временную таблицу со структурой, аналогичной имеющейся постоянной таблице". Но "необходимо еще выполнить кое-какие преобразования над ключевыми полями"
Мой вопрос - зачем создавать полный аналог неизвестной структуты, если работать надо только с несколькими полями ?
29 окт 13, 10:25    [15044974]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить