sql server 跨数据库复制表

来源：互联网发布：淘宝店铺怎么换客服编辑：程序博客网时间：2024/05/16 16:23

复制表之前先把表设计主键改为不自动增长
数据复制完，主键改为自动增长

Exec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP

EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='10.132.163.14' --要访问的服务器

EXEC sp_addlinkedsrvlogin
'DBVIP', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'Py2014dy' --密码

insert into [puyun_analysis_2].[dbo].sms_send_history_2015_09
select * from DBVIP.qianniu_trade_DB2.dbo.sms_send_history_2015_09

select COUNT(shid) from [puyun_analysis_2].[dbo].sms_send_history_2015_09
select COUNT(shid) from DBVIP.qianniu_trade_DB2.dbo.sms_send_history_2015_09

USE [puyun_analysis_4]
GO

/****** Object: Table [dbo].[sms_send_history_2015_12] Script Date: 02/02/2016 15:37:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[sms_send_history_2015_09](
[shid] [bigint] IDENTITY(1,1) NOT NULL,
[tid] [varchar](50) NULL,
[seller_nick] [varchar](50) NULL,
[send_type] [decimal](2, 0) NULL,
[send_status] [decimal](1, 0) NULL,
[buyer_nick] [varchar](50) NULL,
[mobile] [decimal](12, 0) NULL,
[send_time] [datetime] NULL,
[charging] [decimal](1, 0) NULL,
[sms_content] [varchar](500) NULL,
[company_sid] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[shid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

0 0