June 10, 2011 at 1:59 pm
I have a process that weekly imports upto 110 TAB delimited text files from a folder into a table in my database. I have just recently converted an app from non-sql table to SQL. Currently the import process is still within my Win32 application but is terribly slow. Is this something I can write a SP to do? I have a table that holds the possible text files and so I need to look at each record in this table -> look to see if the file exists in a given folder -> import the data into my SQL table.
Here are the tables:
Table holding names of text files to look for an import. The FILENAME field holds the text file name
CREATE TABLE [dbo].[Measures](
[FILENAME] [char](20) NULL,
[MEASURE] [smallint] NULL,
[NAME] [char](255) NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Measures] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table to import text file data into. Fields from text file and this IDXRaw table match up. This table is truncated prior to this import process so no need to look for duplicates or anything. I just need to import ALL records from ALL text files into it.
CREATE TABLE [dbo].[IDXRaw](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DIVISION] [char](7) NULL,
[INVOICE] [int] NULL,
[CPT] [char](5) NULL,
[MEASURE] [char](9) NULL,
CONSTRAINT [PK_IDXRAW] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Thanks for any help you might provide or for pointing me in the direction I need to look. I have a feeling that doing this from inside SQL it will be much faster. Problem is, client can add/remove records from MEASURES and so I do not know up front which text files to look for.
Lee
June 10, 2011 at 2:05 pm
That's the kind of thing SSIS does really well at.
It can query a folder for a list of files, or operate off of a query to a database table, and step through the files and do imports, data cleaning, error handling, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2011 at 1:25 pm
If SSIS is not a goer then bulk insert might do the job.
http://msdn.microsoft.com/en-us/library/ms188365.aspx
Hope this helps,
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply