copying records of 1 table of 1 database to 2ndtable of 2nd database

[557]

i have 2 tables in 2 different databases in sqlserver.
i need to copy all records of one table in one database into another table of another database.

now, i'm doing it this way......

1)i copy all records of 1st table into one recordset using a connection to 1st databse and then, i close connection to that database
2)i open the 2nd table as another recordset using another connection
3) i do a rs1.movefirst and using addnew, i copy the fields of rs1 into rs2's fields using several statements like
rs2.addnew
rs2("field1") = rs1("field1")
rs2("field2") = rs1("field2")
rs2("field3") = rs1("field3")
rs2("field4") = rs1("field4")


rs2.update

4) then i do rs1.movenext and loop thru same procedure till all records of rs1 have been copied to rs2

i have about 1096639 records in 1st table. so, this takes tooooooooooooooooooooooooo long. can anyone tell me how i can copy records of one table in one database into another table of another database in one single sqlstatement or assignment statement. is it possible to assign rs2= rs1?
or can i use

insert into table2 select * from table1

when 2 databases are involved?

 

[fredericofonseca]

DO NOT do it like you are doing now. It's too slow.

Go to Enterprise Manager and look at the possibility of using DTS (Data Transformation Services). There is a option to do a Transforma Data Task or a Copy SQL Server Objects that you can use for that purpose.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[johnwm]

You could also look at the Insert Into ... From syntax.

I find SQL server books on line very useful for all MSSQL details - it's downloaded from:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts