<

Go Premium for a chance to win a PS4. Enter to Win

x

SQL Insert Generator Round 1

Published on
16,986 Points
6,786 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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Join & Write a Comment

This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month