/************************************************************
* 将数据表中的数据生成添加语句
* 调用示例:
EXEC Com_InsertData 'TableName'
* 其中的TableName为表名
************************************************************/
DECLARE @objectId INT
SET @objectId = OBJECT_ID('Com_InsertData')
IF @objectId IS NOT NULL
BEGIN
DROP PROC Com_InsertData
END
GO
CREATE PROC Com_InsertData(@tablename VARCHAR(256))
AS
BEGIN
SET NOCOUNT ON
DECLARE @sqlstr VARCHAR(4000)
DECLARE @sqlstr1 VARCHAR(4000)
DECLARE @sqlstr2 VARCHAR(4000)
SELECT @sqlstr = 'select ''INSERT INTO [' + @tablename +']'
SELECT @sqlstr1 = ''
SELECT @sqlstr2 = '('
SELECT @sqlstr1 = 'VALUES (''+'
SELECT @sqlstr1 = @sqlstr1 + col + '+'',''+',
@sqlstr2 = @sqlstr2 + '['+NAME + '],'
FROM (
SELECT CASE
WHEN a.xtype = 173 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(' + CONVERT(VARCHAR(4), a.length * 2 + 2)
+ '),' + a.name + ')' + ' end'
WHEN a.xtype = 104 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(1),' + a.name + ')' + ' end'
WHEN a.xtype = 175 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' + '''''''''+' +
'replace(' + a.name + ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN a.xtype = 61 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' + '''''''''+' +
'convert(varchar(23),' + a.name + ',121)' +
'+''''''''' + ' end'
WHEN a.xtype = 106 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(' + CONVERT(VARCHAR(4), a.xprec + 2)
+ '),' + a.name + ')' + ' end'
WHEN a.xtype = 62 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(23),' + a.name + ',2)' + ' end'
WHEN a.xtype = 56 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(11),' + a.name + ')' + ' end'
WHEN a.xtype = 60 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(22),' + a.name + ')' + ' end'
WHEN a.xtype = 239 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' + '''''''''+' +
'replace(' + a.name + ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN a.xtype = 108 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(' + CONVERT(VARCHAR(4), a.xprec + 2)
+ '),' + a.name + ')' + ' end'
WHEN a.xtype = 231 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' + '''''''''+' +
'replace(' + a.name + ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN a.xtype = 59 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(23),' + a.name + ',2)' + ' end'
WHEN a.xtype = 58 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' + '''''''''+' +
'convert(varchar(23),' + a.name + ',121)' +
'+''''''''' + ' end'
WHEN a.xtype = 52 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(12),' + a.name + ')' + ' end'
WHEN a.xtype = 122 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(22),' + a.name + ')' + ' end'
WHEN a.xtype = 48 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(6),' + a.name + ')' + ' end'
WHEN a.xtype = 165 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' +
'convert(varchar(' + CONVERT(VARCHAR(4), a.length * 2 + 2)
+ '),' + a.name + ')' + ' end'
WHEN a.xtype = 167 THEN 'case when ' + a.name +
' is null then ''NULL'' else ' + '''''''''+' +
'replace(' + a.name + ','''''''','''''''''''')'
+ '+''''''''' + ' end'
ELSE '''NULL'''
END AS col,
a.colid,
a.name
FROM syscolumns a
WHERE a.id = OBJECT_ID(@tablename)
AND a.xtype <> 189
AND a.xtype <> 34
AND a.xtype <> 35
AND a.xtype <> 36
)t
ORDER BY
colid
SELECT @sqlstr = @sqlstr + LEFT(@sqlstr2, LEN(@sqlstr2) -1) + ') ' + LEFT(@sqlstr1, LEN(@sqlstr1) -3)
+ ');'' from ' + @tablename
PRINT @sqlstr
EXEC (@sqlstr)
SET NOCOUNT OFF
END
GO