Post reply

import xls , To replace existing data

  • January 16, 2009 at 12:47 pm

    #198855

    Really need you help on this,

    I have been given a xls file with updated records ,

    and need to import in to sql 2005 ,

    The problem i have is how do i replace existing data ,

    Say i have a table with index col1 , col2 , (Same is sql)

    How do I just replace the existing data in col2, I have 3000 records and don’t want to input the data manually, the index in sql and xls match, Please can someone help,

  • January 16, 2009 at 4:33 pm

    #928586

    Once you load your excel data to a table you can join it to your "real" table and do an update.

    Sounds like this is a one time thing so it might be easiest to use the import/export wizard to load the excel file into a new table, do this update, then drop the excel staging table.

    -- build and load example tables

    CREATE TABLE #YourExistingData(

    Col_1 INT NOT NULL

    ,Col_2 VARCHAR(10) NOT NULL

    ,CONSTRAINT [pk_YourExistingData] PRIMARY KEY CLUSTERED (Col_1)

    )

    CREATE TABLE #UpdatedDataFromExcel(

    Col_1 INT NOT NULL

    ,Col_2 VARCHAR(10) NOT NULL

    ,CONSTRAINT [pk_UpdatedDataFromExcel] PRIMARY KEY CLUSTERED (Col_1)

    )

    INSERT INTO #YourExistingData (

    Col_1,

    Col_2

    )

    SELECT '1','Monday' UNION ALL

    SELECT '2','Monday' UNION ALL

    SELECT '3','Monday'

    INSERT INTO #UpdatedDataFromExcel(

    Col_1,

    Col_2

    )

    SELECT '1','Saturday' UNION ALL

    SELECT '2','Sunday' -- note, nothing for col_1 value of 3, it won't get updated

    -- update your table with data from excel

    UPDATE yd

    SET yd.Col_2 = xls.Col_2

    FROM #YourExistingData yd

    INNER JOIN #UpdatedDataFromExcel xls

    ON yd.Col_1 = xls.Col_1

    -- take a look

    SELECT Col_1, Col_2 FROM #YourExistingData

    -- clean up

    DROP TABLE #YourExistingData

    DROP TABLE #UpdatedDataFromExcel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply