如何为包含所有存储行的现有SQL Server表生成INSERT脚本?

是的，但是您需要在数据库级别运行它。

右键单击SSMS中的数据库，选择“Tasks”，“Generate Scripts…”当你继续工作时，你会看到“脚本选项”部分。点击“高级”，在弹出的列表中，它显示“脚本数据类型”，你可以选择数据和/或模式。

是的，使用商业但便宜的SSMS工具包插件，它有一个漂亮的“从结果集、表或数据库生成插入语句”功能

这个脚本生成现有数据的插入语句。这是一个存储过程，您需要运行一次，然后它是为您量身定制的。

我试着找到这类东西，但对结果不满意，所以我写了这个存储过程。

例子:

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

(1)这里dbo是模式，test是表名，1=1是条件。

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

(2)这里dbo是模式，test是表名，name='neeraj'是条件。

下面是存储过程

/*
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)

只是为了分享，我开发了自己的脚本来做这件事。请随意使用。它生成&;select &;语句，然后您可以在表上运行以生成&;insert &;语句。

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'