July 31, 2012 at 8:23 am
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 Client Identifier1 Identifier2
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.
July 31, 2012 at 8:57 am
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
July 31, 2012 at 9:22 am
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/
July 31, 2012 at 9:35 am
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
July 31, 2012 at 9:41 am
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!!
July 31, 2012 at 9:46 am
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.
July 31, 2012 at 9:48 am
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! 🙂
July 31, 2012 at 9:50 am
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