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

Question

我想找一种方法在SQL Management Studio 2008 R2中生成一个“创建并插入所有行”脚本。

我知道可以创建“创建表”脚本。

我也可以创建“插入”脚本，但那只会生成一个带有占位符的单行。

是否有一种方法可以生成包含所有当前存储行的插入脚本？

- citronas

---

可能是什么是为SQL Server表自动生成INSERT语句的最佳方法？的重复问题。 - Moslem Ben Dhaou

Answer 1

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

在SSMS中右键单击数据库，选择“任务”，“生成脚本...”。随着您的操作，您将进入“脚本选项”部分。 单击“高级”，在弹出的列表中，您可以选择要脚本化的数据类型和/或模式。

- Thomas Rushton

---

不错！我曾经依赖于工具包插件，就像gbn建议的那样，来完成这种任务。很高兴知道我不再需要它了。 - ivanmp

Answer 2

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

我尝试了一段时间才找到这种东西，但对结果不满意，所以我编写了这个存储过程。

例子：

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)  

- Neeraj Prasad Sharma

---

这是一个非常有用的脚本。 是否有一种简单的方法可以更改Datetime格式，以便导出标准格式的日期，例如“2014-03-31 23:55:41”？目前我得到的时间是“Feb 10 2010 2:44 PM”。 - ChrisGuest

看起来你必须省略SELECT FROM部分，并去掉方括号，才能使SPROC正常工作。 - Corgalore

缺少计算列检查。如果您的表有任何计算列，将会出现错误。 - vicky

非常好的答案，谢谢Neeraj。对我来说有一些小错误，“INformation_schema”应该是“INFORMATION_SCHEMA”，而“DECLARE Condition Varchar(MAX)”应该是“DECLARE CONDITION Varchar(MAX)”。 - canbax

Answer 3

是的，使用商业但价格便宜的SSMS Tools Pack addin，它具有一个聪明的“从结果集、表或数据库生成插入语句”的功能。

- gbn

---

2012版本不再免费。 - CJM

Answer 4

分享一下，我已经开发了自己的脚本来完成这个任务。随意使用它。它生成“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'

- Danielle Paquette-Harvey

---

有人知道如何使用这个脚本吗？它看起来几乎为数据库中的每个表生成脚本。如何仅针对单个表运行它？ - Post Impatica

嗨，我编写了这个脚本来脚本化我的数据库中的所有表。您只需要在“table_name = so.name”之后的“where”中添加“and table_name ='NAME_OF_TABLE'”即可。 - Danielle Paquette-Harvey

这里还应该有一行 DATA_TYPE = 'text'，与 'xml' 的设置相同。 - VenerableAgents

@VenerableAgents 你说得对，我刚刚添加了它！ - Danielle Paquette-Harvey