根据表结构生成 insert into 语句

/*
 功能：根据表结构生成insert into 语句
 打工仔：zheng_jy (引用请保留此信息)
 日期：2010/05/06
 备注：这里假设数据类型都是varchar nvarchar numeric int其他请自行修改
*/
declare @strtablename varchar(50)
declare @strKeyName varchar(50)
declare @strKeyValue varchar(50)
declare @strTempColname varchar(50)
declare @strTempColType varchar(50)
declare @strSQL varchar(5000)
declare @intCount int
declare @intCount_Value int
declare @strTempValue varchar(200)
Declare @sql nvarchar(2000)
Declare @sqlValue nvarchar(200)
Declare @strValue nvarchar(200)

Set @strTempColname=''
Set @strSQL=''
Set @intCount=0
Set @strTempValue=''

------------------------------------
--初始化参数，本sql暂时只支持一个key，多key请自己修改
Set @strtablename='DebtApplyForm'--table名
Set @strKeyName='ApplyFormId'--key名
Set @strKeyValue='A200979469A1'--key值
------------------------------------

if @strtablename <>''
begin
------------------------------------
Set @strSQL='insert into '+@strtablename +' ('
Set @intCount=0
------------------------------------

Create table #temp(Colname varchar(50),ColType varchar(50))
insert into #temp
SELECT  t1.ColName,t1.coltype FROM --,t1.ColNameCN,t1.coltype,t1.collength,t1.dcollength
(SELECT c.name ColName,value ColNameCN, systypes.name coltype,c.isnullable ,COLUMNPROPERTY(c.id,c.name,'PRECISION') collength,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) dcollength
FROM systypes,sys.sysobjects o,syscolumns c
LEFT JOIN ::fn_listextendedproperty(N'MS_Description', N'user', N'dbo', N'table',@strtablename, N'column', default) d
ON objname = c.name COLLATE Chinese_PRC_CI_AS
WHERE  c.xusertype = systypes.xusertype 
AND c.id = o.id
AND o.name =@strtablename) t1
LEFT JOIN
(SELECT i.name keyname,c.name keykey
FROM sys.indexes i,sys.sysindexkeys sd, sys.all_columns c, sys.sysobjects o
WHERE i.index_id = sd.indid
AND i.object_id = o.id
AND i.is_primary_key = 1
AND sd.id = o.id
AND c.object_id = o.id
AND c.column_id = sd.colid
AND o.xtype='u'
AND o.name =@strtablename) t2 ON t1.ColName = t2.keykey

--select * from #temp
Set @intCount=@@rowcount
Set @intCount_Value=@intCount

 Declare CursorTemp Cursor
        For Select ColName,ColType
              From #temp
    Open CursorTemp
    While 1 = 1
    Begin
        Fetch Next From CursorTemp Into @strTempColname,@strTempColType

        if not (@@Fetch_Status = 0)
            Break
        Begin
   If @intCount <> 1
    Begin
     Set @strSQL=@strSQL+@strTempColname+','
     Set @intCount=@intCount-1
    End
   Else
    Begin
     Set @strSQL=@strSQL+@strTempColname+')'
     Set @intCount=@intCount-1
    End
        End
    End
    Close CursorTemp
    DeAllocate CursorTemp

 
 Set @strSQL=@strSQL+ 'Values('

------------------------------------------------------------------------------
 Declare CursorTempValues Cursor
        For Select ColName,ColType
              From #temp
    Open CursorTempValues
    While 1 = 1
    Begin
        Fetch Next From CursorTempValues Into @strTempColname,@strTempColType

        if not (@@Fetch_Status = 0)
            Break
        Begin
   
   Set @strValue=''

   Set @sql='Select @sqlValue='+@strTempColname +' From '+ @strtablename +' Where '+ @strKeyName +'='''+@strKeyValue+''''
   exec sp_executesql  @sql,N'@sqlValue nvarchar(200) output',@strValue output
   
   if @strTempColType='varchar' or @strTempColType='nvarchar'
   Begin
    Set @strSQL=@strSQL+''''+@strValue+''''
   End 
   Else
   Begin
    if @strValue=''
     Set @strValue='0'
    Set @strSQL=@strSQL+@strValue
   End 

   If @intCount_Value <> 1
    Begin
     Set @strSQL=@strSQL+','
     Set @intCount_Value=@intCount_Value-1
    End
   Else
    Begin
     Set @strSQL=@strSQL+')'
     Set @intCount=@intCount-1
    End
        End
    End
    Close CursorTempValues
    DeAllocate CursorTempValues

 Print @strSQL
 drop table #temp
end

--以下为测试语句
--set IDENTITY_INSERT DebtApplyForm  on
--delete from DebtApplyForm where ApplyFormId='A200979469A1'
--insert into DebtApplyForm (ApplyFormId,IDNo,ApplyDate,InterestStopDate,ApplyName,BirthDate,HomeAddr,AreaRegisterTel,RegisterTel,ExtRegisterTel,ContactAddr,AreaHomeTel,HomeTel,ExtHomeTel,AreaMobleTel,MobleTel,ContactName,RelationShip,AreaContactTel,ContactTel,ExtContactTel,AreaContactMebleTel,ContactMebleTel,OtherDebt,EssentialID1,EssentialID2,AcceptType,TransferBnkId,NonBankLoanAmt,ContactAddrZipCode,HomeAddrZipCode,Remark,LetterSrNo,UnDiscoverBNKID1,UnDiscoverBNKID2,UnDiscoverBNKID3,UnDiscoverBNKID4,UnDiscoverBNKID5,UnDiscoverBNKID6,BranchId,CreateDate,CreateUserId,PassUserId,PassDate,PassStatus,SuperId,TotalAmt,TotalPayAmt,PayCountByAll,DelayCountByAll,ShortPayCountByAll,OverCountByAll,NewAccuPayAmt,AccuPayAmt,ABPercent,LastPayDate,LastVAFirstDate,FirstPayDate,LastPayCompleteDate,NextPayAmtDate,SDate,PassTime)Values('A200979469A1','A200979469','20100505','','洪秀全','19890101','645646','','4564654','','4654646464','','0999999','','','','DDD','02','099','678687','68768','','','N','Y','Y','A','057',0,'','','',0,'','','','','','','','20100505','000005','','','S','070100',14277.00,14200.00,1.00,0.00,0.00,0.00,0.00,14277.00,0.00,'20100410','','','20100410','20100710','','')
--set IDENTITY_INSERT DebtApplyForm  off

转载于:https://www.cnblogs.com/umen/archive/2010/05/06/1728994.html