Help assigning records from one table to another

  • CREATE TABLE #MapIDs

    (

    WonkyID CHAR(8) NOT NULL,

    Client VARCHAR(50) NULL,

    Identifier1 VARCHAR(12) NULL,

    Identifier2 VARCHAR(20) NULL,

    CONSTRAINT PK_CenseoID PRIMARY KEY CLUSTERED

    (

    WonkyID ASC

    ))

    INSERT INTO #MapIDs

    (WonkyID)

    VALUES

    ('C1212121'),

    ('C1212122'),

    ('C1212123'),

    ('C1212124'),

    ('C1212125')

    CREATE TABLE #IDSource

    (

    Client VARCHAR(50),

    Identifier1 VARCHAR(12),

    Identifier2 VARCHAR(20)

    )

    INSERT INTO #IDSource

    (Client, Identifier1, Identifier2)

    VALUES

    ('ABC', 'HA1313008', '99NK88771'),

    ('ABC', 'HA1585008', '99NK98745'),

    ('ABC', 'HA1361208', '99NK42138'),

    ('ABC', 'HA1318117', '99NK66744'),

    ('ABC', 'HA9876351', '99NK01578')

    #MapIDs is a table with pre-populated, randomized IDs (millions) that are waiting to be associated with actual records, which in this case exist in #IDSource. What I mean is that I have to take the data from #IDSource and assign it to unique identifiers from #MapIDs, and in so doing, I need to update the 3 NULL columns in #MapIDs with the data from #IDSource. It does not matter which data record from #IDSource gets associated with which WonkyID, as we're creating the association right now.

    So what I would like to end up with is:

    WonkyID ClientIdentifier1Identifier2

    C1212121 ABC HA1313008 99NK88771

    C1212122 ABC HA1585008 99NK98745

    C1212123 ABC HA1361208 99NK42138

    C1212124 ABC HA1318117 99NK66744

    C1212125 ABC HA9876351 99NK01578

    I am looking for the most elegant or efficient way to accomplish this. I haven't figured out how to do it, since there's no common data between the two tables. I'd like to not loop through one at a time, though I could do it that way if I had to.

  • This is the first thing that came to mind...try this:

    IF OBJECT_ID('tempdb..#MapIDs') IS NOT NULL

    DROP TABLE #mapids

    CREATE TABLE #MapIDs

    (

    ID INT IDENTITY(1,1),

    WonkyID CHAR(8) NOT NULL,

    Client VARCHAR(50) NULL,

    Identifier1 VARCHAR(12) NULL,

    Identifier2 VARCHAR(20) NULL,

    CONSTRAINT PK_CenseoID PRIMARY KEY CLUSTERED

    (

    WonkyID ASC

    ))

    INSERT INTO #MapIDs

    (WonkyID)

    VALUES

    ('C1212121'),

    ('C1212122'),

    ('C1212123'),

    ('C1212124'),

    ('C1212125')

    IF OBJECT_ID('tempdb..#IDSource') IS NOT NULL

    DROP TABLE #IDSource

    CREATE TABLE #IDSource

    (

    ID INT IDENTITY(1,1),

    Client VARCHAR(50),

    Identifier1 VARCHAR(12),

    Identifier2 VARCHAR(20)

    )

    INSERT INTO #IDSource

    (Client, Identifier1, Identifier2)

    VALUES

    ('ABC', 'HA1313008', '99NK88771'),

    ('ABC', 'HA1585008', '99NK98745'),

    ('ABC', 'HA1361208', '99NK42138'),

    ('ABC', 'HA1318117', '99NK66744'),

    ('ABC', 'HA9876351', '99NK01578')

    UPDATE m

    SET m.Client = i.client,m.Identifier1 = i.Identifier1,m.Identifier2 = i.Identifier2

    FROM #MapIDS m INNER JOIN #idsource i ON i.id = m.id

    SELECT * FROM #MapIDS

  • You can do it like this using cte's so you don't have to change your underlying data structures.

    CREATE TABLE #MapIDs

    (

    WonkyID CHAR(8) NOT NULL,

    Client VARCHAR(50) NULL,

    Identifier1 VARCHAR(12) NULL,

    Identifier2 VARCHAR(20) NULL,

    CONSTRAINT PK_CenseoID PRIMARY KEY CLUSTERED

    (

    WonkyID ASC

    ))

    INSERT INTO #MapIDs

    (WonkyID)

    VALUES

    ('C1212121'),

    ('C1212122'),

    ('C1212123'),

    ('C1212124'),

    ('C1212125')

    CREATE TABLE #IDSource

    (

    Client VARCHAR(50),

    Identifier1 VARCHAR(12),

    Identifier2 VARCHAR(20)

    )

    INSERT INTO #IDSource

    (Client, Identifier1, Identifier2)

    VALUES

    ('ABC', 'HA1313008', '99NK88771'),

    ('ABC', 'HA1585008', '99NK98745'),

    ('ABC', 'HA1361208', '99NK42138'),

    ('ABC', 'HA1318117', '99NK66744'),

    ('ABC', 'HA9876351', '99NK01578')

    select * from #MapIDs

    ;with MapIDs as

    (

    select *, ROW_NUMBER() over (Order by newid()) as RowNum

    from #MapIDs

    ),

    IDSource as

    (

    select *, ROW_NUMBER() over (Order by newid()) as RowNum

    from #IDSource

    )

    update MapIDs

    set Client = i.Client, Identifier1 = i.Identifier1, Identifier2 = i.Identifier2

    from IDSource i

    join MapIDs m on i.RowNum = m.RowNum

    select * from #MapIDs

    select * from #IDSource

    drop TABLE #MapIDs

    drop TABLE #IDSource

    I have to say that this is a very strange requirement. You have a 1:1 relationship between these tables and from what you posted this equates to another column in the source table instead of this. Of course this may be simplified for the purpose of your problem. Even so the complete random linking is a bit odd. 😉

    Hope this helps.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • That didn't work for me - I got repeating rows:

    WonkyID Client Identifier1 Identifier2

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

    C1212121 ABC HA9876351 99NK01578

    C1212122 ABC HA1318117 99NK66744

    C1212123 ABC HA9876351 99NK01578

    C1212124 ABC HA1318117 99NK66744

    C1212125 ABC HA9876351 99NK01578

    (5 row(s) affected)

    Is there something wrong with my machine?

    This worked though:

    WITH MAP_CTE AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY Client) AS RowId, * -- Just any value - but what would be better...

    FROM #MapIDs

    ),

    IDS_CTE AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY Client) AS RowId, *

    FROM #IDSource

    )

    UPDATE MAP

    SET MAP.Client = IDS.Client,

    MAP.Identifier1 = IDS.Identifier1,

    MAP.Identifier2 = IDS.Identifier2

    FROM MAP_CTE MAP

    INNER JOIN IDS_CTE IDS ON MAP.RowId = IDS.RowID

  • This principle worked perfectly. I switched to CTEs so that I'm not changing table structure...

    ;WITH CTEMap AS

    (

    SELECT *, ROW_NUMBER() OVER(ORDER BY WonkyID) AS RowNum

    FROM #MapIDs

    ),

    CTESource AS

    (

    SELECT *, ROW_NUMBER() OVER(ORDER BY Identifier1, Identifier2) AS RowNum

    FROM #IDSource

    )

    UPDATE M

    SET M.Client = S.Client,

    M.Identifier1 = S.Identifier1,

    M.Identifier2 = S.Identifier2

    FROM CTEMap M

    INNER JOIN CTESource S

    ON M.RowNum = S.RowNum

    Thanks!!

  • Sean Lange (7/31/2012)

    I have to say that this is a very strange requirement. You have a 1:1 relationship between these tables and from what you posted this equates to another column in the source table instead of this. Of course this may be simplified for the purpose of your problem. Even so the complete random linking is a bit odd. 😉

    Hope this helps.

    I replied (or at least started working up my solution, verified, and replied) to SQL Padawan before any other replies were here. I didn't test your CTEs, but yeah, that's the direction I went. Much appreciated!

    So, this is basically a healthcare data thing where we will have multiple clients' data together, and we need a completely arbitrary assignment of internally generated IDs. This ID assignment will be propagated back to the client databases (which are the Source tables), but we're using a local GUID-like system with a specific format. C1212121 represents only one record, for only one client, and we're keeping a master list (separate from all other databases) of which IDs map to where. So as new records come in, we have to get IDs assigned as early in the process as possible.

  • Exactly what I went with. Thanks!

    And thanks, everybody. When I read the first reply that used derived row numbers to join together, I had to look into a mirror so I could facepalm myself. Duh! 🙂

  • Glad that we could help. We all have those facepalm moments. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 7 (of 7 total)

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