SqlServer表数据导成Insert语句

2010年03月04日星期四 10:09

create table test(

id int not null constraint pk_test primary key,

name nvarchar(100) null,

description nvarchar(200) null

)

生成insert 语句的SQL如下：

set nocount on

select 'insert into test(id, name, description) values(''' + convert(varchar(10), id) + ''', ''' + name + ''',''' +description + ''')' + char(13) + char(10)

from test --可以加过滤条件

set nocount off

第二种，利用存储过程：

创建：

create proc spGenInsertSQL

@TableName as varchar(100)

--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)

执行：

exec spGenInsertSQL tablename

删除：

drop proc spGenInsertSQL

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。