3

I'm migrating data from one system to another and will be receiving a CSV file with the data to import. The file could contain up to a million records to import. I need to get each line in the file, validate it and put the data into the relevant tables. For example, the CSV would be like:

Mr,Bob,Smith,1 high street,London,ec1,012345789,work(this needs to be looked up in another table to get the ID)

There's a lot more data than this example in the real files.

So, the SQL would be something like this:

Declare @UserID
Insert into User
Values ('Mr', 'Bob', 'Smith', 0123456789)
Set @UserID = @@Identity
Insert into Address
Values ('1 high street', 'London', 'ec1', select ID from AddressType where AddressTypeName = 'work')

I was thinking of iterating over each row and call an SP with the parameters from the file which will contain the SQL above. Would this be the best way of tackling this? It's not time critical as this will just be run once when updating a site.

I'm using C# and SQL Server 2008 R2.

Improve this question
12
  • 2
    For importing CSV into MS SQL, why don't you just use the import wizard (see HOWTO here). Commented Jan 9, 2013 at 21:46
  • 1
    I would follow TomTom's advice but use BULK INSERT which you can automate vs. the import wizard which will be a challenge to do so... Commented Jan 9, 2013 at 21:52
  • Note that oftentimes when you can't import the file directly it can be easier to have a batch job that just transforms the file into another file that can be uploaded directly to the DB through whatever built in importing mechanisms it supports. Commented Jan 9, 2013 at 21:53
  • @AaronBertrand The whole point of the wizard is that you don't have to automate it for a one time upload. Commented Jan 9, 2013 at 21:53
  • 1
    And still, a BULK INSERT command could be just as quick to write as stepping through all the steps in that heinous wizard... Commented Jan 9, 2013 at 21:56

2 Answers 2

Reset to default
4

What about you load it into a temporary table (note that this may be logically temporary - not necessarily technically) as staging, then process it from there. This is standard ETL behavior (and a million is tiny for ETL), you first stage the data, then clean it, then put it to the final place.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

That sounds good, I'll look into doing this. So basically I create a temp table, bulk load the data into it, I can then validate the data in my program and use SP's to move the data to where it needs to go to...
If I do bulk insert into a temp table, is there anyway to process the data without using a cursor? So in my example above, I would bulk insert the CSV data into the temp table, then I would need to process each row in the temp table to first insert the user and get the ID and use that to insert the rest of the data into the relevant table...
Sure. 99% of all SQL can happen without a cursor - it is only people that never learn SQL properly that tend to use cursors for thing that are wonderfull example of set oriented logic.
My SQL is only average, I can't see how you do it? If I have a temp table with the data above, how would you insert the user first, get the identity value, then use that to insert the rest of the data, for all of the rows in the temp table without using cursors? Any pointers on what to use?
2 statements. First merge into target table, then merge into second. Look up the merge statement.
|
0

When performing tasks of this nature, you do not think in terms of rotating through each record individually as that will be a huge performence problem. In this case you bulk insert the records to a staging table or use the wizard to import to a staging table (look out for teh deafult 50 characters espcially in the address field).Then you write set-based code to do any clean up you need (removing bad telephone numbers or zip code or email addresses or states or records missing data in fields that are required in your database or transforing data using lookup tables (suppose you have table with certain required values, those are likely not the same values that you wil find in this file, you need to convert them. We use doctor specialties a lot. So our system might store them as GP but the file might give us a value of General Practioner. You need to look at all teh non-matching values for the field and then determine if you can map them to existing values, if you need to throw the record out or if you need to add more values to your lookup table. Once you have gotten rid of records you don't want and cleaned up those you can in your staging table then you import to the prod tables. Inserts should be written using the SELECT version of INSERT not with the VALUES clause when you are writing more than one or two records.

Improve this answer

1 Comment

Actually it does work. IN my last ETL project we handled half a million "master" rows with up to 120.000 details EACH by going through the masters one by one then loading the details. 20 minute processing. But i have to agree - our hardware needed for that was quite impressive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.