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')
ALTER PROCEDURE dbo.up_GenerateInserts
,@IDMin SQL_VARIANT = NULL
,@IDMax SQL_VARIANT = NULL
,@IncludeIdent BIT = 0
,@IncludeIfNot BIT = 1
SET NOCOUNT ON
SET @ObjectId = object_id(QUOTENAME(@tableSchema) + '.' + QUOTENAME(@TableName))
IF @ObjectId IS NULL
RAISERROR('Table [%s].[%s] not found.',16,1,@TableSchema,@TableName)
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
RAISERROR('Column [%s].[%s].[%s] not found.',16,1,@TableSchema,@TableName,@IDField)
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
+ CASE WHEN c.DATA_TYPE IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar') THEN '''''''''+' ELSE '' END
+ ' as nvarchar(max))'
+ CASE WHEN c.DATA_TYPE IN ('VARCHAR','TEXT','char','datetime','ntext','nvarchar','nchar') THEN '+''''''''' ELSE '' END
, @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)
,N'@IDMin sql_variant,@IDMax sql_variant, @IFNotSQL nvarchar(max), @InsertSQL nvarchar(max)'
,@IDMin = @IDMin
,@IDMax = @IDMax
,@IFNotSQL = @IFNotSQL
,@InsertSQL = @InsertSQL
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:
@TableSchema = 'dbo'
,@TableName = 'PurchasedGoods'
,@IDField = 'PurchasedGoods_Id'
,@IDMin = 1
,@IDMax = 100
,@IncludeIdent = 1
,@IncludeIfNot = 1
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