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

最新推荐文章于 2023-04-26 09:40:44 发布

weixin_34326429

最新推荐文章于 2023-04-26 09:40:44 发布

阅读量220

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/sunth/archive/2013/06/05/3118312.html

版权

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 --要转换的表名

转载于:https://www.cnblogs.com/sunth/archive/2013/06/05/3118312.html