--将表数据生成SQL脚本的存储过程CREATE PROCEDURE dbo.UspOutputData @tablename sysname AS declare @column varchar(1000) declare @columndata varchar(1000) declare @sql varchar(4000) declare @xtype tinyint declare @name sysname declare @objectId int declare @objectname sysname declare @ident int set nocount on set @objectId=object_id(@tablename) if @objectId is null -- 判斷對象是否存在 begin print 'The object not exists' return end set @objectname=rtrim(object_name(@objectId)) if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密 begin print 'object not in current database' return end if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table begin print 'The object is not table' return end select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 if @ident is not null print 'SET IDENTITY_INSERT '+@TableName+' ON' declare syscolumns_cursor cursorfor select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid open syscolumns_cursor set @column='' set @columndata='' fetch next from syscolumns_cursor into @name,@xtype while @@fetch_status < >-1 begin if @@fetch_status < >-2 begin if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理 begin set @column=@column+case when len(@column)=0 then'' else ','end+@name set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','',' end +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier else @name end end end fetch next from syscolumns_cursor into @name,@xtype end close syscolumns_cursor deallocate syscolumns_cursor set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename print '--'+@sql exec(@sql) if @ident is not null print 'SET IDENTITY_INSERT '+@TableName+' OFF' GOexec UspOutputData 你的表名
就是把表里的数据写语句 再 elect 出来。
我一般使用工具来导,如SQLBrowser
我的意思是生成insert into 语句 例如: a表有三个字段,记录为: a1 a2 a3 1 n b 2 c b 3 y i 如何将该表中的记录导出如下的sql语句: insert into a(a1,a2,a3) value(1,'n','b') insert into a(a1,a2,a3) value(2,'d','b') insert into a(a1,a2,a3) value(3,'y','i')
举个例子吧 最终的语句要写成这样 insert into tableA select '1','2','3' union all select 2','3','4' ... ... 你先要把现在的数据准备成这样 select '1','2','3' union all select 2','3','4'你可以这么写select 'select ' + 字段A + ',','' + 字段B +' union all '把这个执行的结果变成文本 拷贝出来在最前面加上insert into tableA 在修改修改错误 执行就可以了
@tablename sysname
AS
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectId int
declare @objectname sysname
declare @ident int set nocount on
set @objectId=object_id(@tablename) if @objectId is null -- 判斷對象是否存在
begin
print 'The object not exists'
return
end
set @objectname=rtrim(object_name(@objectId)) if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
begin
print 'object not in current database'
return
end if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table
begin
print 'The object is not table'
return
end select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' ON' declare syscolumns_cursor cursorfor select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid open syscolumns_cursor
set @column=''
set @columndata=''
fetch next from syscolumns_cursor into @name,@xtype while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理 begin
set @column=@column+case when len(@column)=0 then'' else ','end+@name set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','
end +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
else @name end end end fetch next from syscolumns_cursor into @name,@xtype end close syscolumns_cursor
deallocate syscolumns_cursor set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename print '--'+@sql
exec(@sql) if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF' GOexec UspOutputData 你的表名
就是把表里的数据写语句 再 elect 出来。
例如:
a表有三个字段,记录为:
a1 a2 a3
1 n b
2 c b
3 y i
如何将该表中的记录导出如下的sql语句:
insert into a(a1,a2,a3) value(1,'n','b')
insert into a(a1,a2,a3) value(2,'d','b')
insert into a(a1,a2,a3) value(3,'y','i')
最终的语句要写成这样
insert into tableA
select '1','2','3'
union all
select 2','3','4'
... ...
你先要把现在的数据准备成这样
select '1','2','3'
union all
select 2','3','4'你可以这么写select 'select ' + 字段A + ',','' + 字段B +' union all '把这个执行的结果变成文本 拷贝出来在最前面加上insert into tableA 在修改修改错误 执行就可以了
包裹表结构,数据库中已有数据
MsSQL 2.0版(西恩软件)
toad for sqlserver都可以的