Click here to Skip to main content
11,806,339 members (69,579 online)
Click here to Skip to main content

Generating INSERT statements in SQL Server

, 16 Jan 2005 CPOL 399.2K 10.6K 94
Rate this:
Please Sign up or sign in to vote.
Stored procedure to generate INSERT..VALUES statements in SQL Server.


The stored procedure InsertGenerator generates the INSERT..VALUES statements for the specified table name.


SQL Server doesn’t allow generation of INSERT statements for the table data, when Generate SQL Script option is selected. The workaround is to make use of DTS for transferring data across servers. However, there exists a need to generate INSERT statements from the tables for porting data. Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT..VALUES statements come handy.

Using the code

This small yet useful stored procedure will take as parameter the table name and generates the INSERT SQL statements for the same. The output can be redirected to either text format (Ctrl+T in Query Analyzer) or Output to a text file. The procedure accepts an input varchar type parameter that has to be the table name under consideration for statement generation.

CREATE PROC InsertGenerator
(@tableName varchar(100)) as

Then it includes a cursor to fetch column specific information (column name and the data type thereof) from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses of an INSERT DML statement.

--Declare a cursor to retrieve column specific information 
--for the specified table
SELECT column_name,data_type FROM information_schema.columns 
    WHERE table_name = @tableName
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 '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
    print 'Table '+@tableName+' not found, processing skipped.'
    close curscol
    deallocate curscol

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

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

FETCH NEXT FROM cursCol INTO @colName,@dataType

After both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.

DECLARE @Query nvarchar(4000) -- provide for the whole query, 
                              -- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') 
    VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' 
    FROM '+@tableName
exec sp_executesql @query --load and run the built query

Eventually, close and de-allocate the cursor created for columns information.

CLOSE cursCol

After the procedure is compiled and created, just run it in Query Analyzer by using the following syntax:

InsertGenerator <tablename>


USE pubs
InsertGenerator employee 

Then copy the INSERT statements and run’em in the query analyzer.

Before the INSERTs are run, SET IDENTITY_INSERT <TABLENAME>ON should be passed for adding values in an identity-based column.

Points of Interest

D: T-SQL lovers might wish to extend this procedure for providing support for binary data types such as IMAGE etc.


  • Ver 0.1b added Dec 5, 03.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sumit Amar .
Team Leader EA
United States United States
Sumit Amar started programming in BASIC at the age of 14 in 1993, then moved on to C/UNIX.
Later in 1999, he started developing commercial applications in J2SE, J2EE and Perl. He started developing applications in .NET with ASP+ (later renamed to ASP.NET) in December 2000 with public Beta 1. He has been developing in .NET ever since.

He has an MBA degree in IT and Systems.

Sumit is a Director of Engineering at Electronic Arts, where he works on building hybrid cloud systems.

You may also be interested in...

Comments and Discussions

