By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

Copying data from one database to another

P: 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
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,616
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
Expert Mod 15k+
P: 31,314
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

P: 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

P: 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
Expert Mod 15k+
P: 31,314
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.