SQL & ASP & 2 Databases

[Kinl]

Ok... I dont know if I should post this in the sQL or the ASP section of this site. But what I'm going to do is this:

I have an access database with about 35,000 records in it. What I need to do, is take that database, select all records from it, and update a SQL Server 7 database with taht select statement. I know how to do the SQL STatement f for that kinda stuff ...

INSERT INTO tblMyTableName1 (myField1, myField2, etc...)
SELECT myField1, myField2, myField3 FROM tblMyTableName2

but what i'm wondering.... is how do I connect from one database to another.... and do this? Or....should I be using ADO ... and If so, how would I do this through ADO?

A DO WHILE NOT EOF loop?

Thanx for the help/advice..

shorty

 

[yahve]

Hi,

Have you tried using a recordset, populating it with the SELECT from Access then INSERT from the recordset to the SQL db?

Bye.

 

[zippynz]

how about connecting to the first database,
fill an array with the contents of that tbl
then looping throught the array inserting the data using a connection to the new database

 

[yahve]

Hi,

zippynz, why use an array when the content of the table is already in a recordset which can be used to update the second table? It looks to me like it adds a step which is not needed.

Bye.

 

[jonscott8]

If this is a re-occurring process, I'd set up a linked server from your SQLS database to your Access database. Then you can use the SQL you're familiar with:

INSERT INTO MySQLSServer.MySQLSDB.DBO.tblMyTableName1 (myField1, myField2, etc...)
SELECT T2.myField1, T2.myField2, T2.myField3
FROM MyLinkedAccessServer.MyAccessDB.DBO.tblMyTableName2 T2 Jon Hawkins

 

[Kinl]

THis willonly be a one time thing.

thanx

 

[Ody]

doing it in SQL is more efficient, however for ASP open up two ADO connections and push and pull the data from one to the other

 

[zippynz]

yahve

because I didn't know you could do it that way!
but now that I think about it that makes a lot more sense
though for a one time thing probably doesnt make that much difference anyway

 

[Kinl]

Thanx for the help!