Updated InsertGenerator to Handle Schema name when generating SQL insert statements

This is an updated version of “InsertGenerator” from CodePlex. This tools works great when you want to create a few insert statements from a table, and save them in a file; or to transfer a few rows from one system to another. It’s great for lookup/code tables

I referred to this “how to build sql insert statements from a table” utility in an earlier blog.

This fix below is to allow support of a schema, because in some database you need to specify both the schema name and the tablename.

To run it:


exec InsertGenerator2 'YourSchema', 'YourTable'

Specify DBO for YourSchema if you that to use the default schema.

It will give you a list of insert statements that you can copy/paste to another system.

If your table has an identity column, add these statemetns before/after your inserts:


SET IDENTITY_INSERT YourSchema.YourTable ON
SET IDENTITY_INSERT YourSchema.YourTable OFF


<pre>USE [EchoOptimizer]
GO
/****** Object:  StoredProcedure [dbo].[InsertGenerator2]    Script Date: 7/11/2018 12:55:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[InsertGenerator2]
(@schemaName varchar(100), 
 @tableName varchar(100)) as

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR 
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName and TABLE_SCHEMA = @schemaName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+ @schemaName + '.' + @tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status&lt;&gt;0
  begin
  print 'Table '+@tableName+' not found, processing skipped.'
  close curscol
  deallocate curscol
  return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
  --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
  SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else 
BEGIN
  SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
  SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE 
IF @dataType='datetime'
BEGIN
  --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
  --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
  --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
  SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE 
IF @dataType='image' 
BEGIN
  SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal 
BEGIN
  --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
  --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
  SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+ @schemaName +'.' + @tableName
print @query 
exec sp_executesql @query
--select @query

CLOSE cursCol
DEALLOCATE cursCol
</pre>

Uncategorized  

Leave a Reply