insert UNIQUE identifier to MULTIPLE duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert UNIQUE identifier to MULTIPLE duplicates

Author

Topic

sgtglok
Starting Member

2 Posts

Posted - 2004-11-05 : 16:56:47

Hi all,

Here is my situation and problem. I have a historic 'flat' table I am converting from Paradox. It has a unique identifier CASE_ID for each record. Each record also has REPORTER_ID, REPORTER_NAME and REPORTER_ADDRESS columns. I need a REPORTERS table with unique records. The problem is REPORTER_ID is not unique or sometimes is NULL.

A) Global table

CASE_ID REPORTER_ID REPORTER_NAME REPORTER_ADDRESS
1001 NULL A 123 Nowhere Street
1002 NULL A 123 Nowhere Street
1003 NULL B 345 Somewhere Avenue
1004 NULL B 345 Somewhere Avenue
1005 NULL B 345 Somewhere Avenue
1006 1234 C 999 Another Place
1007 1234 C 999 Another Place

B) REPORTERS table

REPORTER_ID REPORTER_NAME REPORTER_ADDRESS
1234 C 999 Another Place
TEMP1 A 123 Nowhere Street
TEMP2 B 345 Somewhere Avenue

The way I figure, I need to first extract those records with REPORTER_ID=NULL into a temp table and then assign EACH GROUP of records with same REPORTER_NAME and REPORTER_ADDRESS a UNIQUE REPORTER_ID. I need some help with query construct. The table is pretty large, so there could be 2000+ occurances of the same REPORTER_NAME and REPORTER_ADDRESS that I need to assign the same REPORTER_ID

Any help would be appreciated. Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-05 : 17:04:17

Create Table #tempTable
(
Reporter_Id int identity(seedVal,1),
Reporter_name varchar(100),
Reporter_Address varchar(100)
)

Insert Into #tempTable
Select Distinct Reporter_Id, Reporter_Name, Reporter_Address
From <global table>
Where Reporter_Id

Insert Into Reporters
Select * From #tempTable

Update <Global Table>
Set Reporter_Id = #tempTable.Reporter_Id
From <Global Table>
Inner Join #tempTable
On <Global Table>.Reporter_Name = #tempTable.Reporter_Name
and <Global Table>.Reporter_Address = #tempTable.Reporter_Address

Corey