Generate insert statements

The scripts section of SQLCentral has real wonderful scripts ! πŸ˜‰ One of them is indeed so useful for our MS SQL Server … You could find many variants of the same if you google it …

but this one is really awesome i feel …

Call the stored procedure to generate insert statement for any given table.Β  Or generate inserts for all tables using something like:

exec sp_MSforeachtable @command1="exec [spd_Build_Insert_Into_Stmt_With_Identity] '?','?','[LINKEDSERVERNAME].DATABASENAME.'"

/**********************************
Originally written by:β€ˆβ€ˆβ€ˆβ€ˆJesse McLain
Updated by:β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆDarren Gordon
***********************************/

Here goes the script :

alter 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 + ','
SET @sql_into = @sql_into + @crlf + '--' + @indent + ','
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 + 'ColName1 + '>,'
SET @sql_from = @sql_from + @crlf + '--' + @indent + '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 ''

This script could always come handy in times on urgent need ! Happy coding !

Advertisements

About mytechlifedays

Its been a wonderful learning period over the last 6 years in the IT industry , getting exposed to whole lot of technologies and ideas. The hurdles and the crisis that came along have been wonderful experience ... And now its time to pen them down so that let some others execute faster and easily with these information .....
This entry was posted in Database and tagged . Bookmark the permalink.

Leave a Reply ! It would be always appreciated ! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s