Generate insert script from select statement SQL Server

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

Table of Contents Show

Using the code
Points of Interest
How to create insert script from select statement in SQL Server?
How to generate insert scripts for data in SQL Server?
Can I use select in insert statement SQL?
How to generate insert script in SQL Server 2012 with data?

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

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

3 type parameter that has to be the table name under consideration for statement generation.

SQL

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.

SQL

--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.

SQL

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.

SQL

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.:

SQL

USE pubs
GO
InsertGenerator employee 
GO

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

Before the INSERTs are run,

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

0 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

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

1 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)

How to create insert script from select statement in SQL Server?

Right click on the database > Tasks > Generate Scripts. Next. Select "Select specific database objects" and check the table you want scripted, Next. Click Advanced > in the list of options, scroll down to the bottom and look for the "Types of data to script" and change it to "Data Only" > OK.

How to generate insert scripts for data in SQL Server?

In SSMS Object Explorer, right-click the database. From the right-click menu, go to Tasks >> Generate Scripts... In the Generate and Publish Scripts pop-up window, press Next to choose objects screen. Now, the choose objects screen, choose Select specific database objects and choose the tables you want to script.

Can I use select in insert statement SQL?

You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.