SQL Insert Generator Round 1: SQL 2005, SQL 2008, Insert

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:

Select all 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

SQL Insert Generator Round 1

SQL 2005

SQL 2008

Insert

SQL Server 2005

About The Author

More Articles From BrandonGalderisi

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server 2005 Experts