[dba-SQLServer]SQL Import Spec

Mark,

Once you determined which of the seven layouts, why are you not storing
each data item into its appropriate field during step 2?  The data items
are delimited are they not?  And if not, then how are you performing
step 4?  I hope step 4 is not a manual step! <grin>


Charles Wortz 
Software Development Division 
Texas Education Agency 
1701 N. Congress Ave 
Austin, TX 78701-1494 
512-463-9493 
CWortz at tea.state.tx.us 
-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at McBeeAssociates.com] 
Sent: Friday 2003 Apr 25 14:56
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]SQL Import Spec


Charles -
There are 7 different record layouts in this huge text file.  Step 2
runs 7 queries to separate records based on their record layout type
(using a record layout indicator).
Once the 7 tables are created (records still only 1 field), I need to
break the data into fields.  I only use access because I can use the
import specs, instead of manually selecting the field breaks for each
table.

-----Original Message-----
From: Wortz, Charles [mailto:CWortz at tea.state.tx.us] 
Sent: Friday, April 25, 2003 3:44 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]SQL Import Spec

Mark,

I must be missing something because I don't see the purpose of steps 3
and 4.  What are you doing in those steps that cannot be done during
step 2?
Charles Wortz 
-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at McBeeAssociates.com] 
Sent: Friday 2003 Apr 25 14:39
To: SQLServerList
Subject: [dba-SQLServer]SQL Import Spec
I have been loading gigs and gigs of data into our SQL Server for the
past month.
The process is fairly time consuming:
1.      Import a 16 GB text file into a 1 field SQL table 
2.      Run a sproc to separate records into 7 different tables based on
record layout 
3.      Export each of the 7 tables from SQL to text 
4.      Import each of the 7 text files into linked SQL tables using
Access import specs. 

I need to do this for almost 70 16 GB text files.  Each file takes about
a day to fully load.
Is there a way to expedite this process?  I am really looking for a way
to save an import spec in Enterprise Mgr.
That way, I can omit using Access and run the whole process as a SQL
job, with very minimal human interaction.
Any direction is greatly appreciated.

Thanks,
Mark Boyd
Sr. Systems Analyst
McBee Associates, Inc.