September 3, 2004 at 6:22 am
OK,
I'm a real newbie to MS SQL Server but I'm trying to make the transition from the MS Access way of doing things to a more SQL Server driven model.
I have a MS Access routine that will use the Dir() command to loop through a directory and grab every file with a specified extension and import them into MS Access using an import specification file (they are all the same format, but different names), the tables that are created are named after the filename.
This proves to be very slow and after discovering the import wizard in Enterprise Manager, I was wondering if there was a way to replicate this process in SQL Server.
My requirements are:
Other than that, it just needs to be able to loop. I found a routine by searching this site that would loop through a directory, but it imports all the records into a pre-defined table. I need some dynamic-ness (is that a word) to the process so that the routine defines the table name
Is this even possible or am I stuck with manually importing the files into SQL Server one-by-one?
September 4, 2004 at 9:36 am
I believe you can create a new table for each file to be loaded. As long as you are running as the dbo, the table should be owned by the dbo for future reference. Also, to limit the dynamism involved, I suggest that you load the data into a staging table, and then insert it into the newly created table.
1. Assuming that you have a simple table like this:
CREATE TABLE publishers (
pub_id char(4) NOT NULL
pub_name varchar(40) NULL,
city varchar(20) NULL,
)
2. I would create a publisher staging table, like this:
CREATE TABLE publisher_staging (
pub_id char(4) NOT NULL
pub_name varchar(40) NULL,
city varchar(20) NULL,
)
3. Then here's the code:
-- ----------------------------------------------
-- A. Define local vars, clear the staging table
-- ----------------------------------------------
DECLARE @table_name varchar(50)
DECLARE @template varchar(1024)
DECLARE @sql varchar(1024)
DELETE * FROM dbo.publisher_staging
-- ----------------------------------------------
-- B. Put your code here to get the table name from the file name,
-- and save it in: @table_name
-- Note: @tablename must be a valid SQL table name -- i.e., no file extension!
-- C. Also put your code here to load the table data into: publisher_staging
-- D. Then: This code will create your target table
-- ----------------------------------------------
SELECT @template = ' CREATE TABLE [[table_name]] (' +
' pub_id char(4) NOT NULL ' +
' pub_name varchar(40) NULL,' +
' city varchar(20) NULL,' +
' )
SELECT @sql = REPLACE(@template,'[[table_name]]',@table_name)
EXECUTE @sql
-- ----------------------------------------------
-- E. Now copy the data from staging into your new table:
-- ----------------------------------------------
SELECT @sql = 'INSERT ' + @table_name + ' SELECT * FROM publisher_staging'
EXECUTE @sql
Good luck.
Bob Monahon
September 7, 2004 at 8:45 am
I'm not sure how you plan on getting the file names converted to inputs to Bob's procedure, but you could combine his method in a DTS job combining some VBS that pulls the file names, parses them, and sets the names you want to use as global variables.
November 8, 2004 at 9:20 am
I am also a newbie and have a similar problem. I have an access program which runs every fifteen minutes and imports data which is then read to update the database. I too use a DIR statement to create the files. I have converted the database but I am at a loss as to how to write the update program. Does anyone have similar program source that I could look at?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply