Mapping IDs - SQL Server Forums

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

Transact-SQL (2000)

Mapping IDs

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-23 : 10:34:16

I'm importing data from one database to another. One of the source tables has primary key, which is not being moved over to the destination table. The destination table has instead identity column as the key. I'm loading records into staging table, where I do have the original ID. When I'm done resolving everything in the staging table, I load it (sans ID column) into the destination - and SQL assigns new IDs for it.

It seems to me however, that I might need to be able to map records in the destination table back to the source records.

So I thought I'd create mapping table, holding source ID and target ID. If I were to do that, how would I populate it? Would I have to assign IDs for the destination records manually when inserting (and how do I write such thing)?

Or is there another way of mapping records?

In other words, I have the following tables:


declare @stg table (id char(10), nme varchar(3))
insert	@stg
select	'ko00000001', 'wke' union all
select	'ko00000002', 'agt' union all
select	'ww00000007', 'kel' union all
select	'ww00000011', 'pal'

declare @dest table (id int identity, nme varchar(3))
insert	@dest
select nme from @stg

declare @map table (src_id char(10), dest_id int, tbl varchar(5))

How do I arrive at something like:

src_id       dest_id  tbl
-------------------------------------------
'ko00000001' 1        'xyz'
'ko00000002' 2        'xyz'
'ww00000007' 3        'xyz'
'ww00000011' 4        'xyz'

in the mapping table (xyz indicating source table, in case I have to do it for more than one).

Any ideas appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 10:41:37

[code]
declare @stg table (id char(10), nme varchar(3))
insert @stg
select 'ko00000001', 'wke' union all
select 'ko00000002', 'agt' union all
select 'ww00000007', 'kel' union all
select 'ww00000011', 'pal'

declare @dest table (id int identity, nme varchar(3))
insert @dest
select nme from @stg

declare @map table (src_id char(10), dest_id int, tbl varchar(5))

insert into @map(src_id, dest_id, tbl)
select s.[id], d.[id], 'xyz'
from @stg s inner join @dest d
on s.nme = d.nme

select * from @map
[/code]

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-23 : 11:00:18

Thank you for the answer.

This is pretty much what I'd like to avoid though, because although there are many columns in both staging and destination tables (making unique match for each record more likely), matching all of them would probably take forever. And if I DO have the bad luck of having two identical (save for IDs) records, I'm screwed.

I was thinking about writing SP which would insert records one at a time, and knowing both the original id and just assigned by SQL could add record to the mapping table. But wouldn't that require having to go through staging table with a cursor and calling this SP one by one? Or could I write a trigger for the destination table? But how would the trigger know the source ID?

I was hoping that maybe there is some fool-proof method that wouldn't require SPs, triggers, etc.

Anyway - I appreciate all ideas.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-23 : 11:03:47

so why don't you just put the @stg.id in the destination table?
what's another column, right?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-23 : 11:16:30

quote:
so why don't you just put the @stg.id in the destination table? what's another column, right?

I can't add columns to the destination tables. But you're right - maybe instead of having mapping table, I could add dest_id to the staging table.

declare @stg table (id char(10), nme varchar(3), dest_id int)
insert	@stg
select	'ko00000001', 'wke', NULL union all
select	'ko00000002', 'agt', NULL union all
select	'ww00000007', 'kel', NULL union all
select	'ww00000011', 'pal', NULL

How would I write UPDATE for dest_id column, inserting consecutive numbers into it?

Because if I had that, I could insert dest_id into destination table by switching IDENTITY_INSERT, right?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-23 : 11:21:40

and what's wrong with dest_id being identity?

since you're just inserting data into this destination table there's no need for updating, no?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-23 : 11:28:04

quote:
and what's wrong with dest_id being identity?

since you're just inserting data into this destination table there's no need for updating, no?

I personally have nothing against MSSQL assigning IDs in the final table

. And you're right - I'm not going to be updating this table afterwards.

However - I'll need to have the newly generated ID (from dest) as part of primary key in another table. And to resolve things in this other table (let's call it dest_2), I may have to go back to the source. Which is why I'm looking for a way to map the records...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-23 : 12:11:41

well what i'd do is put the char(10) id from source table and identity id into the dsestination table.
that way you have the mapping there.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-23 : 13:39:51

Since adding columns to the dest table is not an option for me, I'd appreciate learning the following:

declare @stg table (id char(10), nme varchar(3), dest_id int)
insert	@stg
select	'ko00000001', 'wke', NULL union all
select	'ko00000002', 'agt', NULL union all
select	'ww00000007', 'kel', NULL union all
select	'ww00000011', 'pal', NULL

How do I write UPDATE for dest_id column, inserting consecutive numbers into it, as in:

id          nme  dest_id
---------------------------
ko00000001  wke  10
ko00000002  agt  11
ww00000007  kel  12
ww00000011  pal  13

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-23 : 16:05:15

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-28 : 09:06:39

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources