Insert Into Different Database

[TLowder]

Hello,

I'm trying to find the best way to insert data from one MySQL database to another. The structure is identical. I would like to do this in one sql statement as opposed to looping through each record, movenext etc, but I'm not sure how. I've seen examples such as -

"Insert into " & Db2.TableName & " select * from TableName"

I'm unsure on how the connection to Db2.TableName is created. I've always used ADODB.Connection then with that connection .execute sql or open recordsets etc. How do I create the Db2.tablename. Is this an ODBC connection or can I create this using ADO? I think my problem is that I'm ignorant to other database connections as I've only done it the one way, that I can recall.

Thanks,

Tom

 

[PatrickIRL]

SELECT * INTO <Table2> FROM <Table1>

Will create table2 and data.


If the tables already exists then the way to do it is:

INSERT INTO <Table2> (<FIELD_LIST>) SELECT * FROM <Table1>

Patrick

 

[vb5prgrmr]

See

http://www.connectionstrings.com

for future reference and also use your friends (yahoo, google, ask, answers, bing) to search for something like vb6 insert from one database to another or vb6 insert into from one database to another mysql, which using yahoo returns results like...

http://blog.sqlauthority.com/2007/0...r-table-insert-into-select-select-into-table/

which is within the same database, so you would do something like dbo.sqlserverinstancename.databasename.tablename.fieldname I believe, but then again that is from memory and from working with M$ SQL databases...


Also, check the forums list, I know we have a SQL server forum and I'm betting we have a MySQL forum also...



Good Luck

 

[TLowder]

Thanks,

I was grossly overcomplicating this. In my code example I thought db2.table new was a completly different connection. I thought it was an object outside of the quotes Therefor I thought - How in VB do I create a connection or object to be referenced within another SQL statement - is what I didn't know.

The code should instead read -

"Insert into Db2Name.TableName select * from TableName"

While the initial connection is to Db1 - I simply state the Db2 name within the quotes and the sql handles the other connection all on its own. No wonder none of the examples I saw explained what Db2 was, because it was simply Db2's name.

I think I need to get more sleep!

Thanks,

Tom

 

[TLowder]

vb5prgrmr,
And you're right, turns out it's a MySQL question. But I originally thought I need to create another connection, object or ADOX.Table in VB but I didn't know what or how I was supposed to do it via VB.

Thanks,

Tom