复制数据库

心灵彩虹 2011-03-17 12:32:41
存储过程实现【数据库数据复制】功能,其中 目标数据库结构> 源数据库结构 也就是说【目标数据库是在源数据库的修改以后,增加了字段和存储过程的】。

要求:
1用存储过程实现 2不能修改目标数据库字段和存储过程

另外这个是邹老大的

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]
GO


/*--数据库数据复制

将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制

适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中

--邹建 2003.10(引用请保留此信息)--*/

/*--调用示例

exec p_copydb 'bns_aa','bns_new'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/
create proc p_copydb
@o_dbname sysname, --要复制数据的数据库--源数据库
@n_dbname sysname --接收数据的数据库--目标数据库
as
declare @sql nvarchar(4000)

--禁用约束/触发器,防止复制时的数据冲突
set @sql='declare #tbc cursor for select name
from '+@n_dbname+'..sysobjects where xtype=''U'' and status>=0'
exec(@sql)

declare @tbname sysname
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ALL'
exec(@sql)
set @sql='alter table '+@n_dbname+'..['+@tbname+'] disable trigger ALL'
exec(@sql)
fetch next from #tbc into @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 @tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ALL'
exec(@sql)
set @sql='alter table '+@n_dbname+'..['+@tbname+'] enable trigger ALL'
exec(@sql)
fetch next from #tbc into @tbname
end
close #tbc
deallocate #tbc
go



本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zjcxc/archive/2003/12/29/20081.aspx
...全文
125 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2011-03-17
  • 打赏
  • 举报
回复
SQL复制表结构的通用存储过程 

-- Transfer对象的重要属性

-- 1. 属性

属性名 类型 描述
--------------------------------- ------------------- --------------------
CopyAllDefaults Boolean 所有默认值
CopyAllObjects Boolean 所有对象
CopyAllRules Boolean 所有规则
CopyAllStoredProcedures Boolean 所有存储过程
CopyAllTables Boolean 所有表
CopyAllTriggers Boolean 所有触发器
CopyAllUserDefinedDatatypes Boolean 所有用户自定义类型
CopyAllViews Boolean 所有视图
CopyData Boolean 所有数据
DestDatabase String 目标对象数据库
DestLogin String 目标数据库登陆用户名
DestPassword String 目标数据库登陆密码
DestServer String 目标服务器
DestUseTrustedConnection Boolean 用户信任连接
DropDestObjectsFirst Boolean 是否先删除目标对象
IncludeDependencies Boolean 是否包含依靠对象
ScriptType Boolean 脚本类型

-- 2. 重要方法:

方法名称 功能描述
--------------------------- --------------------------
AddObject 增加对象
AddObjectByName 通过对象名称增加对象

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]
GO

/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移
存储过程实现源数据库到目标数据库的对象和数据的复制
要求源数据库和目标数据库在同一服务器
如果是要实现不同服务器之间的复制,则需要增加验证信息
--邹建 2005.07(引用请保留此信息)--*/

/*--调用示例

CREATE DATABASE test
EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test'
DROP DATABASE test
--*/
CREATE PROCEDURE P_CopyDB
@Des_DB sysname, --目标数据库
@Obj_Type nvarchar(4000)=N'',--复制的对象类型,可以是下列字符串列表:
-- O 所有对象,D 默认值,R 规则,P 存储过程
-- T 表,TR 触发器,DT 用户定义数据类型
-- V 视图,DATA 数据,DEL 删除目标对象
@Source_DB sysname=N'', --源数据库
@ServerName sysname=N'', --服务器名
@UserName sysname=N'', --用户名,不指定则表示使用 Windows 身份登录
@pwd sysname=N'' --密码
AS
SET NOCOUNT ON
DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int,
@err int,@src varchar(255), @desc varchar(255)

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME
IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME()

--创建sqldmo对象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err

--连接服务器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
IF @err<>0 GOTO lb_Err

EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
END
ELSE
EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwd

IF @err<>0 GOTO lb_Err

--获取数据库集
EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
IF @err<>0 GOTO lb_Err

