SQL Server: Copying from one DB to another

[Emma123]

Hi I have a database with photos and text. I can copy the text from one DB to another without any problems but I'm having trouble copying the photos. Someone suggested using an INSERT Statement. I'm not sure how it works but this is what I have so far. Can anyone help me I'm really stuck.

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

'Reset server objects
Set Conn3 = Nothing

'Create an ADO connection object
Set Conn4 = Server.CreateObject("ADODB.Connection")

Conn4.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("\db\FileDB_bak.mdb")

'Create an ADO recordset object
Set oRS4 = Server.CreateObject("ADODB.Recordset")

sSQL4 = INSERT INTO Files VALUES oRS3

Set oRS4 = Conn4.Execute(sSQL4)

'Reset server objects
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing


This is the line I'm having problems with sSQL4 = INSERT INTO Files VALUES oRS3 not sure how to put two SQL statement together? Thanks

[abduraooft]

Please read http://www.codingforums.com/postguide.htm
1) Do not cross post your question in multiple forum categories- When posting, there is no excuse to post the same question in multiple categories in hopes of getting a quicker response. This is called spamming, and can get you banned. Choose one category that best accommodates your question, and post it there, once. If you're following up on a question, reply to the original thread, not start a new one! Everyone here is volunteering their time to help out others. The least you can do is make their jobs easier and less confusing.

[guelphdad]

you are not using mysql as your database, as such your question belongs in the general database forum and the subject marked as sql server along with the subject.

second, in my opinion, you should not store images in your database at all, store them in your data directory on your server and store a path to the file in the database itself.

[Fumigator]

Is this a MySQL database you are using?

(edit) sorry, I had this post up for 30 minutes before replying, in which time my question got deprecated.

[Roelf]

you can easily copy records to access, by using IN in your insert query:

INSERT INTO targettablename IN accessfilename SELECT * FROM sourcetablename

for the accessfilename, you have to specify the full filename, including path. Execute this query against an open connection to the SQL database.

some documentation: http://office.microsoft.com/en-us/access/HP010322661033.aspx