<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SQL Insert Generator Round 1

Published on
16,939 Points
6,739 Views
2 Endorsements
Last Modified:
Awarded
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
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 93

Expert Comment

by:Patrick Matthews
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
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Join & Write a Comment

In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month