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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to create new table based off data in table

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/11U70mG

Can 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.
Go to Top of Page

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 Table

Holder1 Holder2 Holder3 Holder4 Holder5 Holder6 Holder7 Holder8 Holder9
HCO CCN Measure Pos Denom Positive Median Neg Negative

50515 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.


Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -