Unanswered: Make a horizontal table into a vertical table
Any assistance would be greatly appreciated.
I have a current table which I create on a regular basis from a text file with a layout similar to this:
Type Policy # Amount Rider 1 Amt Rider 2 Amt
B 1112H 24.34 12 12.34
This text file is brought into a staging table with each field (even the amount field) as a varchar (12). I then assign types in a later step in my DTS package.
What I need to do is stack the riders under each policy so for each policy where there is a rider, there is a new row for every rider.
So in the example I've given, there would be 2 additional rows for the original first row since there are two riders.
Type Policy # Amount
B 1112H 24.34
R1 1112H 12
R2 1112H 12.34
I plan on doing this by first creating a table with just the Type, Policy #, and Amt fields, and then using a series of insert queries where I take the rider (if there is one) and append it onto the table.
However, I'm getting the following error message when I try:
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Basically, it wouldn't let me put an 'R1' in the Type column.
How can I get this to work!?!?
If the number of rider columns is constant, you can use a Union query like this:
Insert into DESTINATIONTABLE (Type, [Policy #], Amount)
Select [Type], [Policy #], [Amount Rider 1] as Amount from SOURCETABLE where [Amount Rider 1] is not null
Select [Type], [Policy #], [Amount Rider 2] as Amount from SOURCETABLE where [Amount Rider 2] is not null
If it's not practically useful, then it's practically useless.
Wow blindman, you are a genius, and I'm a big retarded idiot. Yup, stoopid mistake. Importing literally a hundred columns, and one of them was missing a comma. Had to search through the bowels of my dts package, but finally found it. Thanks very much for your help.
Oh, and I didn't need to use the UNION in mine. Just INSERT INTO worked. Once I located that rogue comma, of course.
Last edited by melatic; 11-22-04 at 15:37.
Reason: ETA stuff about union