Copying data from one database to another

[ozzii]

Hi

I am using the following code to copy data from one database table into another database table:

SELECT * INTO Products From exportdb.mdb.exporttable

However the query simply deletes everything in the Products table before copying the content of the exporttable.

what is the syntax to run a similar query so that data is simply appended to the products table instead of deleting the entire contents before copying the exporttable?

[ADezii]

Hi

I am using the following code to copy data from one database table into another database table:

SELECT * INTO Products From exportdb.mdb.exporttable

However the query simply deletes everything in the Products table before copying the content of the exporttable.

what is the syntax to run a similar query so that data is simply appended to the products table instead of deleting the entire contents before copying the exporttable?

If I understand you correctly, here is some code which will hopefully point you in the right direction. Let me know if this is what you were looking for:

'The following code snippet will Import a Table from an
'External Database and append its Records to a Table in the current Database
Dim MYtdf As TableDef
 
For Each MYtdf In CurrentDb.TableDefs
  'DELETE Imported Table if it exists
  If MYtdf.Name = "tblEmployeesAppend" Then
    CurrentDb.TableDefs.Delete MYtdf.Name
  End If
Next
 
'Import tblEmployees from C:\Test2\Test2.mdb and rename it tblEmployeesAppend
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Test2\Test2.mdb", acTable, "tblEmployees", "tblEmployeesAppend"
 
DoCmd.SetWarnings False     'Turn System Messages OFF
  'Run Append Query adding all Records from tblEmployeesAppend to tblEmployees
  DoCmd.OpenQuery "qryAppendToEmployee"
DoCmd.SetWarnings True      'Turn System Messages ON
 
'No need to keep it around
CurrentDb.TableDefs.Delete "tblEmployeesAppend"

[NeoPa]

I think the question should have been more like :
What is the correct SQL syntax to append data into a table rather than to create a table?
My existing query

SELECT * INTO Products From exportdb.mdb.exporttable

clears out the table prior to adding the new data :confused:

The syntax needed is INSERT INTO rather than SELECT INTO.

INSERT INTO Statement
Adds a record or multiple records to a table. This is referred to as an append query.

Syntax
Multiple-record append query:

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

The INSERT INTO statement has these parts:

Part Description
target The name of the table or query to append records to.
field1, field2 Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
externaldatabase The path to an external database. For a description of the path, see the IN clause.
source The name of the table or query to copy records from.
tableexpression The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.
value1, value2 The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').

[ozzii]

I think the question should have been more like :
What is the correct SQL syntax to append data into a table rather than to create a table?
My existing query

SELECT * INTO Products From exportdb.mdb.exporttable

clears out the table prior to adding the new data :confused:

The syntax needed is INSERT INTO rather than SELECT INTO.

Yes you are right. The SQL syntax I am after is append data. How would i run this query in ASP though?

[ozzii]

If I understand you correctly, here is some code which will hopefully point you in the right direction. Let me know if this is what you were looking for:

'The following code snippet will Import a Table from an
'External Database and append its Records to a Table in the current Database
Dim MYtdf As TableDef
 
For Each MYtdf In CurrentDb.TableDefs
  'DELETE Imported Table if it exists
  If MYtdf.Name = "tblEmployeesAppend" Then
    CurrentDb.TableDefs.Delete MYtdf.Name
  End If
Next
 
'Import tblEmployees from C:\Test2\Test2.mdb and rename it tblEmployeesAppend
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Test2\Test2.mdb", acTable, "tblEmployees", "tblEmployeesAppend"
 
DoCmd.SetWarnings False     'Turn System Messages OFF
  'Run Append Query adding all Records from tblEmployeesAppend to tblEmployees
  DoCmd.OpenQuery "qryAppendToEmployee"
DoCmd.SetWarnings True      'Turn System Messages ON
 
'No need to keep it around
CurrentDb.TableDefs.Delete "tblEmployeesAppend"

The above code does look similar to what i am trying to do. However I am trying to run this query with ASP. Basically I have a production database (Access) running on a web server, and a duplicate "staging" database which is to be used for table import to the production database. For example, I make changes to the table in my local copy of the database, then FTP it to the web server to become the "staging" database. I now need to be able to import the data from this table from the staging database to the production database using ADO (VBScript). The SQL query needs to be an append action. Is there a simple way to do this, given the source and destination tables will have separate connections?

[NeoPa]

I think the question should have been more like :
What is the correct SQL syntax to append data into a table rather than to create a table?
My existing query

SELECT * INTO Products From exportdb.mdb.exporttable

clears out the table prior to adding the new data :confused:

The syntax needed is INSERT INTO rather than SELECT INTO.

Yes you are right. The SQL syntax I am after is append data. How would I run this query in ASP though?

I'm afraid I know little or nothing about ASP.
I would suggest taking this to the ASP forum for more expert help on ASP, now you have the SQL syntax you need.