/************************************************************
*将数据表中的数据生成添加语句
*调用示例:
EXECCom_InsertData'TableName'
*其中的TableName为表名
************************************************************/
DECLARE@objectIdINT
SET@objectId=OBJECT_ID('Com_InsertData')
IF@objectIdISNOTNULL
BEGIN
DROPPROCCom_InsertData
END
GO
CREATEPROCCom_InsertData(@tablenameVARCHAR(256))
AS
BEGIN
SETNOCOUNTON
DECLARE@sqlstrVARCHAR(4000)
DECLARE@sqlstr1VARCHAR(4000)
DECLARE@sqlstr2VARCHAR(4000)
SELECT@sqlstr='select''INSERTINTO['+@tablename+']'
SELECT@sqlstr1=''
SELECT@sqlstr2='('
SELECT@sqlstr1='VALUES(''+'
SELECT@sqlstr1=@sqlstr1+col+'+'',''+',
@sqlstr2=@sqlstr2+'['+NAME+'],'
FROM(
SELECTCASE
WHENa.xtype=173THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar('+CONVERT(VARCHAR(4),a.length*2+2)
+'),'+a.name+')'+'end'
WHENa.xtype=104THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(1),'+a.name+')'+'end'
WHENa.xtype=175THEN'casewhen'+a.name+
'isnullthen''NULL''else'+'''''''''+'+
'replace('+a.name+','''''''','''''''''''')'
+'+'''''''''+'end'
WHENa.xtype=61THEN'casewhen'+a.name+
'isnullthen''NULL''else'+'''''''''+'+
'convert(varchar(23),'+a.name+',121)'+
'+'''''''''+'end'
WHENa.xtype=106THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar('+CONVERT(VARCHAR(4),a.xprec+2)
+'),'+a.name+')'+'end'
WHENa.xtype=62THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(23),'+a.name+',2)'+'end'
WHENa.xtype=56THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(11),'+a.name+')'+'end'
WHENa.xtype=60THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(22),'+a.name+')'+'end'
WHENa.xtype=239THEN'casewhen'+a.name+
'isnullthen''NULL''else'+'''''''''+'+
'replace('+a.name+','''''''','''''''''''')'
+'+'''''''''+'end'
WHENa.xtype=108THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar('+CONVERT(VARCHAR(4),a.xprec+2)
+'),'+a.name+')'+'end'
WHENa.xtype=231THEN'casewhen'+a.name+
'isnullthen''NULL''else'+'''''''''+'+
'replace('+a.name+','''''''','''''''''''')'
+'+'''''''''+'end'
WHENa.xtype=59THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(23),'+a.name+',2)'+'end'
WHENa.xtype=58THEN'casewhen'+a.name+
'isnullthen''NULL''else'+'''''''''+'+
'convert(varchar(23),'+a.name+',121)'+
'+'''''''''+'end'
WHENa.xtype=52THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(12),'+a.name+')'+'end'
WHENa.xtype=122THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(22),'+a.name+')'+'end'
WHENa.xtype=48THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar(6),'+a.name+')'+'end'
WHENa.xtype=165THEN'casewhen'+a.name+
'isnullthen''NULL''else'+
'convert(varchar('+CONVERT(VARCHAR(4),a.length*2+2)
+'),'+a.name+')'+'end'
WHENa.xtype=167THEN'casewhen'+a.name+
'isnullthen''NULL''else'+'''''''''+'+
'replace('+a.name+','''''''','''''''''''')'
+'+'''''''''+'end'
ELSE'''NULL'''
ENDAScol,
a.colid,
a.name
FROMsyscolumnsa
WHEREa.id=OBJECT_ID(@tablename)
ANDa.xtype<>189
ANDa.xtype<>34
ANDa.xtype<>35
ANDa.xtype<>36
)t
ORDERBY
colid
SELECT@sqlstr=@sqlstr+LEFT(@sqlstr2,LEN(@sqlstr2)-1)+')'+LEFT(@sqlstr1,LEN(@sqlstr1)-3)
+');''from'+@tablename
PRINT@sqlstr
EXEC(@sqlstr)
SETNOCOUNTOFF
END
GO
如果觉得《SqlServer将数据表中的数据生成添加语句》对你有帮助，请点赞、收藏，并留下你的观点哦！
