Data operations between different server databases

Use Svtccdata

/******* data operations between different server databases *********/

--Create a linked server
exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' dbsever\mssql2008 '
exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' sa ', ' 123321 '
Set XACT_ABORT on
----If TableB has an ID column, you need to add this sentence
SET Identity_insert TableB on

--query Example
SELECT * from ITSV. Scjzdata.dbo. [2014TestRemote]

--import Example
insert INTO [2014TestRemote] (id,name,phone) Select Id,name,phone from ITSV. Scjzdata.dbo. [2014TestRemote]

--Remove linked server when no longer in use
exec sp_dropserver ' itsv ', ' droplogins '

/******* connecting remote/LAN data (Openrowset/openquery/opendatasource) *********/
/********openrowset**********/

--Enable AD Hoc distributed Queries:
exec sp_configure ' show advanced options ', 1--Turn on premium configuration
Reconfigure with OVERRIDE
exec sp_configure ' Ad Hoc distributed Queries ', 1--Open ad hoc query
Reconfigure with OVERRIDE

--query Example
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' dbsever\mssql2008 '; ' Sa '; ' 123321 ', scjzdata.dbo. [2014TestRemote])

--Raw cost surface
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Import local tables to remote tables
Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) SELECT * FROM local surface

--Update the local surface
Update b set B. Column a=a. Column A from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B on A.column1=b.column1

--Close ad Hoc distributed Queries after use is complete:
exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure with OVERRIDE
exec sp_configure ' show advanced options ', 0
Reconfigure with OVERRIDE

/********openquery usage requires creating a connection **********/

--First create a connection to create a linked server
exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' dbsever\mssql2008 '
exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' sa ', ' 123321 '
--Query
SELECT * FROM OPENQUERY (ITSV, ' SELECT * from scjzdata.dbo.[ 2014TestRemote] ')
--Import local tables to remote tables
Insert OpenQuery (ITSV, ' SELECT * from scjzdata.dbo.[ 2014TestRemote] ') SELECT * FROM [2014TestRemote]

--Update the local surface
Update b set B. column b=a. Column B from OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') as a inner join local table B on a. Column a=b. Column A

--Remove linked server when no longer in use
exec sp_dropserver ' itsv ', ' droplogins '

/********opendatasource/openrowset **********/

SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=dbsever\mssql2008; User Id=sa; password=123321 '). Test.dbo.roy_ta
--Import local tables to remote tables
Insert OpenDataSource (' SQLOLEDB ', ' Data source=dbsever\mssql2008; User Id=sa; Password=123321 '). Scjzdata.dbo. [2014TestRemote] SELECT * FROM [2014TestRemote]

Data operations between different server databases