--选择源数据库
EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
IF @err<>0 GOTO lb_Err

--选择目标数据库
EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
IF @err<>0 GOTO lb_Err

--设置复制的对象
EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
IF @err<>0 GOTO lb_Err

--设置目标服务器信息
EXEC @err=sp_oasetproperty @TransferID,'DestServer',@ServerName
IF @err<>0 GOTO lb_Err

--设置连接用户
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
IF @err<>0 GOTO lb_Err
END
ELSE
BEGIN
EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
IF @err<>0 GOTO lb_Err
END

--设置复制对象信息
EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
IF @err<>0 GOTO lb_Err

DECLARE tb CURSOR FAST_FORWARD LOCAL
FOR
SELECT Name FROM(
SELECT KeyWord=N',D,', Name=N'CopyAllDefaults' UNION ALL
SELECT KeyWord=N',O,', Name=N'CopyAllObjects' UNION ALL
SELECT KeyWord=N',R,', Name=N'CopyAllRules' UNION ALL
SELECT KeyWord=N',P,', Name=N'CopyAllStoredProcedures' UNION ALL
SELECT KeyWord=N',T,', Name=N'CopyAllTables' UNION ALL
SELECT KeyWord=N',TR,', Name=N'CopyAllTriggers' UNION ALL
SELECT KeyWord=N',DT,', Name=N'CopyAllUserDefinedDatatypes' UNION ALL
SELECT KeyWord=N',V,', Name=N'CopyAllViews' UNION ALL
SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL
SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst'
)A WHERE CHARINDEX(KeyWord,
CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0
OPEN tb
FETCH tb INTO @src
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @err=sp_oasetproperty @TransferID,@src,1
IF @err<>0 GOTO lb_Err
FETCH tb INTO @src
END
CLOSE tb
DEALLOCATE tb

--复制对象
EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
IF @err<>0 GOTO lb_Err

--结束
SET @err=0
GOTO lb_Exit

--错误处理
lb_Err:
EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT
RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc)
RETURN -1

lb_Exit:
EXEC sp_OADestroy @Dbid
EXEC sp_OADestroy @srvid
EXEC sp_OADestroy @TransferID
RETURN @err
GO
心灵彩虹 2011-03-17
  • 打赏
  • 举报
回复
答案如下:

--复制表数据的脚本
--加入存在临时表,删除临时表
if exists (select 1
from sysobjects
where id = object_id('tmp_ITEM')
and type = 'U')
drop table tmp_ITEM
go
if exists (select 1
from sysobjects
where id = object_id('ADJUSTHISTORY')
and type = 'U')
drop table tmp_ADJUSTHISTORY
go

if exists (select 1
from sysobjects
where id = object_id('CUSTOMER')
and type = 'U')
drop table tmp_CUSTOMER
go

--创建临时表并将表数据复制到临时表中

select * into tmp_ITEM from ITEM
select * into tmp_ADJUSTHISTORY from ADJUSTHISTORY
select * into tmp_CUSTOMER from CUSTOMER
GO
--删除表
DROP TABLE ITEM
DROP TABLE ADJUSTHISTORY
DROP TABLE CUSTOMER

GO
--创建新表
USE [LMS]
GO
/****** 对象: Table [dbo].[tmp_ITEM] 脚本日期: 03/17/2011 15:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--创建item表
CREATE TABLE [dbo].[ITEM](
[ITEMCD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ITEMNAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ITEMNM] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[TRADEMARK] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[DATA1] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DATA2] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DATA3] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DATA4] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[IMGPATH] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[ITEMTY] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[NOTE] [varchar](250) COLLATE Chinese_PRC_CI_AS NULL,
[DELMODE] [varchar](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UPDT] [datetime] NOT NULL,
[UPID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UPGM] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CRDT] [datetime] NOT NULL,
[CRID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CRGM] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[IMG] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--创建ADJUSTHISTORY表
CREATE TABLE [dbo].[ADJUSTHISTORY](
[ADJUSTCD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ITEMCD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TYPECD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[DATE] [decimal](8, 0) NOT NULL,
[UNITPRICE] [decimal](10, 2) NULL CONSTRAINT [DF__ADJUSTHIS__UNITP__7B1C2680] DEFAULT (0),
[UNITCD] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[QTY] [decimal](8, 0) NULL CONSTRAINT [DF__ADJUSTHISTO__QTY__7C104AB9] DEFAULT (0),
[ACTUALQTY] [decimal](8, 0) NULL,
[ADJUSTTYPE] [varchar](1) COLLATE Chinese_PRC_CI_AS NULL,
[ADJUSTQTY] [decimal](8, 0) NULL,
[CRDT] [datetime] NOT NULL,
[CRID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CRGM] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[BIKO] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK__ADJUSTHISTORY__7D046EF2] PRIMARY KEY CLUSTERED
(
[ADJUSTCD] ASC
) ON [PRIMARY]
) ON [PRIMARY]
--创建CUSTOMER表
CREATE TABLE [dbo].[CUSTOMER](
[CUSTCD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CUSTNAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CUSTNM] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[TEL1] [varchar](4) COLLATE Chinese_PRC_CI_AS NULL,
[TEL2] [decimal](18, 0) NULL,
[FAX1] [varchar](4) COLLATE Chinese_PRC_CI_AS NULL,
[FAX2] [decimal](18, 0) NULL,
[QQ] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DISCOUNT] [decimal](18, 0) NULL,
[MOBILETEL] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[ADDRESS] [varchar](250) COLLATE Chinese_PRC_CI_AS NULL,
[POST] [decimal](18, 0) NULL,
[EMAIL] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[CONTACT] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[PAYCD] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[BANKCD] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[BANKNO] [decimal](18, 0) NULL,
[NOTE] [varchar](250) COLLATE Chinese_PRC_CI_AS NULL,
[DELMODE] [varchar](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UPDT] [datetime] NOT NULL,
[UPID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UPGM] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CRDT] [datetime] NOT NULL,
[CRID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CRGM] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK__CUSTOMER__334B710A] PRIMARY KEY CLUSTERED
(
[CUSTCD] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

go
--插入ADJUSTHISTORY表数据
insert into ADJUSTHISTORY (ADJUSTCD, ITEMCD, TYPECD, DATE, UNITPRICE, UNITCD, QTY, ACTUALQTY, ADJUSTTYPE, ADJUSTQTY, CRDT, CRID, CRGM, BIKO)
select ADJUSTCD, ITEMCD, TYPECD, DATE, UNITPRICE, UNITCD, QTY, ACTUALQTY, ADJUSTTYPE, ADJUSTQTY, CRDT, CRID, CRGM, BIKO
from tmp_ADJUSTHISTORY
--插入CUSTOMER表数据
INSERT INTO CUSTOMER (
CUSTCD,CUSTNAME,CUSTNM, TEL1,TEL2,FAX1, FAX2,QQ,DISCOUNT,
MOBILETEL,ADDRESS,POST, EMAIL,CONTACT,PAYCD,BANKCD,BANKNO,
NOTE,DELMODE,UPDT,UPID, UPGM,CRDT,CRID,CRGM )
select CUSTCD,CUSTNAME,CUSTNM, TEL1,TEL2,FAX1, FAX2,QQ,DISCOUNT,
MOBILETEL,ADDRESS,POST, EMAIL,CONTACT,PAYCD,BANKCD,BANKNO,
NOTE,DELMODE,UPDT,UPID, UPGM,CRDT,CRID,CRGM
from tmp_CUSTOMER
--从临时表中复制数据到新表中
--INSERT INTO ITEM select * from tmp_ITEM
go


心灵彩虹 2011-03-17
  • 打赏
  • 举报
回复
感谢胡兄,问题解决了[Quote=引用 2 楼 fredrickhu 的回复:]
SQL code
SQL复制表结构的通用存储过程

-- Transfer对象的重要属性

-- 1. 属性

属性名 类型 描述
--------------------------------- ------------------- --------------------
CopyA……
[/Quote]

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