469,352 Members | 1,661 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Copying data from one database to another

37
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?
Jan 20 '07 #1
5 15966
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. 'The following code snippet will Import a Table from an
  2. 'External Database and append its Records to a Table in the current Database
  3. Dim MYtdf As TableDef
  4.  
  5. For Each MYtdf In CurrentDb.TableDefs
  6.   'DELETE Imported Table if it exists
  7.   If MYtdf.Name = "tblEmployeesAppend" Then
  8.     CurrentDb.TableDefs.Delete MYtdf.Name
  9.   End If
  10. Next
  11.  
  12. 'Import tblEmployees from C:\Test2\Test2.mdb and rename it tblEmployeesAppend
  13. DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Test2\Test2.mdb", acTable, "tblEmployees", "tblEmployeesAppend"
  14.  
  15. DoCmd.SetWarnings False     'Turn System Messages OFF
  16.   'Run Append Query adding all Records from tblEmployeesAppend to tblEmployees
  17.   DoCmd.OpenQuery "qryAppendToEmployee"
  18. DoCmd.SetWarnings True      'Turn System Messages ON
  19.  
  20. 'No need to keep it around
  21. CurrentDb.TableDefs.Delete "tblEmployeesAppend"
Jan 20 '07 #2
NeoPa
32,184 Expert Mod 16PB
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
Expand|Select|Wrap|Line Numbers
  1. 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 (' ').
Jan 22 '07 #3
ozzii
37
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
Expand|Select|Wrap|Line Numbers
  1. 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?
Jan 22 '07 #4
ozzii
37
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:
Expand|Select|Wrap|Line Numbers
  1. 'The following code snippet will Import a Table from an
  2. 'External Database and append its Records to a Table in the current Database
  3. Dim MYtdf As TableDef
  4.  
  5. For Each MYtdf In CurrentDb.TableDefs
  6.   'DELETE Imported Table if it exists
  7.   If MYtdf.Name = "tblEmployeesAppend" Then
  8.     CurrentDb.TableDefs.Delete MYtdf.Name
  9.   End If
  10. Next
  11.  
  12. 'Import tblEmployees from C:\Test2\Test2.mdb and rename it tblEmployeesAppend
  13. DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Test2\Test2.mdb", acTable, "tblEmployees", "tblEmployeesAppend"
  14.  
  15. DoCmd.SetWarnings False     'Turn System Messages OFF
  16.   'Run Append Query adding all Records from tblEmployeesAppend to tblEmployees
  17.   DoCmd.OpenQuery "qryAppendToEmployee"
  18. DoCmd.SetWarnings True      'Turn System Messages ON
  19.  
  20. 'No need to keep it around
  21. 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?
Jan 22 '07 #5
NeoPa
32,184 Expert Mod 16PB
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 23 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jani Tamminen | last post: by
reply views Thread by berwiki | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.