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 |
|
mgreen84
Yak Posting Veteran
94 Posts |
 Posted - 2014-11-25 : 10:12:51
|
I have an excel file that I need to load into two separate tables. So I have an ssis package dumping all the data from the excel file into an dump table. and from there I want to parse through the data in that table to create two separate tables. here is what my data on the table looks like: http://1drv.ms/11U70mG "/>http://1drv.ms/11U70mGCan anyone guide me or provide some tsql to getting two serparte tables created from this data?Thanks |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-25 : 10:15:59
|
| Instead of a link to an image (my company blocks that site so I can't see it), please post a few rows of the input data and what you want the destination tables to look like. |
 |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2014-11-25 : 12:06:35
|
| the excel spread sheet is dumped into a table and appears in the table as:My Dump TableHolder1 Holder2 Holder3 Holder4 Holder5 Holder6 Holder7 Holder8 Holder9HCO CCN Measure Pos Denom Positive Median Neg Negative50515 110225 Meatest 126 126 112.5 126 EPISODE RESULTS Measure PID EOCID Mdesc stat ED 900207050 1003327510 D 175 ED 900835282 1003328696 D 88 ED 900835242 1003339150 D 110 ED 903628297 1003342159 D 93 I need to pull from this table the first two rows, the 1st row being the column header for the new table and the 2nd row being the record inserted into the new table. The 1st row with the column headers will always be the same.The next table I need, needs to be created out of the records starting below 'EPISODE RESULTS'. The first record following 'EPISODE RESULTS' will be the Headers for the columns on the 2nd new table(will always be the same). The records that follow will need to be inserted in the 2nd new table as records. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-25 : 12:16:13
|
| Hmmm...well it is possible to build dynamic sql to create the tables, but since the column headers are always the same, this is not needed. Instead, create the destination tables ahead of time. Also, add an identity column to your dump (usually called staging) table. Then you can split the table vertically by getting the id of the row containing the string 'EPISODE RESULTS'. Rows with ids less than that (but not the first row!) go into the first table. Rows with ids greater than that (+2) go into the second table. |
|
|
|
|
|
|
How to create new table based off data in table