Importing data from 2 Access tables into 1 sql table
-
I recently made an online application from using access to store the values, due to limitations in Access the data had to split between two tables, with an userid number linking the two sets.
I recently upgraded to SQL Server because of the limitations in access, but now have to import the existing data into SQL server. I can import the two tables into one SQL table but I can't get it to put the two sets of data back into one. There's too many records to copy and paste!!
Is there a way of automating the process?
-
Colin,
You can go through Enterprise Manager and right click on the database you want to import into and select All Tasks -> Import Data. This will walk you through all of the steps to import the data. The wizard will allow you to specify a query where you can join both of your tables into one.
At the end of the wizard it will allow you to save it as a DTS package. This will allow you to automate the process.
Hope this helps,
Richard
-
also you can make a union query between these 2 tables in Access ... and make an insert query based on that Union Query ...
and insert the Data into a new table you will make in SQL Server .. and of course you link this table into access to work fine.
I hope this help u.
Alamir Mohamed
Alamir_mohamed@yahoo.com -
I'm a complete newbie to SQL
!, What are the steps for this??Cheers
-
to link tables in Access ..
1- make a system DSN from Control Panel >> Administrative Tools >> Data Sources (ODBC) .. and make this System DSN refer to your Database on SQL Server machine .. and name it MyDSN
2- open access , then file > get external data >> link tables
choose in (File of type) >> ODBC Databases()
and choose myDSn
and choose the 3 tables (2 orignal and 1 new) in SQL Server
3- then make the steps I menthioned in my last post.
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply