如果有表结构: exec sp_MSForEachTable 'insert 目标库名..? select * from ?'
exec sp_MSForEachTable 'insert into 目标库名..? from ?'
select ' set identity_insert on' as t union select ' go' union select 'insert into [目标库名]..'+name+' select * from name' from sysobjects where type='u' union select 'set identity_insert off' union select 'go'将结果拿来运行
exec sp_MSForEachTable 'insert 目标库名..? select * from ?'
/*--数据库数据复制 将一个数据库中的数据复制到另一个数据库 如果某列在目标数据库中为标识列,将不会被复制 适用范围:数据库结构发生了变化,想将旧数据库进行升级 这样就可以根据新的数据库结构创建一个空库,然后 将旧数据库的所有数据复制到新库中 --邹建 203.10--*//*--调用示例 exec p_copydb '源数据库','目标数据库' exec p_copydb 'acc_五医','acc_演示数据8' --*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_copydb] GOcreate proc p_copydb @o_dbname sysname, --要复制数据的数据库--源数据库 @n_dbname sysname, --接收数据的数据库--目标数据库 @cleardb bit=0 --清空目标数据库 as declare @sql nvarchar(4000)--禁用约束,防止复制时的数据冲突 set @sql='declare #tbc cursor for select name,tbname=object_name(parent_obj) from '+@n_dbname+'..sysobjects where xtype in(''C'',''F'')' exec(@sql) declare @name sysname,@tbname sysname open #tbc fetch next from #tbc into @name,@tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ['+@name+']' exec(@sql) fetch next from #tbc into @name,@tbname end close #tbc--复制数据 declare @sql1 varchar(8000) set @sql='declare #tb cursor for select a.name from ' +@o_dbname+'..sysobjects a inner join ' +@n_dbname+'..sysobjects b on a.name=b.name where a.xtype=''U'' and b.xtype=''U''' exec(@sql) open #tb fetch next from #tb into @tbname while @@fetch_status=0 begin select @sql1='' ,@sql='select @sql1=@sql1+'',[''+a.name+'']'' from( select name from '+@o_dbname+'..syscolumns where id in (select id from '+@o_dbname+'..sysobjects where name='''+@tbname+''') ) a inner join ( select name from '+@n_dbname+'..syscolumns where status<>0x80 and id in (select id from '+@n_dbname+'..sysobjects where name='''+@tbname+''') ) b on a.name=b.name' exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out select @sql1=substring(@sql1,2,8000) exec('insert into '+@n_dbname+'..['+@tbname+']('+@sql1 +') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']') if @@error<>0 print('insert into '+@n_dbname+'..['+@tbname+']('+@sql1 +') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']') fetch next from #tb into @tbname end close #tb deallocate #tb--数据复制完成后启用约束 open #tbc fetch next from #tbc into @name,@tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ['+@name+']' exec(@sql) fetch next from #tbc into @name,@tbname end close #tbc deallocate #tbc go
是想求一个SQL脚本exec sp_MSForEachTable 'insert 目标库名..? select * from ?'这个怎么用?
开始菜单-->打开sqlserver查询分析器-->写入: use 你的数据库 go exec sp_MSForEachTable 'insert 目标库名..? select * from ?'-->按F5执行
exec sp_MSForEachTable 'insert 目标库名..? select * from ?'两个?指什么啊? 源库名在哪?求求你们还能写段小完整的代码~~唉~~~
打开查询分析器,复制下面的代码.按F5执行 /*--数据库数据复制 将一个数据库中的数据复制到另一个数据库 如果某列在目标数据库中为标识列,将不会被复制 适用范围:数据库结构发生了变化,想将旧数据库进行升级 这样就可以根据新的数据库结构创建一个空库,然后 将旧数据库的所有数据复制到新库中 --邹建 203.10--*//*--调用示例 exec p_copydb '源数据库','目标数据库' exec p_copydb 'acc_五医','acc_演示数据8' --*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_copydb] GOcreate proc p_copydb @o_dbname sysname, --要复制数据的数据库--源数据库 @n_dbname sysname, --接收数据的数据库--目标数据库 @cleardb bit=0 --清空目标数据库 as declare @sql nvarchar(4000)--禁用约束,防止复制时的数据冲突 set @sql='declare #tbc cursor for select name,tbname=object_name(parent_obj) from '+@n_dbname+'..sysobjects where xtype in(''C'',''F'')' exec(@sql) declare @name sysname,@tbname sysname open #tbc fetch next from #tbc into @name,@tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ['+@name+']' exec(@sql) fetch next from #tbc into @name,@tbname end close #tbc--复制数据 declare @sql1 varchar(8000) set @sql='declare #tb cursor for select a.name from ' +@o_dbname+'..sysobjects a inner join ' +@n_dbname+'..sysobjects b on a.name=b.name where a.xtype=''U'' and b.xtype=''U''' exec(@sql) open #tb fetch next from #tb into @tbname while @@fetch_status=0 begin select @sql1='' ,@sql='select @sql1=@sql1+'',[''+a.name+'']'' from( select name from '+@o_dbname+'..syscolumns where id in (select id from '+@o_dbname+'..sysobjects where name='''+@tbname+''') ) a inner join ( select name from '+@n_dbname+'..syscolumns where status<>0x80 and id in (select id from '+@n_dbname+'..sysobjects where name='''+@tbname+''') ) b on a.name=b.name' exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out select @sql1=substring(@sql1,2,8000) exec('insert into '+@n_dbname+'..['+@tbname+']('+@sql1 +') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']') if @@error<>0 print('insert into '+@n_dbname+'..['+@tbname+']('+@sql1 +') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']') fetch next from #tb into @tbname end close #tb deallocate #tb--数据复制完成后启用约束 open #tbc fetch next from #tbc into @name,@tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ['+@name+']' exec(@sql) fetch next from #tbc into @name,@tbname end close #tbc deallocate #tbc go
exec sp_MSForEachTable 'insert 目标库名..? select * from ?'
union
select ' go'
union
select 'insert into [目标库名]..'+name+' select * from name' from sysobjects where type='u'
union
select 'set identity_insert off'
union
select 'go'将结果拿来运行
/*--数据库数据复制 将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制 适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建 203.10--*//*--调用示例 exec p_copydb '源数据库','目标数据库'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_copydb]
GOcreate proc p_copydb
@o_dbname sysname, --要复制数据的数据库--源数据库
@n_dbname sysname, --接收数据的数据库--目标数据库
@cleardb bit=0 --清空目标数据库
as
declare @sql nvarchar(4000)--禁用约束,防止复制时的数据冲突
set @sql='declare #tbc cursor for select name,tbname=object_name(parent_obj)
from '+@n_dbname+'..sysobjects where xtype in(''C'',''F'')'
exec(@sql)
declare @name sysname,@tbname sysname
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ['+@name+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc--复制数据
declare @sql1 varchar(8000)
set @sql='declare #tb cursor for select a.name from '
+@o_dbname+'..sysobjects a inner join '
+@n_dbname+'..sysobjects b on a.name=b.name
where a.xtype=''U'' and b.xtype=''U'''
exec(@sql)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
select @sql1=''
,@sql='select @sql1=@sql1+'',[''+a.name+'']'' from(
select name from '+@o_dbname+'..syscolumns where id in
(select id from '+@o_dbname+'..sysobjects where name='''+@tbname+''')
) a inner join (
select name from '+@n_dbname+'..syscolumns where status<>0x80 and id in
(select id from '+@n_dbname+'..sysobjects where name='''+@tbname+''')
) b on a.name=b.name'
exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out select @sql1=substring(@sql1,2,8000)
exec('insert into '+@n_dbname+'..['+@tbname+']('+@sql1
+') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']')
if @@error<>0
print('insert into '+@n_dbname+'..['+@tbname+']('+@sql1
+') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']')
fetch next from #tb into @tbname
end
close #tb
deallocate #tb--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ['+@name+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go
http://expert.csdn.net/Expert/topic/2403/2403537.xml?temp=.6569178
楼主用这个吧:
先把表中indentity设置为"否"
insert into hy(hyid,name,number)select hyid,name,number from 数据库2.dbo.hy
另外要注意外键关系
目前的问题是:做一个数据库的安装程序,用VB,有的库比较大,分成两个库,但最终安装后还是一个库
比如Data1,Data2,安装后的库名为Data,如果Data没有安装,直接附加上去,如果装了Data1,那Data2就不好用附加数据库了~:(这就是我的问题能请楼上兄弟们写一段代码吗?
我没有用过存储过程,上面的不会用:(谢谢各位~~~
开始菜单-->打开sqlserver查询分析器-->写入:
use 你的数据库
go
exec sp_MSForEachTable 'insert 目标库名..? select * from ?'-->按F5执行
源库名在哪?求求你们还能写段小完整的代码~~唉~~~
/*--数据库数据复制 将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制 适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建 203.10--*//*--调用示例 exec p_copydb '源数据库','目标数据库'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_copydb]
GOcreate proc p_copydb
@o_dbname sysname, --要复制数据的数据库--源数据库
@n_dbname sysname, --接收数据的数据库--目标数据库
@cleardb bit=0 --清空目标数据库
as
declare @sql nvarchar(4000)--禁用约束,防止复制时的数据冲突
set @sql='declare #tbc cursor for select name,tbname=object_name(parent_obj)
from '+@n_dbname+'..sysobjects where xtype in(''C'',''F'')'
exec(@sql)
declare @name sysname,@tbname sysname
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ['+@name+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc--复制数据
declare @sql1 varchar(8000)
set @sql='declare #tb cursor for select a.name from '
+@o_dbname+'..sysobjects a inner join '
+@n_dbname+'..sysobjects b on a.name=b.name
where a.xtype=''U'' and b.xtype=''U'''
exec(@sql)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
select @sql1=''
,@sql='select @sql1=@sql1+'',[''+a.name+'']'' from(
select name from '+@o_dbname+'..syscolumns where id in
(select id from '+@o_dbname+'..sysobjects where name='''+@tbname+''')
) a inner join (
select name from '+@n_dbname+'..syscolumns where status<>0x80 and id in
(select id from '+@n_dbname+'..sysobjects where name='''+@tbname+''')
) b on a.name=b.name'
exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out select @sql1=substring(@sql1,2,8000)
exec('insert into '+@n_dbname+'..['+@tbname+']('+@sql1
+') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']')
if @@error<>0
print('insert into '+@n_dbname+'..['+@tbname+']('+@sql1
+') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']')
fetch next from #tb into @tbname
end
close #tb
deallocate #tb--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ['+@name+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go
exec p_copydb '源数据库','目标数据库'
比如Data1,Data2,安装后的库名为Data,如果Data没有安装,直接附加上去,如果装了Data1,那Data2就不好用附加数据库了~Data1,Data2都是从MSSQL分离数据库得到的MDF文件这就是我的问题
pengdali(大力 V3.0) , zjcxc(邹建) ( ) ,lynx1111(任我行:CrazyEnglishing) ...你们说说这个问题这样的思路还能行得通?
你们倒是说话啊,急死我了~~
GO---拷贝路径一定要对
exec xp_cmdshell 'copy c:\program files\microsoft sql server\mssql\data\你的旧库的文件.mdf c:\program files\microsoft sql server\mssql\data\你的新库的文件.mdf'
go--附加还原
CREATE DATABASE 你的旧库名
ON PRIMARY (FILENAME = 'c:\program files\microsoft sql server\mssql\data\你的旧库的文件.mdf')
FOR ATTACH
GO--附加新的库
CREATE DATABASE 你的新库名
ON PRIMARY (FILENAME = 'c:\program files\microsoft sql server\mssql\data\你的新库的文件.mdf')
FOR ATTACH
GO
zjcxc(邹建) 在查询分析器中实现了,那我如何在VB中实现呢?