你肯定有過這樣的煩惱，同樣的表，不同的數(shù)據(jù)庫，加入你不能執(zhí)行select ?insert
那么你肯定需要一條這樣的存儲過程，之需要傳入表明，就會給你生成數(shù)據(jù)的插入語句。
當(dāng)然數(shù)據(jù)表數(shù)量太大，你將最好用別的方式
?
Create proc [ dbo ] . [ spGenInsertSQL ] ( @tablename varchar ( 256 )) as begin declare @sql varchar ( 8000 ) declare @sqlValues varchar ( 8000 ) set @sql = ' ( ' set @sqlValues = ' values ( '' + ' select @sqlValues = @sqlValues + cols + ' + '' , '' + ' , @sql = @sql + ' [ ' + name + ' ], ' from ( select case when xtype in ( 48 , 52 , 56 , 59 , 60 , 62 , 104 , 106 , 108 , 122 , 127 ) then ' case when ' + name + ' is null then '' NULL '' else ' + ' cast( ' + name + ' as varchar) ' + ' end ' when xtype in ( 58 , 61 ) -- then '''''''''+convert(char(23),'+name+',121)+''''''''' --datetime then ' case when ' + name + ' is null then '' NULL '' else ' + ''''''''' + ' + ' cast( ' + name + ' as varchar) ' + ' + ''''''''' + ' end ' when xtype in ( 167 ) then ' case when ' + name + ' is null then '' NULL '' else ' + ''''''''' + ' + ' replace( ' + name + ' , '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end ' when xtype in ( 231 ) then ' case when ' + name + ' is null then '' NULL '' else ' + ''' N '''''' + ' + ' replace( ' + name + ' , '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end ' when xtype in ( 175 ) then ' case when ' + name + ' is null then '' NULL '' else ' + ''''''''' + ' + ' cast(replace( ' + name + ' , '''''''' , '''''''''''' ) as Char( ' + cast (length as varchar ) + ' ))+ ''''''''' + ' end ' when xtype in ( 239 ) then ' case when ' + name + ' is null then '' NULL '' else ' + ''' N '''''' + ' + ' cast(replace( ' + name + ' , '''''''' , '''''''''''' ) as Char( ' + cast (length as varchar ) + ' ))+ ''''''''' + ' end ' else ''' NULL ''' end as Cols,name from syscolumns where id = object_id ( @tablename ) ) T set @sql = ' select '' INSERT INTO [ ' + @tablename + ' ] ' + left ( @sql , len ( @sql ) - 1 ) + ' ) ' + left ( @sqlValues , len ( @sqlValues ) - 4 ) + ' ) '' from ' + @tablename print @sql exec ( @sql ) end
?
最后的結(jié)果：
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C','任務(wù)調(diào)度系統(tǒng)',18,'2013-01-02 21:42:30.013','',NULL,'2013-01-02 21:42:30.013')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0004A6F3-EC28-4D1F-BA40-0FC4B2218C92','任務(wù)調(diào)度系統(tǒng)',18,'2013-07-09 19:36:00.060','',NULL,'2013-07-09 19:36:00.060')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('00094D35-7B51-4EA3-871E-CE17E293B157','任務(wù)調(diào)度系統(tǒng)',18,'2013-05-16 15:21:20.070','',NULL,'2013-05-16 15:21:20.070')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('000BFBB0-B37D-4D6E-9FA2-3069D4F18F84','任務(wù)調(diào)度系統(tǒng)',18,'2013-04-11 11:41:50.030','',NULL,'2013-04-11 11:41:50.030')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('000C2CBC-E358-4469-BC2C-04F4DDCD72CD','任務(wù)調(diào)度系統(tǒng)',18,'2013-05-06 16:07:00.037','',NULL,'2013-05-06 16:07:00.037')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('000CB795-40EC-4783-B7A4-8D298DF63B70','任務(wù)調(diào)度系統(tǒng)',18,'2013-01-23 20:52:30.030','',NULL,'2013-01-23 20:52:30.030')
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
