December 17, 2007 at 3:09 pm
I have a delimited text file that I need to read in and evaluate a couple of the fields before inserting into a table. I'm looking for the best practice to do this.
December 17, 2007 at 3:52 pm
Is this a one-time operation or a process that will be reused often? How large of a file are you looking at?
December 17, 2007 at 6:32 pm
What do you mean by 'before putting it into a table'? I would suggest setting up a DTS package, inserting the data into staging table, evaluate it there, and if it passes evaluation, move it from the staging table to the real table.
But without knowing what you mean by evaluate the data, it's hard to give you a solution.
-SQLBill
December 18, 2007 at 8:04 am
This process will be used very sporatically. Files are typically around 200 records. Basically, I will read the record in the file, evaluate two columns of the record to determine which table the record should be inserted to.
December 19, 2007 at 7:55 am
I agree with SQLBill.
You will save yourself a lot of headache by loading the data into a staging table first via BCP or a DTS package.
Once the staging table is loaded, you can write a Stored Procedure to check the data columns which will determine the destination table.
You may also be able to write everything in a multi-stage DTS package.
I would encourage you to spend a little time to build a small test case using this article as a reference:
Forum Etiquette: How to post data/code on a forum to get the best help
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
With a test case posted, suggestions can be made that are tailored to your project.
Happy T-SQLing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 19, 2007 at 9:32 am
Would you load the data into a staging table as one long string including the delimiters?
December 19, 2007 at 9:44 am
Disregard the last question. I read more about bcp and figured out how to load a delimited record into the proper fields. Thanks for the guidance.
Bryan
December 19, 2007 at 9:45 am
No. We want to load the data into the staging table, so that we can begin to work with data at the column level.
The lack of a staging table at the moment puts you in a cart before the horse situation. It make take several iterations of tweaking create table script that defines the columns and datatypes for the Staging Table.
I will be working on assumptions, unless you can provide at least sample data, a delimited data file, a guess of the columns/datatypes for a staging table and the columns/datatypes of the destination tables.
If you can post a sample (or an entire 200 line) data file to be imported, I will take a swag at creating the Staging Table as well as how I loaded the data.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 19, 2007 at 9:51 am
Bryan,
Not a problem!
There are several different ways to parse your data into the correct destination table, once it is loaded into the staging table.
Keep us posted on how you progress, or if you have any other questions/concerns/comments/complaints/etc.
We all learn from each other.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 20, 2007 at 1:32 am
Hi ,
I am agree with some persons who suggest that you have to first save the file data in a table after that you have to perform the check then take a action that you have to enter this data in a table or not . So first read tha file and store in the table after that perform check on that table and then store into staging table . And if you sre facing problem in reading file then i will give you the code by that you can read the file data as in the text file ...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply