Insert data from One Database to another Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert data from One Database to another Database

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-11-18 : 11:14:11

Hi,
My requirement is ,
There is a
--> Database 1 and it has
--> Table1 and I have

-------->Databsse 2 and
--------> Table 2..
and Table1 and Table 2 has same structure..and I want to run the INSERT INTO DATABSE2.TABLE2 SELECT * FROM DATABSE1.TABLE1...
Could you please tell me how to acheive this?..Can I use 'Execute SQL task'?..Is it possible to copy data from one databse tbale to another databse table using 'Execute SQL task'?

Your help would be greatly appreciated..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 11:16:22

What's the problem with running that?
INSERT INTO DATABSE2.TABLE2 SELECT * FROM DATABSE1.TABLE1...

No, you're never too old to Yak'n'Roll if you're too young to die.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-11-18 : 11:56:55

My both databases are in different servers...so i cant fire the statement like

insert into db1.tab1
select *
from db2.tab2
where tab2.id =1023

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 12:02:21

need to use linked servers then. or a dts export / import job

check the documentation or books online

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 12:03:32

start here:

http://msdn.microsoft.com/en-us/library/ms188279.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 12:03:41

That is an interesting information!
You can use linked server. (look for that in BOL)
Then you can do:
insert into db1.dbo.tab1
select *
from LinkedSERVERName.db2.dbo.tab2 t2
where t2.id =1023

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources