/*
功能：根據表結構生成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
