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?
This question is related to
sql-server
ssms
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)
Yes, use the commercial but inexpensive SSMS Tools Pack addin which has a nifty "Generate Insert statements from resultsets, tables or database" feature
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.
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='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='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'
Source: Stackoverflow.com