Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ghanta
Yak Posting Veteran
96 Posts |
 Posted - 2010-12-02 : 12:15:28
|
| I wanted to know the efficient way to do this:I have a fixed width files with hundreds of columns... I have the data layout for the file. This file has data for more than 20 tables and layout provided tells me what columns goes on which table... Is the good way to do is to create a staging table, create 20 tables and use SSIS to populate those 20 tables from that staging table? I will be loading bunch of these files ... probably create something and run it overnight...If you have a better way please let me know. Thanks! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-02 : 12:24:22
|
| Well don't think you need the staging table - SSIS could split the data over the 20 tables for you.How many rows in the files?As the simplest solution I would load into a staging table then use an SP to insert into the others but depends on how much data.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-12-02 : 12:41:12
|
| Personally, I like staging tables, because if something goes wrong, you already have the data in a table, so you can go back and adjust/delete/re-insert whatever in the final tables. I consider it then a sort of "history" of the original data that can be preserved (rather than the file the data was provided in). Also, I have found that if you have to do this once, you will have to do it again. And once you have the "final" tables created, you are going to have to ensure that any data loaded from a file the next time is cleaned up properly so that it matches your tables. |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2010-12-02 : 12:53:34
|
| Tim and Nigel thanks for the response...makes sense. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 12:57:52
|
| We add columns to Staging tables for Error number and Error message.If in processing we find that ColumnX sometimes does not contain a valid date we can update all rows where ColumnX is not a valid date with ErrorNumber=1, ErrorMessage=COALESCE(ErrorMessage+', ', '') + 'ColumnX date invalid'and then produce a report for people to sort out the data. Over time the number of validations added makes the import process more robust |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2010-12-02 : 13:45:17
|
| Kristen good idea. Thanks! |
|
|
|
|
|
|
Breaking Huge Table in Chunks