根据Sql表格数据自动生成InsertInto的SP(spRD_AutoGenInsertSql)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--本存储过程可以根据SqlTable的记录内容生成多条Insert Into语句，便于Sql数据的迁移。
-- 2013-03-11 通过初步测试. 

if Exists (Select Top 1 1 From sys.objects Where name = 'spRD_AutoGenInsertSql')
begin
  drop PROC spRD_AutoGenInsertSql
end ;  
go

CREATE PROC spRD_AutoGenInsertSql
    (@tableName varchar(100)) as
begin
    --Declare a cursor to retrieve column specific information for the specified table
    DECLARE cursCol CURSOR FAST_FORWARD FOR 
    SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
    
    OPEN cursCol
    DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
    DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
    DECLARE @dataType nvarchar(1000) --data types returned for respective columns
    SET @string='INSERT '+@tableName+'('
    SET @stringData=''

    DECLARE @colName nvarchar(50)
    FETCH NEXT FROM cursCol INTO @colName,@dataType
    IF @@fetch_status<>0
        begin
        print 'Table '+@tableName+' not found, processing skipped.'
        close curscol
        deallocate curscol
        return
    END

    WHILE @@FETCH_STATUS=0
    BEGIN
    IF @dataType in ('varchar','char','nchar','nvarchar')
    BEGIN
        --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
        SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
    END
    ELSE
    if @dataType in ('text','ntext') --if the datatype is text or something else 
    BEGIN
        SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
    END
    ELSE
    IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
    BEGIN
        SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
    END
    ELSE 
    IF @dataType='datetime'
    BEGIN
        --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
        --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
        --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
        SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
      --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
    END
    ELSE 
    IF @dataType='image' 
    BEGIN
        SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
    END
    ELSE --presuming the data type is int,bit,numeric,decimal 
    BEGIN
        --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
        --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
        SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
    END

    SET @string=@string+@colName+','

    FETCH NEXT FROM cursCol INTO @colName,@dataType
    END
    DECLARE @Query nvarchar(4000)

    SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
    exec sp_executesql @query
    --select @query

    CLOSE cursCol
    DEALLOCATE cursCol
end ;


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO