Copy single row to multiple rows in another table

1

I have a csv file that I am importing to a staging table in SQL Server 2014. In the staging table there are 10 columns for ticket numbers. The first column will always have a number but all the others could be null. If there is a value in any of the other columns I need to create a new row when inserting it to another table.

so my staging table looks like this:

ID    tkt1         tkt2         tkt3         tkt4      ... tkt10
1     123456789    NULL         NULL         NULL
2     987654321    102345678    NULL         NULL
3     555555555    444444444    333333333    NULL

Destination Table

ID tkt
1  123456789    
2  987654321    
2  102345678    
3  555555555    
3  444444444    
3  333333333

I am trying to put this in a sp that imports the file to the staging table then inserts it to the destination.

thx,
Scott

2 

INSERT INTO Destination_Table ( ID, tkt )
SELECT ca.*
FROM Staging_Table ST
CROSS APPLY (
    SELECT ST.ID, tickets.ticket
    FROM ( VALUES(tkt1), (tkt2), (tkt3), (tkt4), /*...*/ 
        (tkt10) ) AS tickets(ticket)
    WHERE ticket IS NOT NULL
) AS ca
3 
Declare @sampleData Table (ID int, tkt1 int, tkt2 int, tkt3 int, tkt4 int, tkt5 int
                                 , tkt6 int, tkt7 int, tkt8 int, tkt9 int, tkt10 int);

 Insert Into @sampleData (ID, tkt1, tkt2, tkt3, tkt4, tkt5, tkt6, tkt7, tkt8, tkt9, tkt10)
 Values (1, 123456789, Null, Null, Null, Null, Null, Null, Null, Null, Null)
      , (2, 987654321, 102345678, Null, Null, Null, Null, Null, Null, Null, Null)
      , (3, 555555555, 444444444, 333333333, Null, Null, Null, Null, Null, Null, Null);
 
 Select sd.ID
      , tk.tkt
   From @sampleData     sd
  Cross Apply (Values (sd.tkt1), (sd.tkt2)
                    , (sd.tkt3), (sd.tkt4)
                    , (sd.tkt5), (sd.tkt6)
                    , (sd.tkt7), (sd.tkt8)
                    , (sd.tkt9), (sd.tkt10)
              )         tk(tkt)
  Where tk.tkt Is Not Null;

And with a slight modification - you can include the location of each ticket number:

 Select sd.ID
      , tk.tkt_id
      , tk.tkt
   From @sampleData     sd
  Cross Apply (Values (1, sd.tkt1), (2, sd.tkt2)
                    , (3, sd.tkt3), (4, sd.tkt4)
                    , (5, sd.tkt5), (6, sd.tkt6)
                    , (7, sd.tkt7), (8, sd.tkt8)
                    , (9, sd.tkt9), (10, sd.tkt10)
              )         tk(tkt_id, tkt)
  Where tk.tkt Is Not Null;
4

Thank you both.

5

Just so you know, what Scott and Jeff have done is referred to as an "UnPivot". There is also an operator in T-SQL named UNPIVOT but it's not as effective.