Data Transfer Between Two Databases

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Data Transfer Between Two Databases

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-22 : 12:41:33

I have two databases DB1 and DB2
With Tables T11,T12 and T21, T22 respectively.
All the tables in the two databases have the same structure.

I need help on a script to clear the contents of Table T21 and T22 in the DB2 database
And load the contents of Table T11 and T12 ( Table have about 2 Million records of data ) into T21 and T22.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-22 : 13:24:45

Truncate tables T21 and T 22 in DB2
And use export/import GUI tool to export tables from one table to other.
Otherwise you have to add linked server .

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-22 : 13:30:24

Both database are on the same server.
Do you have a sample script that I can use ?

quote:
Originally posted by sodeep

Truncate tables T21 and T 22 in DB2
And use export/import GUI tool to export tables from one table to other.
Otherwise you have to add linked server .

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-22 : 13:45:35

It works in Same server too.
Otherwise:
truncate T21 AND T22
AND
insert into T21
select * from T11
go
insert into T22
select * from T12

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-22 : 13:48:07

If it is different databases then
:
Insert into T21
Select * from DB1.owner.T11
go
insert into T22
Select * from DB1.owner.T12

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-22 : 14:48:59

I am wondering if this approach will not impact on perfomance
taking into consideration the number of records ( 2 millon) and
the number of columns ( about 20).

quote:
Originally posted by sodeep

If it is different databases then
:
Insert into T21
Select * from DB1.owner.T11
go
insert into T22
Select * from DB1.owner.T12

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-22 : 14:57:19

If you have clustered and many non-clustered index then ofcourse the performance will be affected. Import/Export will be faster. We do so many times in a day in a production server. 2 million is not a big deal.

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-22 : 15:12:18

Thanks a million !!!!!!!!

quote:
Originally posted by sodeep

If you have clustered and many non-clustered index then ofcourse the performance will be affected. Import/Export will be faster. We do so many times in a day in a production server. 2 million is not a big deal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources