January 20, 2008 at 2:21 am
Comments posted to this topic are about the item Generate INSERT for table with IDENTITY column
March 7, 2008 at 6:44 am
Won't be easy to exclude identity column from insert statement?
existing table T1 (ID [int] IDENTITY(1,1) NOT NULL,
A1 varchar(50),A2 varchar(50),A3 varchar(50)......)
insert into T1 (A1,A2,A3,...)
select B1,B2,B3,... from T2
March 7, 2008 at 7:27 am
Displaying help information about the stored procedure when someone passes in 'help' as the first parameter is a nice touch.
March 7, 2008 at 8:38 am
This didn't work for me initially. It duplicated a column four times. I had to modify the code as below:
Original line:
WHERE O1.Name = @Target_Table) AS C1
New line:
WHERE O1.Name = @Target_Table and T1.Name <> 'sysname') AS C1
Original line:
WHERE O2.Name = @Source_Table) AS C2
New line:
WHERE O2.Name = @Source_Table and T2.Name <> 'sysname') AS C2
With those changes it worked. Will be a time-saver. 'Message' was the column name that was duplicated.
Thanks
March 7, 2008 at 8:50 am
This works great if you know the columns and it's only one table. Next task is to dynamically generate the insert and select statements dynamically from the columns of any given table so that we can generate the insert command for any number of tables while in a loop. Using 5 separate stored procedures to start the insert, concatenate the insert, end the insert and start the select, concatenate the select and end the select we can read the column list into a temporary table then loop through the columns and build the command.
March 20, 2008 at 11:11 am
Reginald J Ray Jr (3/7/2008)
This didn't work for me initially. It duplicated a column four times. I had to modify the code as below:Original line:
WHERE O1.Name = @Target_Table) AS C1
New line:
WHERE O1.Name = @Target_Table and T1.Name <> 'sysname') AS C1
Original line:
WHERE O2.Name = @Source_Table) AS C2
New line:
WHERE O2.Name = @Source_Table and T2.Name <> 'sysname') AS C2
With those changes it worked. Will be a time-saver. 'Message' was the column name that was duplicated.
Thanks
Yes, SQL Server will make multiple entries in SysColumns for some columns, causing the duplicate lines in the generated script. I put your fix in place, along with a "DISTINCT" clause on the subqueries. Thanks for your response!
May 22, 2012 at 12:55 pm
Here is a more polished version of the script, enhancements: bracket column names to allow for spaces, add ability to tack on a prefix - this lets you generate a script for doing inserts from another server, add a GO to the output so you can monitor progress when running a large batch, add handling for table names passed in with brackets and [dbo]. which is the case when using sp_MSforeachtable. Only generate enable identity_insert statements IF the table actually has an identity column.
--Example Call: For every table generate a truncate/insert
exec sp_MSforeachtable @command1="exec [spd_Build_Insert_Into_Stmt_With_Identity] '?','?','[LINKED_SERVER_NAME].DATABASENAME.'"
Create PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]
@Source_Table varchar(200),-- req'd; the name of the source table
@Target_Table varchar(200) = '',-- req'd; the name of the target table
@FromPrefix varchar(500)='',-- String that will be added to the from statement, this lets you use linked server data sources
@Print_Truncate char(1) = 'Y',-- optional; 'Y' to print truncate statement
@Print_Identity_Insert_Stmts char(1) = 'Y',-- optional; 'Y' to include the SET IDENTITY_INSERT
-- statements in the output
@Show_Matchless_Columns char(1) = 'N',-- optional; 'Y' to include the columns from each
-- table that have no match in the other table (if
-- the tables have slightly different structures);
-- these columns will be commented-out in output
@AddGo char(1) = 'Y'-- Add a GO to the end which will allow you to view progress when a batch is running
AS
IF @Source_Table = 'help'
BEGIN
PRINT 'PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]'
PRINT '@Source_Table varchar(200),-- required; the name of the source table '
PRINT '@Target_Table varchar(200) = '''',-- required; the name of the target table '
PRINT ' @FromPrefix varchar(500)='''',-- String that will be added to the from statement, this lets you use linked server data sources '
PRINT ' @Print_Truncate char(1) = ''Y'',-- optional; ''Y'' to print truncate statement '
PRINT '@Print_Identity_Insert_Stmts char(1) = ''Y'',-- optional; ''Y'' to include the SET IDENTITY_INSERT '
PRINT '-- statements in the output'
PRINT '@Show_Matchless_Columns char(1) = ''N''-- optional; ''Y'' to include the columns from each '
PRINT '-- table that have no match in the other table (if '
PRINT '-- the tables have slightly different structures);'
PRINT '-- these columns will be commented-out in output'
PRINT ' @AddGo char(1) = ''Y''-- Add a GO to the end which will allow you to view progress when a batch is running'
RETURN
END
DECLARE @sql_into varchar(8000)
DECLARE @sql_from varchar(8000)
DECLARE @sql varchar(8000)
DECLARE @crlf char(2)
SET @crlf = CHAR(13) + CHAR(10)
DECLARE @indent char(1)
SET @indent = CHAR(9)
DECLARE @ColName1 varchar(250)
DECLARE @ColOrder1 smallint
DECLARE @ColType1 varchar(50)
DECLARE @ColLength1 smallint
DECLARE @ColPrec1 smallint
DECLARE @ColScale1 smallint
DECLARE @IsNullable1 tinyint
DECLARE @ColName2 varchar(250)
DECLARE @ColOrder2 smallint
DECLARE @ColType2 varchar(50)
DECLARE @ColLength2 smallint
DECLARE @ColPrec2 smallint
DECLARE @ColScale2 smallint
DECLARE @IsNullable2 tinyint
DECLARE @OrderBy decimal(9,1)
DECLARE Table_Struct_Compare_Cursor CURSOR FOR
SELECT
ColName1,
ColOrder1,
ColType1,
ColLength1,
ColPrec1,
ColScale1,
IsNullable1,
ColName2,
ColOrder2,
ColType2,
ColLength2,
ColPrec2,
ColScale2,
IsNullable2,
OrderBy = ISNULL(CONVERT(decimal(9,1), ColOrder1), CONVERT(decimal(9,1), ColOrder2) + 0.5)
FROM
(SELECT DISTINCT
ColName1 = C.Name,
ColOrder1 = C.ColOrder,
ColType1 = T1.Name,
ColLength1 = C.Length,
ColPrec1 = C.XPrec,
ColScale1 = C.XScale,
IsNullable1 = C.IsNullable
FROM SysColumns C
JOIN SysObjects O1 ON O1.Id = C.Id
JOIN SysTypes T1 ON T1.XType = C.XType
WHERE O1.Name = REPLACE(REPLACE(REPLACE(@Target_Table,'[dbo].',''),'[',''),']','') and T1.Name <> 'sysname') AS C1
FULL OUTER JOIN
(SELECT DISTINCT
ColName2 = C.Name,
ColOrder2 = C.ColOrder,
ColType2 = T2.Name,
ColLength2 = C.Length,
ColPrec2 = C.XPrec,
ColScale2 = C.XScale,
IsNullable2 = C.IsNullable
FROM SysColumns C
JOIN SysObjects O2 ON O2.Id = C.Id
JOIN SysTypes T2 ON T2.XType = C.XType
WHERE O2.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']','') and T2.Name <> 'sysname') AS C2
ON C2.ColName2 = C1.ColName1
ORDER BY OrderBy, ColOrder1, ColOrder2
-- init vars:
SET @sql_into = 'INSERT INTO ' + @Target_Table + ' ('
SET @sql_from = 'SELECT '
SET @sql = ''
OPEN Table_Struct_Compare_Cursor
FETCH NEXT FROM Table_Struct_Compare_Cursor INTO
@ColName1,
@ColOrder1,
@ColType1,
@ColLength1,
@ColPrec1,
@ColScale1,
@IsNullable1,
@ColName2,
@ColOrder2,
@ColType2,
@ColLength2,
@ColPrec2,
@ColScale2,
@IsNullable2,
@OrderBy
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColName1 IS NULL
BEGIN
IF @Show_Matchless_Columns = 'Y'
BEGIN
--SET @sql_into = @sql_into + @crlf + '--' + @indent + '<no matching column in ' + @Target_Table + ' for column ' + @ColName2 + '>,'
SET @sql_into = @sql_into + @crlf + '--' + @indent + '<missing:[' + @ColName2 + ']>,'
SET @sql_from = @sql_from + @crlf + '--' + @indent + '<' + @Source_Table + '.[' + @ColName2 + ']>,'
END
END
ELSE IF @ColName2 IS NULL
BEGIN
IF @Show_Matchless_Columns = 'Y'
BEGIN
SET @sql_into = @sql_into + @crlf + '--' + @indent + '<' + @Target_Table + '.[' + @ColName1 + ']>,'
--SET @sql_from = @sql_from + @crlf + '--' + @indent + '<no matching column in ' + @Source_Table + ' for column ' + @ColName1 + '>,'
SET @sql_from = @sql_from + @crlf + '--' + @indent + '<missing: [' + @ColName1 + ']>,'
END
END
ELSE IF @ColType1 <> @ColType2
BEGIN
SET @sql_into = @sql_into + @crlf + @indent + '[' + @ColName1 + '],'
SET @sql_from = @sql_from + @crlf + @indent + 'CONVERT(' + '[' + @ColType1 + '], [' + @ColName2 + ']),'
END
ELSE
BEGIN
SET @sql_into = @sql_into + @crlf + @indent + '[' + @ColName1 + '],'
SET @sql_from = @sql_from + @crlf + @indent + '[' + @ColName2 + '],'
END
FETCH NEXT FROM Table_Struct_Compare_Cursor INTO
@ColName1,
@ColOrder1,
@ColType1,
@ColLength1,
@ColPrec1,
@ColScale1,
@IsNullable1,
@ColName2,
@ColOrder2,
@ColType2,
@ColLength2,
@ColPrec2,
@ColScale2,
@IsNullable2,
@OrderBy
END
CLOSE Table_Struct_Compare_Cursor
DEALLOCATE Table_Struct_Compare_Cursor
SET @sql_into = LEFT(@sql_into, LEN(RTRIM(@sql_into)) - 1) + ')' + @crlf-- remove trailing comma, add closing paren
SET @sql_from = LEFT(@sql_from, LEN(@sql_from) - 1) + @crlf-- remove trailing comma
SET @sql_from = @sql_from + 'FROM ' + @FromPrefix + @Source_Table + ';'+ @crlf
PRINT 'select ''Started Populating: ' + @Target_Table + '''+ CONVERT(varchar,GETDATE(),108) ;'
IF @AddGo = 'Y'
PRINT 'GO '
IF @Print_Truncate = 'Y'
PRINT 'TRUNCATE TABLE ' + @Target_Table
--If the table does not have an identity column, then do not print the set identity_insert statement which would cause an error
if NOT exists (select 1 from sys.columns sc inner join sys.objects so on so.Object_Id = sc.Object_Id where sc.is_identity = 1 and so.Type = 'u' and so.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']',''))
set @Print_Identity_Insert_Stmts = 'N'
IF @Print_Identity_Insert_Stmts = 'Y'
PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' ON;'
PRINT @sql_into
PRINT @sql_from
IF @Print_Identity_Insert_Stmts = 'Y'
PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' OFF;'
PRINT ''
PRINT ''
May 22, 2012 at 1:09 pm
I don't know how to post a properly formatted block of code in the forum, so I just submitted my revised version as a new script, title = 'Generate insert statements with column names for any or all tables' if anyone is interested (since it hasn't been approved yet, I can't post a link to it).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply