Click here to Skip to main content
14,239,386 members

Generating INSERT statements in SQL Server

Rate this:
4.91 (89 votes)
Please Sign up or sign in to vote.
4.91 (89 votes)
16 Jan 2005CPOL
Stored procedure to generate INSERT..VALUES statements in SQL Server.

Introduction

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

Background

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
DECLARE cursCol CURSOR FAST_FORWARD FOR 
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
    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+','''')+'''''',''+'
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'')+''''''),''+'
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'')+'''''',''+'
END

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

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

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
DEALLOCATE cursCol

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

InsertGenerator <tablename>

E.g.:

USE pubs
GO
InsertGenerator employee 
GO

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.

History

  • Ver 0.1b added Dec 5, 03.

License

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

Share

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.

Comments and Discussions

 
QuestionHow to emulate SQLPUBWIZ? [modified] Pin
apergiel11-May-09 12:10
memberapergiel11-May-09 12:10 
Generalfine Script, updated with schema of table Pin
spider_pat5-Mar-09 1:26
memberspider_pat5-Mar-09 1:26 
GeneralRe: fine Script, updated with schema of table Pin
futebolb29-Sep-10 9:04
memberfutebolb29-Sep-10 9:04 
GeneralPerfect!!!! Pin
MTRIG4-Feb-09 3:10
memberMTRIG4-Feb-09 3:10 
GeneralMuchas Gracias Pin
Galo Solís19-Jan-09 9:10
memberGalo Solís19-Jan-09 9:10 
GeneralThanks !!! Herewith Addition for inserting without taking the Primary Keys ( i.e. not having to run set identity_insert on ; .... [modified] Pin
yordan_georgiev19-Nov-08 0:20
memberyordan_georgiev19-Nov-08 0:20 
Generalthanks a lot! Pin
Member 23868618-Oct-08 10:16
memberMember 23868618-Oct-08 10:16 
GeneralSimpler way, which i preferred Pin
dRn-18-May-08 4:34
memberdRn-18-May-08 4:34 
<code>
-- MSSQL 2005
declare @table_name nvarchar(100)
declare @columns nvarchar(max)
declare @values nvarchar(max)
declare @identity bit
declare @sql nvarchar(max)

set @table_name = 'MyTable'
set @columns =''
set @values =''
set @identity = 0

select
@identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),
@columns = @columns + ',' + '['+column_name+']',
@values = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+'] as varbinary(max))),''NULL'')'
from
information_schema.columns
where
table_name = @table_name and data_type != 'timestamp'

set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ') values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name
if @identity=1 print 'set identity_insert [' + @table_name + '] on'
exec sp_executesql @sql
if @identity=1 print 'set identity_insert [' + @table_name + '] off'
</code>
GeneralRe: Simpler way, which i preferred Pin
galsont11-Mar-09 23:58
membergalsont11-Mar-09 23:58 
GeneralRe: Simpler way, which i preferred Pin
Kanyungu9-Feb-11 23:08
memberKanyungu9-Feb-11 23:08 
GeneralGenerated script will break on Varchar fields with ( ' ) Pin
Hanysm5-Mar-08 4:36
memberHanysm5-Mar-08 4:36 
GeneralRe: Generated script will break on Varchar fields with ( ' ) Pin
cj@cjnow.com20-Apr-10 18:28
membercj@cjnow.com20-Apr-10 18:28 
GeneralAuto-Increment PK Columns Pin
Jacquers20-Nov-07 22:10
memberJacquers20-Nov-07 22:10 
GeneralTime saver Pin
pornoisfoei26-Jun-07 21:44
memberpornoisfoei26-Jun-07 21:44 
GeneralVery useful, saved a lot of time Pin
Omar Al Zabir1-Apr-07 22:34
memberOmar Al Zabir1-Apr-07 22:34 
GeneralSQL Scripter Pin
tommyonline28-Jan-06 4:32
membertommyonline28-Jan-06 4:32 
GeneralRe: SQL Scripter Pin
r_maiya27-Mar-07 6:21
memberr_maiya27-Mar-07 6:21 
GeneralEscape the quotes Pin
Amit G18-Jan-06 8:13
memberAmit G18-Jan-06 8:13 
GeneralCOLLATE Pin
milovanderlinden18-Jan-06 2:19
membermilovanderlinden18-Jan-06 2:19 
GeneralNull Values For DateTime Data Type Pin
YinkaAlalade3-Nov-05 23:03
memberYinkaAlalade3-Nov-05 23:03 
GeneralError with type image Pin
sebastien97129-Oct-05 10:01
membersebastien97129-Oct-05 10:01 
Generalran into a cool tool which generates SQL Insert statements Pin
Padigela14-May-05 10:01
memberPadigela14-May-05 10:01 
Generalpresuming the data type is int,bit,numeric,decimal Pin
Jaem00713-May-05 2:35
memberJaem00713-May-05 2:35 
GeneralRe: presuming the data type is int,bit,numeric,decimal Pin
Jaem00713-May-05 11:24
memberJaem00713-May-05 11:24 
GeneralRe: presuming the data type is int,bit,numeric,decimal Pin
Ticanaer19-May-09 9:00
memberTicanaer19-May-09 9:00 

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

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

Article
Posted 6 Dec 2003

Stats

479.1K views
11.6K downloads
98 bookmarked