Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

SQL Insert Generator Round 1

Published:
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What if it has an identity column?  I've taken a few steps to make things easier for me.

First of all, this is a work in progress.  There's bound to be little bugs that I haven't worked out yet because I literally just wrote it 10 minutes ago when asked to do this.  But as updates to this happen, I will post post new iterations of "SQL Insert Generator".

Secondly, this is for SQL 2005 and later only.  Trying to do this working within the limitations of the varchar() and nvarchar() data types in SQL 2000 would have been too much of a pain in the butt.  But if you are working with fairly narrow tables then it should work in SQL 2000.  Just change the nvarchar(MAX) variables to nvarchar(4000).

IF object_id('dbo.up_GenerateInserts') IS NULL
                           EXEC ('create procedure dbo.up_GenerateInserts as set nocount on')
                      GO
                      
                      ALTER PROCEDURE dbo.up_GenerateInserts
                            @TableSchema       sysname
                           ,@TableName         sysname
                           ,@IDField           sysname
                           ,@IDMin             SQL_VARIANT = NULL
                           ,@IDMax             SQL_VARIANT = NULL
                           ,@IncludeIdent      BIT = 0
                           ,@IncludeIfNot      BIT = 1
                      AS
                      SET NOCOUNT ON
                      
                      DECLARE 
                            @InsertSQL         NVARCHAR(MAX)
                           ,@SelectSQL         NVARCHAR(MAX)
                           ,@IFNotSQL          NVARCHAR(MAX)
                           ,@ObjectId          INT
                           ,@IDFieldQuote      BIT
                           ,@IDFieldDT         sysname
                      
                      SET @ObjectId = object_id(QUOTENAME(@tableSchema) + '.' + QUOTENAME(@TableName))
                      
                      IF @ObjectId IS NULL
                      BEGIN
                           RAISERROR('Table [%s].[%s] not found.',16,1,@TableSchema,@TableName)
                           RETURN -1
                      END
                      SELECT @IDFieldDT = c.DATA_TYPE
                      FROM INFORMATION_SCHEMA.columns c WHERE c.TABLE_SCHEMA = @TableSchema AND c.TABLE_NAME = @TableName AND c.COLUMN_NAME = @IDField
                      
                      IF @IDFieldDT IS NULL
                      BEGIN
                           RAISERROR('Column [%s].[%s].[%s] not found.',16,1,@TableSchema,@TableName,@IDField)
                           RETURN -1
                      END
                      
                      IF @IDFieldDT IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar')
                           SET @IDFieldQuote = 1
                      
                      SET @IFNotSQL = 'IF NOT EXISTS (select 1 from ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' where ' + QUOTENAME(@IDField) + CASE WHEN @IDFieldQuote = 1 THEN  ' = ''%IDValue%'')' ELSE ' = %IDValue%)' END  + CHAR(13) + CHAR(10) 
                      SET @SelectSQL = 'SELECT replace(@IFNotSQL,''%IDValue%'',replace(' + @IDField + ','''''''','''''''''''')) + ''    @@InsertSQL@@ 
                          VALUES('' + '
                      SET @InsertSQL = N'insert into ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '('
                      
                      
                      SELECT @SelectSQL = @SelectSQL 
                           + 'isnull(' 
                           + CASE WHEN c.DATA_TYPE IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar') THEN '''''''''+' ELSE '' END 
                           + 'REPLACE('
                           + 'cast('
                           + QUOTENAME(c.COLUMN_NAME) 
                           + ' as nvarchar(max))'
                           + ','''''''','''''''''''')'
                           + CASE WHEN c.DATA_TYPE IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar') THEN '+''''''''' ELSE '' END 
                           + ',''NULL'')'
                           + '+'',''+'
                           , @InsertSQL = @InsertSQL + QUOTENAME(c.COLUMN_NAME) + ','
                      FROM INFORMATION_SCHEMA.COLUMNS c
                      WHERE c.TABLE_SCHEMA = @TableSchema
                        AND c.TABLE_NAME = @TableName
                        AND (@IncludeIdent = 1 OR (@IncludeIdent = 0 AND COLUMNPROPERTY(@ObjectId,c.Column_Name,'IsIdentity')=0))
                      ORDER BY c.ORDINAL_POSITION
                      
                      SET @InsertSQL = LEFT(@InsertSQL,LEN(@InsertSQL)-1) + ')'
                      SET @SelectSQL = LEFT(@SelectSQL,LEN(@SelectSQL)-5) + '+'')'''
                      
                      SET @SelectSQL = REPLACE(@SelectSQL, '@@InsertSQL@@', @InsertSQL)
                      SET @SelectSQL = @SelectSQL 
                                + ' from ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) 
                                + ' where ' + QUOTENAME(@IDField) + ' BETWEEN @IDMin and @IDMax '
                                + ' order by 1,' + QUOTENAME(@IDField)
                      
                      EXEC sp_executesql 
                            @SelectSQL
                           ,N'@IDMin  sql_variant,@IDMax sql_variant, @IFNotSQL nvarchar(max), @InsertSQL nvarchar(max)'
                           ,@IDMin = @IDMin
                           ,@IDMax = @IDMax 
                           ,@IFNotSQL = @IFNotSQL
                           ,@InsertSQL = @InsertSQL
                      
                      
                      
                      GO

Open in new window


The procedure works as follows.  You pass in some simple parameters.  The schema and table name are obvious.  The @IDField parameter isn't quite so obvious so I'll explain.  This is the field used to select which values you want to export.  This is necessary, at least for me, because when I am generating these types of inserts I am doing so only for a range of data in a column.  @IDMin and @IDMax are inclusive range limiters for the @IDField.  @IncludeIdent says whether or not to include a field if it has an identity property on it.  This script, intentionally, does not include the necessary SET IDENTITY_INSERT ON statement in order for the resulting SQL to run, that's something you will need to include with the output.  Lastly @IncludeIfNot says whether or not to include an "IF NOT EXISTS" statement on the resultant insert statement.  This is needed for making code that does not fail when run more than once or produce an undesirable result.

Now let's talk about an example.  Let's say that you have a table called dbo.PurchasedGoods and you want to generate an insert statement for fields where the PurchasedGoods_Id value is from 1 to 100.  The syntax of the command would be as follows:



exec dbo.up_GenerateInserts
                            @TableSchema   = 'dbo'
                           ,@TableName     = 'PurchasedGoods'
                           ,@IDField       = 'PurchasedGoods_Id'
                           ,@IDMin         = 1
                           ,@IDMax         = 100
                           ,@IncludeIdent  = 1
                           ,@IncludeIfNot  = 1

Open in new window


Variations of the last two parameters can be used depending upon what data you want to be generated.

I hope this comes in handy for you as it already has for me.  If you notice any problems, or something doesn't work with your data, please post a comment and I will address it or post a new revision.  To keep up to date on changes to this script (http://bit.ly/SQLinsGen) as it evolves, and to find other useful scripts, stop by my site at http://www.sqlservernation.com.

BrandonGalderisi
2
7,530 Views

Comments (1)

CERTIFIED EXPERT
Top Expert 2010

Commented:
Brandon,

Thanks for sharing, this is brilliant!  I wish I had had something like this available the few times I had had to script out the complete recreation of a table, including the values :)

Patrick

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.