程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQL Server存儲過程生成insert語句實例

SQL Server存儲過程生成insert語句實例

編輯:更多數據庫知識

你肯定有過這樣的煩惱,同樣的表,不同的數據庫,加入你不能執行select  insert
那麼你肯定需要一條這樣的存儲過程,之需要傳入表明,就會給你生成數據的插入語句。
當然數據表數量太大,你將最好用別的方式

復制代碼 代碼如下:
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

SQL語句

最後的結果:
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C','任務調度系統',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','任務調度系統',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','任務調度系統',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','任務調度系統',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','任務調度系統',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','任務調度系統',18,'2013-01-23 20:52:30.030','',NULL,'2013-01-23 20:52:30.030')

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved