Home Forums SQL Server 2008 SQL Server 2008 - General Import many column CSV into 2-column sql table RE: Import many column CSV into 2-column sql table

  • December 18, 2013 at 11:54 am

    #1674558

    elliottcoyne (12/17/2013)

    Hi Jeff - thanks for the offer of help. I've followed your guide (as far as I was able to) and created the following to mimic my data source:

    --truncate table tstStreamlineCode

    --===== Create the test table with

    CREATE TABLE tstStreamlineCodes

    (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    NHScode varchar(7),

    sl1 varchar(5),

    sl2 varchar(5),

    sl3 varchar(5),

    sl4 varchar(5),

    sl5 varchar(5),

    sl6 varchar(5),

    sl7 varchar(5),

    sl8 varchar(5),

    sl9 varchar(5)

    )

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT tstStreamlineCodes ON

    --===== Insert the test data into the test table

    INSERT INTO tstStreamlineCodes

    (ID, NHScode, sl1 ,sl2 ,sl3 ,sl4 ,sl5 ,sl6 ,sl7 ,sl8 ,sl9)

    SELECT '1','10002K','4068','4065','4086','4069','4096','4120','4121','4097','4353' UNION ALL

    SELECT '2','10006P','4068','4065','4086','4069','4096','4120','4121','4097','' UNION ALL

    SELECT '3','1003T', '3632','','','','','','','','' UNION ALL

    SELECT '4','1007B','3633','','','','','','','','' UNION ALL

    SELECT '5','1024X','1589','2044','','','','','','','' UNION ALL

    SELECT '6','1037N','1589','2044','','','','','','',''

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT tstStreamlineCodes OFF

    The following should do it (assuming you don't want to include the ID column which could easily be added in):

    SELECT ca.NHSCode,ca.SLCode

    FROM dbo.tstStreamLineCodes

    CROSS APPLY

    (

    SELECT NHSCode,sl1 UNION ALL

    SELECT NHSCode,sl2 UNION ALL

    SELECT NHSCode,sl3 UNION ALL

    SELECT NHSCode,sl4 UNION ALL

    SELECT NHSCode,sl5 UNION ALL

    SELECT NHSCode,sl6 UNION ALL

    SELECT NHSCode,sl7 UNION ALL

    SELECT NHSCode,sl8 UNION ALL

    SELECT NHSCode,sl9

    )ca(NHSCode,SLCode)

    WHERE ca.SLCode > ''

    ;

    Results:

    NHSCode SLCode

    ------- ------

    10002K 4068

    10002K 4065

    10002K 4086

    10002K 4069

    10002K 4096

    10002K 4120

    10002K 4121

    10002K 4097

    10002K 4353

    10006P 4068

    10006P 4065

    10006P 4086

    10006P 4069

    10006P 4096

    10006P 4120

    10006P 4121

    10006P 4097

    1003T 3632

    1007B 3633

    1024X 1589

    1024X 2044

    1037N 1589

    1037N 2044

    (23 row(s) affected)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)