 |
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

January 29th, 2008, 11:53 PM
|
Authorized User
|
|
Join Date: Jun 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Import Access data whith varying columns
Hi ,
I am trying to import n number of access database.
A1.mdb
A2.mdb
A3.mdb
.
.
.
An.mdb
Each of the above .MDB file has a table name called tblTEST
The table name same across all the mdb files.
But the COLUMNS ARE NOT THE SAME IN EACH TABLE tblTEST (but different .mdb file).
What i want is below,
1. Put all the access file in a folder.
2. copy one mdb file :- its table tblTEST,the STRUCTURE and DATA into sql server at a time.
3. Loop thro all the mdb files in the folder.
I am not sure how to automate this task. To copy the whole structure and data from access to Sql server.
That means i need to create a table with the exact structure as in access in SQL SERVER and then import the data.
Any help is highly appreciated.
Thanks Heaps. This is really important and urgent.
|

January 30th, 2008, 12:06 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
try macro, there are many options in macro such as open database, run query etc. you can also edit it like vb code.
urt
|

January 30th, 2008, 12:09 AM
|
Authorized User
|
|
Join Date: Jun 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
But i need to write a DTS or a Stored procedure to import the data from the MDB files into SQL SERVER.
|

January 30th, 2008, 12:12 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
First you take every table from various mdb files in one mdb file, and then from sql server import all tables from one access file
urt
|

January 30th, 2008, 12:19 AM
|
Authorized User
|
|
Join Date: Jun 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi urt,
Thanks for the suggestion, but...
The Table names are the same in all the mdb files. And more over how do i programatically copy all the tables into sql server.
As the table names are same across all the mdb files but the columns will vary in each of the mdb file.
So copying the tables into the same mdb file will be needed to be done programatically and thats a waste of time.
|

January 30th, 2008, 12:38 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
How many mdb files are there and how many tables to import are there 1 mdb file
urt
|

January 30th, 2008, 12:46 AM
|
Authorized User
|
|
Join Date: Jun 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
There are N mdb files, but the same table name in all the mdb files. And i want to import this particulat table in all the mdb files.
|

January 30th, 2008, 12:47 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Your create one access file in the same folder where all a1,a2.... files are placed, say final.mdb
create new module and paste following code
Sub ImportTables()
For i = 1 To 10
DoCmd.RunSQL ("SELECT * into tblTEST" & Trim(Str(i)) & " from A" & Trim(Str(i)) & ".mdb.area")
Next
End Sub
replace 10 with no of mdb files in your folder
run this module all tables will be imported directly to your one final database, now you can import this one file in sql server.
urt
|

January 30th, 2008, 12:56 AM
|
Authorized User
|
|
Join Date: Jun 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
hmmm......... yeah now i get what u mean. Except that, as i said this is a matter of time as well, as the data in each table is a few hundred thousand.
But will try this solution. Thanks for the suggestion.
If anything better please let me know. As i will have to look into the time as well.
|

January 30th, 2008, 01:00 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Sub ImportTables()
For i = 1 To N
DoCmd.RunSQL ("SELECT * into tblTEST" & Trim(Str(i)) & " from A" & Trim(Str(i)) & ".mdb.tblTEST")
Next
End Sub
urt
|
|
 |