Insert from another database table 1

[elhaix]

I need a TSQL query that reads from a table in another database on another server, and inserts those values into a table on a different database.


Thanks.

 

[Qik3Coder]

Insert into Server.Database.User.Table
Select * from Server.Database.User.Table

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[beautieee]

I have futher qestion on above TSQL .

insert into SERVER1.DB1.dbo.TableA select * from Server2.DB2.dbo.TableA

Since both server having different login and password, how to specify login and password for above statement?

Thank you.

Beautieee

 

[jrbarnett]

You can either set up a linked server with predefined login credentials, or use openrowset - eg

From server1 in the correct database:

insert into tableA
select * from openrowset ('SQLOLEDB', 'server2';'userid';'password', 'select * from db2.dbo.tableA')

You can run the select statement on its own as a test that the data returned are correct before adding the insert line to copy the data across.

Note that use of OpenRowset needs to be enabled (if you are using SQL Server 2005 or newer), and also use of SQL server logins also needs to be enabled.

John

 

[beautieee]

Dear John,

Thank you for your statement. Tested ok with and without using predefined login.

Above statement defined login source from remote, what about defining login for destination in remote?

insert into tableA ( This tableA from Server1 is remote server)
select * from server2db2.dbo.tableA')

Predefined login is much easier but just try to work out all those possibolity.

Regards,
Beautieee

 

[jrbarnett]

The destination is the local table - ie the one which the result of the OpenRowset is being called from; as a result, provided the account this is running under has permissions to read and write to this table, there is no need to have a specific login set up for the script.

The downsides of this method are:
1. The password is written in plain text, so anybody with access to the server admin tools can find it out.
You can minimise this by having a dedicated login with permissions for this.

2. Any changes to the password need the login information here changed as well otherwise the script will fail.

John

 

[beautieee]

Thank you for your information. Very much appreciated with your explanation.

Regards,
Beautieee

 

[elhaix]

Hey everyone,

Thank you for your input. I ended up setting up a SSIS job for the import (very slick), and was able to add more logic functionality that would have taken a lot longer to simply do with sprocs.