SQL_server 将表中的记录 转换成 Insert(插入) SQL 语句

?

create proc spGenInsertSQL

 

  @TableName as varchar(100)

 

  as

 

  --declare @TableName varchar(100)

 

  --set @TableName = 'orders'

 

  --set @TableName = 'eeducation'

 

  DECLARE xCursor CURSOR FOR

 

  SELECT name,xusertype

 

  FROM syscolumns

 

  WHERE (id = OBJECT_ID(@TableName))

 

  declare @F1 varchar(100)

 

  declare @F2 integer

 

  declare @SQL varchar(8000)

 

  set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''

 

  OPEN xCursor

 

  FETCH xCursor into @F1,@F2

 

  WHILE @@FETCH_STATUS = 0

 

  BEGIN

 

    set @sql = @sql +

 

          + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '   else '+' end

 

          + 'replace(ISNULL(cast(' + @F1 + ' as varchar(8000)),''NULL''),'''''''','''''''''''')'  

 

          + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '   else '+' end

 

          + char(13) + ''','''  

 

    FETCH NEXT FROM xCursor into @F1,@F2

 

  END

 

  CLOSE xCursor

 

  DEALLOCATE xCursor

 

  set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName

 

  exec (@sql)

 

   

 

  go

 

 

以上在查询分析器中运行.

再运行

EXEC spGenInsertSQL tableName   --要转换的表名