在SQL Server數(shù)據(jù)庫中，如果需要讓INSERT語句自動生成，應(yīng)該采用何種方法呢？下面就將為您介紹自動生成INSERT語句的SQL存儲過程，供您參考。

這是建立一個存儲過程，通過執(zhí)行這個存儲過程，將指定的數(shù)據(jù)表中的數(shù)據(jù)導(dǎo)成Insert語句形式

--這是一個可以將表中的數(shù)據(jù)自動生成INSERT語句的存儲過程

--有個BUG，如果是自動增長列，數(shù)據(jù)也會被插入

--在查詢分析器中執(zhí)行完成后，可以從執(zhí)行結(jié)果中自制出SQL腳本。

IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE   proc 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 '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
GO

執(zhí)行如下語句測試結(jié)果：

exec spGenInsertSQL test

【編輯推薦】

SQL語句中的SELECT DISTINCT

SQL數(shù)據(jù)庫壓縮語句簡介

教您用事務(wù)一次處理多條SQL語句

通過執(zhí)行多條SQL語句實現(xiàn)數(shù)據(jù)庫事務(wù)

存儲過程優(yōu)化的SQL語句寫法