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 --要转换的表名
记录Coding学习过程中的点点滴滴,记性不好,特别需要这么一个博客 逸'Coding'Blog