Wortz, Charles
CWortz at tea.state.tx.us
Fri Apr 25 15:31:08 CDT 2003
Mark, Now you have me confused. No delimiters between data items? Then your records must be fixed length and your import specs must be doing column counts which you could code into your step 2. If your records are variable length, then there must be data delimiters which you also can code into your step 2. Of course, as another has already mentioned, this can be done by DTS. IIRC, BOL has a good tutorial on DTS. 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 15:18 To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]SQL Import Spec Charles - I think I'm a little confused. How can I distinguish where the field breaks are without some type of specification? There is no delimiter between data items. Mark -----Original Message----- From: Wortz, Charles [mailto:CWortz at tea.state.tx.us] Sent: Friday, April 25, 2003 4:10 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [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 ----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. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com