GeneralVery helpful Pin
RUs12317-Nov-14 23:03
memberRUs12317-Nov-14 23:03 
QuestionYour procedure is helpful Pin
George daniel14-Jul-14 21:00
memberGeorge daniel14-Jul-14 21:00 
GeneralThanks a lot Pin
RakeshKr11-Jun-13 1:48
memberRakeshKr11-Jun-13 1:48 
QuestionGenerate insert for varbinary fields Pin
Diana NoWay16-Apr-13 9:28
professionalDiana NoWay16-Apr-13 9:28 
QuestionThanks Pin
Eddy Nijs15-Oct-12 20:42
memberEddy Nijs15-Oct-12 20:42 
Generalthnks Pin
Payitax123316-Aug-12 15:03
memberPayitax123316-Aug-12 15:03 
GeneralMy vote of 5 Pin
Payitax123316-Aug-12 15:01
memberPayitax123316-Aug-12 15:01 
QuestionAwesome! Pin
jaaaaaac3-Jul-12 4:50
memberjaaaaaac3-Jul-12 4:50 
Questioni would say AWESOME dude Pin
Nikhil Bhivgade25-Jun-12 1:13
memberNikhil Bhivgade25-Jun-12 1:13 
NewsSQL Server Management Studio does this. Pin
Alexandru Lungu2-May-12 1:50
memberAlexandru Lungu2-May-12 1:50 
QuestionNULLs are not handled correctly. Causing issues with nullabel UniqueIdentifier columns. Pin
RS Reddy220-Apr-12 12:30
memberRS Reddy220-Apr-12 12:30 
AnswerRe: NULLs are not handled correctly. Causing issues with nullabel UniqueIdentifier columns. Pin
bencejoful12-Jun-12 8:17
memberbencejoful12-Jun-12 8:17 
SuggestionReady to use Stored Procedure for generating INSERT statements Pin
Mohammed_Rashid12-Mar-12 21:04
memberMohammed_Rashid12-Mar-12 21:04 
This post [^]may be helpful. You can generate the INSERT statements for a table with applying filter and even the order in which you want the output data. It's a ready to use parameterized Stored Procedure. It does not use cursors and hence faster. It works fine with all data types.
Questionsimply awesome man Pin
Farhan Asif12-Mar-12 4:09
memberFarhan Asif12-Mar-12 4:09 
QuestionAnother tool with more features Pin
Rishikesh_716-Feb-12 3:42
memberRishikesh_716-Feb-12 3:42 
QuestionGenerate Insert script using C# : Check this out Pin
Rishikesh_716-Feb-12 3:39
memberRishikesh_716-Feb-12 3:39 
GeneralMy vote of 5 Pin
Amol_B7-Feb-12 1:00
memberAmol_B7-Feb-12 1:00 
GeneralMy vote of 5 Pin
btrain24-Jan-12 12:51
memberbtrain24-Jan-12 12:51 
QuestionHow to add a WHERE param Pin
Member 63398310-Jan-12 2:47
memberMember 63398310-Jan-12 2:47 
AnswerRe: How to add a WHERE param Pin
Hawkeye367718-Apr-12 12:00
memberHawkeye367718-Apr-12 12:00 
GeneralExcellent! Thanks for sharing Pin
Natreen12-Dec-11 2:57
memberNatreen12-Dec-11 2:57 
GeneralThanks! Pin
Member 84519141-Dec-11 15:29
memberMember 84519141-Dec-11 15:29 
QuestionThanks lot Pin
basheer97127-Oct-11 0:44
memberbasheer97127-Oct-11 0:44 
QuestionThank you, Thank You :) Pin
Basil Ntinga20-Oct-11 20:33
memberBasil Ntinga20-Oct-11 20:33 
QuestionHandling XML Datatype Pin
Badcop66619-Jun-11 12:45
memberBadcop66619-Jun-11 12:45 
AnswerRe: Handling XML Datatype Pin
Badcop66619-Jun-11 15:42
memberBadcop66619-Jun-11 15:42 
GeneralRe: Handling XML Datatype Pin
Badcop66619-Jun-11 15:45
memberBadcop66619-Jun-11 15:45 
GeneralMy vote of 5 Pin
Polash.IT6-Jun-11 11:42
memberPolash.IT6-Jun-11 11:42 
GeneralMy vote of 5 Pin
Gypo2-Jun-11 4:50
memberGypo2-Jun-11 4:50 
GeneralMy vote of 5 Pin
jaguarOax17-May-11 10:32
memberjaguarOax17-May-11 10:32 
GeneralMy vote of 5 Pin
JP®16-May-11 3:43
memberJP®16-May-11 3:43 
GeneralMy vote of 5 Pin
George Kousis8-May-11 1:30
memberGeorge Kousis8-May-11 1:30 
GeneralDoes not work with local or global temporary tables (ie #foo ##foo) Pin
Jafin30-Mar-11 17:47
memberJafin30-Mar-11 17:47 
GeneralINTO Qualifier Missing Pin
RogerDodge18-Feb-11 6:41
memberRogerDodge18-Feb-11 6:41 
GeneralRe: INTO Qualifier Missing Pin
Sumit Amar .25-May-11 10:53
memberSumit Amar .25-May-11 10:53 
GeneralMy vote of 5 Pin
r sukhadiya1-Feb-11 21:06
memberr sukhadiya1-Feb-11 21:06 
GeneralMy vote of 5 Pin
didilcool28-Jan-11 5:29
memberdidilcool28-Jan-11 5:29 
GeneralMy vote of 5 Pin
Yatin Barmeda15-Jan-11 3:58
memberYatin Barmeda15-Jan-11 3:58 
GeneralMy vote of 5 Pin
pavan1233219-Dec-10 1:53
memberpavan1233219-Dec-10 1:53 
GeneralGreat! Thank you! Pin
vnts722-Nov-10 23:17
membervnts722-Nov-10 23:17 
GeneralMy vote of 5 Pin
Member 164164223-Sep-10 4:28
memberMember 164164223-Sep-10 4:28 
GeneralMy vote of 5 Pin
bytejanitor10-Aug-10 4:30
memberbytejanitor10-Aug-10 4:30 
QuestionReally long strings Pin
bytejanitor9-Aug-10 13:34
memberbytejanitor9-Aug-10 13:34 
AnswerRe: Really long strings Pin
Member 85414645-Jan-12 7:06
memberMember 85414645-Jan-12 7:06 
GeneralThank you for saving me time! Pin
Adrian Akison8-Jun-10 16:42
memberAdrian Akison8-Jun-10 16:42 
GeneralTks a Lot! Pin
FernandoMartin18-Nov-09 2:10
memberFernandoMartin18-Nov-09 2:10 
GeneralThanks !!! Pin
aqnysos12329-Sep-09 5:11
memberaqnysos12329-Sep-09 5:11 
GeneralThanks a lot! Pin
Uday Takbhate7-Aug-09 2:41
memberUday Takbhate7-Aug-09 2:41 
GeneralThank you! Pin
BruceL21-May-09 5:24
memberBruceL21-May-09 5:24 
QuestionHow to emulate SQLPUBWIZ? [modified] Pin
apergiel11-May-09 12:10
memberapergiel11-May-09 12:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.151002.1 | Last Updated 17 Jan 2005
Article Copyright 2003 by Sumit Amar .
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid