Insert Data From One Server To Another?

Question

If I want to run this sort of query in SQL Server, how can I do the same query from one server I am connected to to another?

I tried adding "[ServerName1]." before "[DatabaseName1].[dbo]..." and "[ServerName2]." before "[DatabaseName2].[dbo]..." but that didn't seem to work.

INSERT INTO [DatabaseName1].[dbo].[TableName]
           ([FieldName])
     SELECT [FieldName] FROM [DatabaseName2].[dbo].[TableName]

Is this possible?

score 11 · Accepted Answer · edited Jul 21 '14 at 11:40

11

Yes you would use the server-name before the whole rest of object-name like:

myserver.mydatabase.dbo.mytable

However you first have to set up linked servers. Look up linked servers in BOL.

edited Jul 21 '14 at 11:40

Sid M

4,340
4
28
49

answered Jul 19 '10 at 21:54

HLGEM

91,883
14
110
181

What is a "BOL"? – Jeff Puckett Mar 17 '17 at 14:53
1

Books Online - the SQL Server help files. – HLGEM Mar 23 '17 at 13:42

Martin Smith · Answer 2 · 2010-07-19T22:05:18.110

8

If you have adhoc distributed queries enabled you can use OPENDATASOURCE. Setting up a linked server is another option. Not sure of the pros and cons of each approach.

INSERT INTO [DatabaseName1].[dbo].[TableName]
SELECT FieldName
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=Server\InstanceName;Integrated Security=SSPI')
    .DatabaseName2.dbo.TableName

edited Jul 19 '10 at 22:05

answered Jul 19 '10 at 21:55

Martin Smith

419,657
83
708
800

FYI this feature has to be enabled: http://stackoverflow.com/questions/14544221/how-to-enable-ad-hoc-distributed-queries – VoteCoffee Jun 01 '15 at 15:37

score 2 · Answer 3 · answered Jul 20 '10 at 01:06

The best way to do this would be to create a "linked server" between the two. You will need appropriate permissions to do this.

Then it's just a matter of accessing the databases using your linkedserver name.

Ex: [linkedserver].databasename.dbo.tablename

To create a linkedserver, go to server objects->right click on linked servers->click on 'new linked server'.

Ashok Yadav · Answer 4 · 2021-03-29T16:06:19.920

0

In SSMS, Go to Query -> 'SQLCMD Mode'

DECLARE @VERSION VARCHAR(1000)
:CONNECT Soruce_Server_Name
SELECT @@VERSION AS [SQL_VERSION]
INTO 
:CONNECT Destination_Server_Name
[MSSQLTips].[dbo].[TEST]

Now on the Destination Server, execute your select command to check your output. For E.g.

SELECT * FROM [CloverInfotech_DB].[dbo].[TEST]

edited Mar 29 '21 at 16:06

answered Mar 28 '21 at 19:30

Ashok Yadav

11
2

Insert Data From One Server To Another?

4 Answers4

Linked