Dblink implementation in SQL Server

Data operations between databases on different servers

-- Create a linked server

Exec sp_addmediaserver
'Itsv ',
'',
'Sqlodb ',
'Remote server name or IP address'

Exec sp_add1_srvlogin
'Itsv ',
'False ',
Null,
'Username ',
'Password'

-- Query example

Select
* From itsv. Database Name. DBO. Table Name

-- Import example

Select
* Into table
From itsv. Database Name. DBO. Table Name

-- Delete the linked server when it is no longer in use

Exec sp_dropserver
'Itsv ',
'Droplogins'

-- Connect to remote/LAN data (OpenRowSet/openquery/OpenDataSource)
--

1. OpenRowSet -- Query example
Select
* From
OpenRowSet ('sqloledb ',
'SQL Server name ';
'Username ';
'Password', database name. DBO. Table name)

-- Generate a local table

Select
* Into table
From
OpenRowSet ('sqloledb ',
'SQL Server name ';
'Username ';
'Password', database name. DBO. Table name)

-- Import a local table to a remote table

Insert
OpenRowSet (
'Sqlodb ',
'SQL Server name ';
'Username ';
'Password', database name. DBO. Table name)
Select
* From local table

-- Update local table
Update B
Set B. Column A = A. Column
From
OpenRowSet ('sqloledb ',
'SQL Server name ';
'Username ';
'Password', database name. DBO. Table name)
As
Inner join local Table B
On a. column1
= B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addmediaserver
'Itsv ',
'',
'Sqlodb ',
'Remote server name or IP address'

-- Query

Select
* From
Openquery (itsv,
'Select * from database. DBO. Table name ')

-- Import a local table to a remote table

Insert
Openquery (itsv,
'Select * from database. DBO. Table name ')
Select
* From local table

-- Update local table
Update B
Set B. Column B = A. Column B
From
Openquery (itsv,
'Select * from database. DBO. Table name ')
As
Inner join local Table B
On a. Column A = B. Column

-- 3. OpenDataSource/OpenRowSet

Select
* From
OpenDataSource ('sqloledb ',
'Data source = IP/servername; user id = login name; Password = password'). Test. DBO. roy_ta

-- Import a local table to a remote table

Insert
OpenDataSource ('sqloledb ',
'Data source = IP/servername; user id = login name; Password = password'). database. DBO. Table Name
Select
* From local table