How can I generate an INSERT script for an existing SQL Server table that includes all stored rows?

I'm looking for a way to generate a "Create and insert all rows" script with SQL Management Studio 2008 R2.

I know that I can create a "create table" script.

I can also create an "insert in" script, but that will only generate a single row with placeholders.

Is there a way to generate an insert script that contains all currently stored rows?

Yes, but you'll need to run it at the database level.

Right-click the database in SSMS, select "Tasks", "Generate Scripts...". As you work through, you'll get to a "Scripting Options" section. Click on "Advanced", and in the list that pops up, where it says "Types of data to script", you've got the option to select Data and/or Schema.

![Screen shot of Advanced Scripting Options][1]


[1]:

Yes, use the commercial but inexpensive [SSMS Tools Pack addin][1] which has a nifty "Generate Insert statements from resultsets, tables or database" feature

[1]:

[To see links please register here]

This script generates insert statements of your existing data. This is a stored procedure which you need to run once and then it is tailor made for you.

I tried to find this kind of stuff for a while but wasn't satisfied with the results, so I wrote this stored procedure.

Example:

Exec [dbo].[INS] 'Dbo.test where 1=1'

(1) Here `dbo` is schema and test is tablename and `1=1` is condition.

Exec [dbo].[INS] 'Dbo.test where name =''neeraj''' * for string

(2) Here `dbo` is schema and test is tablename and `name='neeraj'` is condition.



Here is the stored procedure

/*
Authore : neeraj prasad sharma (please dont remove this :))
Example (1) Exec [dbo].[INS] 'Dbo.test where 1=1'
(2) Exec [dbo].[INS] 'Dbo.test where name =''neeraj''' * for string

here Dbo is schema and test is tablename and 1=1 is condition

*/


CREATE procedure [dbo].[INS]
(
@Query Varchar(MAX)
)

AS

SET nocount ON

DECLARE @WithStrINdex as INT
DECLARE @WhereStrINdex as INT
DECLARE @INDExtouse as INT

DECLARE @SchemaAndTAble VArchar(270)
DECLARE @Schema_name varchar(30)
DECLARE @Table_name varchar(240)
DECLARE @Condition Varchar(MAX)

SET @WithStrINdex=0

SELECT @WithStrINdex=CHARINDEX('With',@Query )
, @WhereStrINdex=CHARINDEX('WHERE', @Query)

IF(@WithStrINdex!=0)
SELECT @INDExtouse=@WithStrINdex
ELSE
SELECT @INDExtouse=@WhereStrINdex

SELECT @SchemaAndTAble=Left (@Query,@INDExtouse-1)
SELECT @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))

SELECT @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)
, @Table_name = SUBSTRING( @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )

, @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6


DECLARE @COLUMNS table (Row_number SmallINT , Column_Name VArchar(Max) )
DECLARE @CONDITIONS as varchar(MAX)
DECLARE @Total_Rows as SmallINT
DECLARE @Counter as SmallINT

DECLARE @ComaCol as varchar(max)
SELECT @ComaCol=''

SET @Counter=1
SET @CONDITIONS=''

INSERT INTO @COLUMNS
SELECT Row_number()Over (Order by ORDINAL_POSITION ) [Count], Column_Name
FROM INformation_schema.columns
WHERE Table_schema=@Schema_name AND table_name=@Table_name


SELECT @Total_Rows= Count(1)
FROM @COLUMNS

SELECT @Table_name= '['+@Table_name+']'

SELECT @Schema_name='['+@Schema_name+']'

While (@Counter<=@Total_Rows )
begin
--PRINT @Counter

SELECT @ComaCol= @ComaCol+'['+Column_Name+'],'
FROM @COLUMNS
WHERE [Row_number]=@Counter

SELECT @CONDITIONS=@CONDITIONS+ ' + Case When ['+Column_Name+'] is null then ''Null'' Else '''''''' + Replace( Convert(varchar(Max),['+Column_Name+'] ) ,'''''''','''' ) +'''''''' end+'+''','''
FROM @COLUMNS
WHERE [Row_number]=@Counter

SET @Counter=@Counter+1

End

SELECT @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)

SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)
SELECT @ComaCol= substring (@ComaCol,0, len(@ComaCol) )

SELECT @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS

SELECT @CONDITIONS=@CONDITIONS+'+'+ ''')'''

SELECT @CONDITIONS= 'Select '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition
print(@CONDITIONS)
Exec(@CONDITIONS)

Just to share, I've developed my own script to do it. Feel free to use it. It generates "SELECT" statements that you can then run on the tables to generate the "INSERT" statements.

```SQL
select distinct 'SELECT ''INSERT INTO ' + schema_name(ta.schema_id) + '.' + so.name + ' (' + substring(o.list, 1, len(o.list)-1) + ') VALUES ('
+ substring(val.list, 1, len(val.list)-1) + ');'' FROM ' + schema_name(ta.schema_id) + '.' + so.name + ';'
from sys.objects so
join sys.tables ta on ta.object_id=so.object_id
cross apply
(SELECT ' ' +column_name + ', '
from information_schema.columns c
join syscolumns co on co.name=c.COLUMN_NAME and object_name(co.id)=so.name and OBJECT_NAME(co.id)=c.TABLE_NAME and co.id=so.object_id and c.TABLE_SCHEMA=SCHEMA_NAME(so.schema_id)
where table_name = so.name
order by ordinal_position
FOR XML PATH('')) o (list)
cross apply
(SELECT '''+' +case
when data_type = 'uniqueidentifier' THEN 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''' END '
WHEN data_type = 'timestamp' then '''''''''+CONVERT(NVARCHAR(MAX),CONVERT(BINARY(8),[' + COLUMN_NAME + ']),1)+'''''''''
WHEN data_type = 'nvarchar' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
WHEN data_type = 'varchar' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
WHEN data_type = 'char' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
WHEN data_type = 'nchar' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
when DATA_TYPE='datetime' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],121)+'''''''' END '
when DATA_TYPE='datetime2' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],121)+'''''''' END '
when DATA_TYPE='date' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],121)+'''''''' END '
when DATA_TYPE='datetimeoffset' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],121)+'''''''' END '
when DATA_TYPE='geography' and column_name<>'Shape' then 'ST_GeomFromText(''POINT('+column_name+'.Lat '+column_name+'.Long)'') '
when DATA_TYPE='geography' and column_name='Shape' then '''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''''
when DATA_TYPE='bit' then '''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''''
when DATA_TYPE='xml' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE(CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + ']),'''''''','''''''''''')+'''''''' END '
when DATA_TYPE='text' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE(CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + ']),'''''''','''''''''''')+'''''''' END '
WHEN DATA_TYPE='image' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY(MAX),[' + COLUMN_NAME + ']),1)+'''''''' END '
WHEN DATA_TYPE='varbinary' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],1)+'''''''' END '
WHEN DATA_TYPE='binary' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],1)+'''''''' END '
when DATA_TYPE='time' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''' END '
ELSE 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE CONVERT(NVARCHAR(MAX),['+column_name+']) END' end
+ '+'', '
from information_schema.columns c
join syscolumns co on co.name=c.COLUMN_NAME and object_name(co.id)=so.name and OBJECT_NAME(co.id)=c.TABLE_NAME and co.id=so.object_id and c.TABLE_SCHEMA=SCHEMA_NAME(so.schema_id)
where table_name = so.name
order by ordinal_position
FOR XML PATH('')) val (list)
where so.type = 'U'
```