Monday, December 08, 2008

Create Insert Command for SQL tables

declare @columnnames nvarchar(4000) , @insertcommand nvarchar(4000) , @tablename nvarchar(100) ,@str1 nvarchar(1000), @tmptablename nvarchar(100), @whereclause nvarchar(1000)
set @columnnames = ''
set @tablename = 'planresource'
set @whereclause = ' where planid in (select planid from [plan] where planid >= 1500) '

set @tmptablename = '##tmp__'+@tablename
set @str1 = 'select * into '+@tmptablename+' from ['+@tablename+'] '+@whereclause
--print @str1
exec sp_executesql @str1
set @str1 = 'alter table '+@tmptablename+' add insertcommand nvarchar(3000)'
exec sp_executesql @str1



select @columnnames = @columnnames + '['+column_name+'], ' from information_schema.columns where table_name = @tablename
--print @columnnames
--print left(@columnnames , len(@columnnames)-1)
set @columnnames = left(@columnnames , len(@columnnames)-1)
--print @columnnames
set @insertcommand = 'insert into ['+@tablename+'] ('+@columnnames+') values ('
--print @insertcommand


set @str1 = 'update '+@tmptablename+' set insertcommand = '''+@insertcommand +''''
--print @str1
exec sp_executesql @str1


declare @sqlstring2 nvarchar(4000), @colname nvarchar(100)
set @sqlstring2 = 'update '+@tmptablename+' set insertcommand = insertcommand + '''
DECLARE tmptable_name CURSOR FOR select column_name from information_schema.columns where table_name = @tablename
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @colname

WHILE @@FETCH_STATUS = 0
BEGIN
--set @sqlstring2 = @sqlstring2 + ' convert(nvarchar(1000),isnull(replace(['+@colname+'],'''''''',''''''''''''),''''))+'''''')'' '
set @sqlstring2 = @sqlstring2 + '''''''+ convert(nvarchar(1000),isnull(replace(['+@colname+'],'''''''',''''''''''''),''''))+'''''', '
FETCH NEXT FROM tmptable_name INTO @colname
end
--print @sqlstring2
CLOSE tmptable_name
DEALLOCATE tmptable_name
set @sqlstring2 = left(@sqlstring2 , len(@sqlstring2)-1)+')'''
--print @sqlstring2
exec sp_executesql @sqlstring2

--select insertcommand from ##tmp__landingpage
--select insertcommand from #tmp




declare @string3 nvarchar(1000)
set @string3 =
'
declare @sqlstring nvarchar(4000)
DECLARE tmptable_name CURSOR FOR select insertcommand from '+@tmptablename+'
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @sqlstring

WHILE @@FETCH_STATUS = 0
BEGIN
print @sqlstring
print ''go''
FETCH NEXT FROM tmptable_name INTO @sqlstring
end

CLOSE tmptable_name
DEALLOCATE tmptable_name
'

--print @string3
exec sp_executesql @string3


set @string3 = 'drop table '+@tmptablename
exec sp_executesql @string3

No comments: