Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
SELECT
@tableName
declare @tableName varchar(max) ,@targetDatabase varchar(max) ,@sql varchar(max) ,@hasIdentityColumn bit -- be sure the table name is in the format select -- [schema].[table], including the brackets! @tableName = '[dbo].[dwr_subreport_config]' ,@targetDatabase = 'smd_webreports' ,@sql = '' ,@hasIdentityColumn = 0 if exists ( select 2 from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id inner join sys.columns c on o.object_id = c.object_id where 1=1 and c.is_identity = 1 and '[' + s.name + '].[' + o.name + ']' = @tableName ) set @hasIdentityColumn = 1 if @hasIdentityColumn = 1 set @sql = @sql + 'select n = 1, sql = ''set identity_insert ' + @tableName + ' on''' + char(13) select @sql = @sql + 'union select n = 2, sql = ''insert into ' + @tableName + ' (' ;with MyColumns as ( select c.name ,c.column_id ,TypeName = t.name ,IsIdentity = is_identity ,AddTicks = case t.name when 'varchar' then 1 when 'nvarchar' then 1 when 'datetime' then 1 when 'smalldatetime' then 1 when 'xml' then 1 else 0 end from sys.objects o inner join sys.columns c on o.object_id = c.object_id inner join sys.schemas s on o.schema_id = s.schema_id inner join sys.types t on c.user_type_id = t.user_type_id where 1=1 and '[' + s.name + '].[' + o.name + ']' = @tableName ) select @sql = @sql + case column_id when 1 then '[' else ', [' end + name + ']' from MyColumns order by column_id select @sql = @sql + ') values (''' ;with MyColumns as ( select c.name ,c.column_id ,TypeName = t.name ,IsIdentity = is_identity ,AddTicks = case t.name when 'varchar' then 1 when 'nvarchar' then 1 when 'datetime' then 1 when 'smalldatetime' then 1 when 'xml' then 1 else 0 end from sys.objects o inner join sys.columns c on o.object_id = c.object_id inner join sys.schemas s on o.schema_id = s.schema_id inner join sys.types t on c.user_type_id = t.user_type_id where 1=1 and '[' + s.name + '].[' + o.name + ']' = @tableName ) select @sql = @sql + case column_id when 1 then '' else ' + '', ''' end + case when AddTicks=1 then ' + coalesce(' + case when AddTicks=1 then ''''''''' + ' else '' end + 'convert(varchar(max), ' + case when AddTicks=1 then 'replace(' else '' end + name + case when AddTicks=1 then ', '''''''', '''''''''''')' else '' end + ')' + case when AddTicks=1 then ' + ''''''''' else '' end + ', ''NULL'')' else ' + coalesce(convert(varchar(max), ' + name + '), ''null'')' end from MyColumns order by column_id select @sql = @sql + ' + '')'' from ' + @tableName + ' /* where */' + char(13) if @hasIdentityColumn = 1 set @sql = @sql + 'union select n = 3, sql = ''set identity_insert ' + @tableName + ' off''' + char(13) set @sql = @sql + 'order by 1' print @sql
WHERE
INSERT
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.