sql server 跨数据库复制表

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








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