1.创建存储过程
CREATE PROCEDURE [dbo].[sp_generate_insert_script]
(
@table_name NVARCHAR(128), --表名
@condition NVARCHAR(128) --查询条件WHERE 语句
)
AS
DECLARE @column_list varchar(8000)
DECLARE @values_list varchar(8000)
DECLARE @sql varchar(8000)
DECLARE @msg varchar(8000)
CREATE TABLE #result(sql varchar(8000))
IF EXISTS(SELECT 1 FROM sysobjects WHERE xtype='U' AND name=@table_name)
BEGIN
SELECT @column_list='',@values_list=''
SELECT
@column_list=@column_list+','+name,
@values_list=@values_list+'+'',''+'+
CASE WHEN xtype IN(175,167,36) THEN--char,varchar,uniqueidentifier
'ISNULL(''''''''+REPLACE('+name+','''''''','''''''''''')+'''''''',''NULL'')'
WHEN xtype in(239,231) THEN--nchar,nvarchar
'ISNULL(''N''''''+REPLACE('+name+','''''''','''''''''''')+'''''''',''NULL'')'
WHEN xtype in(61,58) THEN--datetime,smalldatetime
'ISNULL(''''''''+CONVERT(char(23),'+name+',121)+'''''''',''NULL'')'
ELSE--digital
'ISNULL(CONVERT(varchar(20),'+name+'),''NULL'')'
END
FROM (SELECT a.name,a.xtype
FROM syscolumns a,sysobjects b
WHERE b.xtype='U' AND b.name=@table_name AND a.id=b.id
AND a.xtype NOT IN(173,165,34,35,99,98,189)
--NOT binary,varbinary,image,text,ntext,sql_variant,timestamp
)t
SELECT @column_list=STUFF(@column_list,1,1,''),
@values_list=STUFF(@values_list,1,4,''),
@sql='SELECT ''INSERT INTO '+@table_name+'('+@column_list+')'
+' VALUES('''+@values_list+'+'')'' sql FROM ['+@table_name+'] '
+ @condition;
INSERT INTO #result(sql)
EXEC(@sql)
END
ELSE
BEGIN
SET @msg='Can''t generate the insert script of the table '''+@table_name
+''', because it does not exist in the system catalog.'
DROP TABLE #result
RAISERROR(@msg,16,1)
RETURN
END
2.执行存储过程
DECLARE @table_name nvarchar(128)
DECLARE @condition nvarchar(128)
-- TODO: 在此处设置参数值。
SET @table_name='TableName' --表名称
SET @condition='WHERE 1=1' --筛选条件，如果筛选条件为空，SET @condition=''即可
EXECUTE [dbo].[sp_generate_insert_script]
@table_name
,@condition