Need T-SQL query to import several Access tables to one SQL Server table

1

I'm migrating to SQL Server from Access. I created a single table in SQL Server where I want to put all the data that I had to put into separate tables in Access. I tried using the Wizard but it wouldn't allow me to put data from more than one table into the same SQL Server table, unless I did it manually about 50 times. I need a T-SQL query that will import all the Access tables to my single SQL Server table by looping. I know VBA pretty well but I don't know T-SQL. Is this possible? Thanks for your help.

2

Import them from Access using the Wizard into separate tables, then create the combined table in SQL Server. If you give us the details of the single table, we can help you create it, and you very likely won't have to do an explicit loop at all.

1 Like
3

The single table has about 25 fields. The individual tables are actually state tables: In other words, they contain customer data from each of the 50 states. Access couldn't handle all of the data combined into one table.

After I import all 50 tables, how do I combine them into one table?

4

Another way is to use OPENROWSET but might be overkill, Import Wizard might be the way to go for a one time import

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
             'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
             'admin';'',Customers)
1 Like
5

Here's the general idea. You didn't provide any details at all so I can't be more specific.

SELECT TOP (0) *
INTO dbo.combined_table
FROM dbo.any_state_table

CREATE CLUSTERED INDEX combined_table__CL ON dbo.combined_table ( state, customer_id, sales_date ) WITH ( FILLFACTOR = 95 );

--yeah, would be easier to do this part with a loop
INSERT INTO dbo.combined_table
SELECT *
FROM dbo.state_1_table
UNION ALL
SELECT *
FROM dbo.state_2_table
--...remaining tables...
1 Like
6

That's another thing. This is a file that I download each month. It begins as a 6Gb CSV file that I break up into smaller files, convert to xlsx then delete and add some fields. The bottom line is that I will have to clean out the single SQL Server table each month and then re-load it with the new data.

7

^^ that statement is misleading, if you are migrating to SQL Server why are you downloading things each month? Could you further elaborate why you are pushing access data to SQL Server? Reporting?

1 Like
8

The users need to query the data. Originally, they only needed to query one state at a time but now they want to be able to select multiple states. I wrote the VBA code that creates a UNION query in Access but it takes way too long to process. I figure that with SQL Server I could use code to compose a query on the fly in Access, depending on user selections, and then use ADO to retrieve the data from SQL Server to an Excel spreadsheet.

9

So you want the data in sql server but query it from access? what version of SQL server do you have?

1 Like
10

I'm using Azure. I have SQL Server 2016 Management Studio.

11

The general idea for this type of process is:

  1. Import the data into a staging table in SQL Server
  2. Process the data from the staging table to the final table...in this step you would calculate the value for your additional columns and ignore the columns you don't want.
  3. Using an explicit transaction - truncate the target/final table and load the new data. If this process fails it would be rolled back and the previous data still available. This may be eligible for minimal logging...

Since the file is so large - you definitely will benefit by implementing row or page compression on both the staging and final tables. You also don't need to convert to xlsx since it is easier to load a CSV file than an Excel file.

This can also be automated - even to the point of downloading the file (depending on how you get the file now) using SSIS (or a combination of other tools like Powershell, SQLCMD, BCP, etc...).

You could also drop unused columns from the import process - that way you are only loading the relevant columns from the file into your staging table, and if you can calculate the values during the load process (in SSIS this would be a derived column transformation) you could avoid having to perform those calculations when loading to the final table.

1 Like
12

Thanks for your answer. Do you know how I could find the T-SQL code for downloading the CSV file, unzipping it and loading the new data?

13

I found some code that uses a cursor when looping through the tables. This is new for me but I just need to create a query using the code and substitute the correct name for the database.

14

do you need all 6gb of the files?

how often do you want the data copied over to AWS?

t

15

What is "AWS"? Yes, I need all of the 6Gb of the file to begin with.

16

:laughing: sorry I thought you had AWS. Do you want live data for the reports? how often are you running the access to sql extract?

1 Like
17

Every month. The data isn't live.

18

ok here is what I recommend.
every month copy the whole access database over to the sql server
either using openrowset or SSIS pckages ingest the data into SQL server
then from access query this data

1 Like
19

Yes, that's what I'd like to do. That's why I'd like to find a T-SQL query that will automatically import the Access tables.

20

create a folder on SQL Server : C:\MonthlyDataCopy
for now manually copy the access file to that folder
enter the details of the your access db you copied over in the following SQL code

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
             'C:\MonthlyDataCopy\Euler.mdb';
             'admin';'',Customers)