INSERT statement(s) generator

Generates INSERT statement(s) for data in a table.

Purpose

• To regenerate data at another location.
• To script table or view data populated in automated way.
• To script setup data populated in automated/manual way.

Installation

• Pre-requisites: MS SQL Server 2005 or later
• Download a copy of the GenerateInsert.sql
• Open SQL Server Management studio and load GenerateInsert.sql
• Select a database to install the stored procedure to
• Click Execute from the toolbar, this should run with a result of Command Completely Successfully

Change Log

• Build 6. Added support for table-valued and inline user defined functions.
• Build 5. Fixed an issue with strings longer than 4000 characters.
• Build 4. New option to sort data returned by a query.

Usage

Quick example

USE [AdventureWorks];
GO
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';

This will generate the following script:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
VALUES
 (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

Table-valued and inline user defined functions are supported

EXECUTE dbo.GenerateInsert @ObjectName='dbo.ufnGetContactInformation', @FunctionParameters='(1)'
, @TargetObjectName='MyContactInfo';

This will generate the following script:

SET NOCOUNT ON
INSERT INTO MyContactInfo
([PersonID],[FirstName],[LastName],[JobTitle],[BusinessEntityType])
VALUES
 (1,N'Ken',N'Sánchez',N'Chief Executive Officer',N'Employee')

Example using SELECT syntax

EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType'
,@UseSelectSyntax=1
,@UseColumnAliasInSelect=1
,@GenerateOneColumnPerLine=1;

This will generate the following script:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID]
,[Name]
,[rowguid]
,[ModifiedDate]
)
SELECT 1 [AddressTypeID]
,N'Billing' [Name]
,'B84F78B1-4EFE-4A0E-8CB7-70E9F112F886' [rowguid]
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
UNION
SELECT 2 [AddressTypeID]
,N'Home' [Name]
,'41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2' [rowguid]
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
UNION
SELECT 3 [AddressTypeID]
,N'Main Office' [Name]
,'8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575' [rowguid]
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
UNION
SELECT 4 [AddressTypeID]
,N'Primary' [Name]
,'24CB3088-4345-47C4-86C5-17B535133D1E' [rowguid]
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
UNION
SELECT 5 [AddressTypeID]
,N'Shipping' [Name]
,'B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5' [rowguid]
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
UNION
SELECT 6 [AddressTypeID]
,N'Archive' [Name]
,'A67F238A-5BA2-444B-966C-0467ED9C427F' [rowguid]
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
SET IDENTITY_INSERT Person.AddressType OFF

Select results into table variable for later reuse

The example below is pretty tricky because simple approach INSERT INTO... EXECUTE dbo.GenerateInsert; ends up with INSERT EXEC statement cannot be nested. Some pre-requisites are required in advance, i.e. ad hoc distributed queries should be allowed on the server, connection is made using Windows authentication.

DECLARE @Results table (TableRow varchar(max));
DECLARE @sql nvarchar(max) =
'SELECT * FROM OPENROWSET (
''SQLNCLI'',
''Server=(local);Database=' + DB_NAME() + ';Trusted_Connection=yes;'',
''EXECUTE dbo.GenerateInsert @ObjectName = N''''Person.AddressType''''
,@OmmitInsertColumnList=1
,@GenerateSingleInsertPerRow=1
,@FormatCode=0
,@GenerateGo=0
,@PrintGeneratedCode=0
;''
)';

INSERT INTO @Results
EXECUTE sp_executesql @sql;

SELECT *
FROM @Results;

This will generate the following script:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType  VALUES (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
INSERT INTO Person.AddressType  VALUES (2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
INSERT INTO Person.AddressType  VALUES (3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
INSERT INTO Person.AddressType  VALUES (4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
INSERT INTO Person.AddressType  VALUES (5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
INSERT INTO Person.AddressType  VALUES (6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

Script all tables

DECLARE @Name nvarchar(261);
DECLARE TableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name) ObjectName
FROM sys.tables t
  INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name NOT LIKE 'sys%'
FOR READ ONLY
;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
  EXECUTE dbo.GenerateInsert @ObjectName = @Name;

  FETCH NEXT FROM TableCursor INTO @Name;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

Contributing to this project

Anyone and everyone is welcome to contribute to sql-generate-insert,

Feel free to report a bug in the issue tracker or create a fork, improve it and submit a pull request.

Would like to express your gratitude?

Hit the ⭐ Star button!