Technical Article

Insertion Query Creation of a Table in Milliseconds

,

Just paste the script into the analyzer and rename the '[HERE_IS_THE_NAME_OF_THE_TABLE_YOU_WANT]' part as the name of the table and execute it in text output mode. Copy the output and paste it again into the analyzer and execute the creation script. That's it. Be careful, you may need to exclude the id column manually (if it is an identity counter column).

declare @tabloIsim varchar(50)
set @tabloIsim = '[HERE_IS_THE_NAME_OF_THE_TABLE_YOU_WANT]'
declare @gecici nvarchar(255)
declare @sutunlar1 nvarchar(4000),
@sutunlar2 nvarchar(4000)

declare @sutunIsim nvarchar(100)
,@sutunBoyut nvarchar(100)
,@sutunCins nvarchar(100)

declare sutunCatal cursor for
select sc.name,
case st.name
when 'int' then ''
when 'tinyint' then ''
when 'datetime' then ''
else '('+cast(sc.length as nvarchar(100))+')' end
,
st.name
from syscolumns sc
inner join systypes st on sc.xtype = st.xtype
where id = object_id(@tabloIsim)
order by sc.colorder

set @sutunlar1 = ''
set @sutunlar2 = ''
OPEN sutunCatal
fetch next from sutunCatal into @sutunIsim,@sutunBoyut,@sutunCins

print 'CREATE PROC ins_'+@tabloIsim

while @@fetch_status = 0
BEGIN 
set @gecici = '@'+@sutunIsim + ' '+@sutunCins+@sutunBoyut
set @sutunlar2 = @sutunlar2 + '@'+@sutunIsim
set @sutunlar1 = @sutunlar1 + @sutunIsim
fetch next from sutunCatal into @sutunIsim,@sutunBoyut,@sutunCins
if @@fetch_status = 0
BEGIN
set @gecici = @gecici +','
set @sutunlar1 = @sutunlar1 + ',
'
set @sutunlar2 = @sutunlar2 + ',
'
END
print @gecici
END

close sutunCatal
deallocate sutunCatal

print 'AS'
print 'insert into ' + @tabloIsim + '('+@sutunlar1+') values ('+@sutunlar2+')'
print 'GO'

Rate

2.9 (20)

You rated this post out of 5. Change rating

Share

Share

Rate

2.9 (20)

You rated this post out of 5. Change rating