Force uniques - 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

General SQL Server Forums

New to SQL Server Programming

Force uniques

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-03-29 : 04:11:56

I have two tables like this

Table 1
ID Content
1 a
2 b
3 c
4 d

and

Table 2
ID Content
2 e
5 f

and I need to insert the Table 2 content into Table 1, but so that the existing IDs will be updated into something that is not in use i.e. the result is

Result Table (i.e. Table1 after the query is done)
ID Content
1 a
2 b
3 c
4 d
5 f (comes from Table 2)
6 g (2 already exists in Table 1, take the next free which is 6)

It is important that Table 1 IDs are not altered, only the ones from Table 2

So how would I get to there?

I thought that I'll begin with
SELECT ID, Content FROM Table1
UNION
SELECT ID, Content FROM Table2

which gives me:
1 a
2 b
3 c
4 d
5 e
2 f

Should I select the table name of each record as well?
Something like:
SELECT ID, Content, TableName FROM Table1
UNION
SELECT ID, Content, TableName FROM Table2

I'd get
1 a Table1
2 b Table1
3 c Table1
4 d Table1
5 e Table2
2 f Table2

Then take only duplicates
SELECT Id
FROM (SELECT Id FROM Table1
UNION
SELECT Id FROM Table2)
WHERE TableName = Table2
GROUP BY Id
HAVING ( COUNT(Id) > 1 )

I'd get
2 f Table2

Then check the maximum of unique Ids
SELECT Top 1 ID FROM
(SELECT Id FROM Table1
UNION
SELECT Id FROM Table2)

And finally I should update the IDs of Table2. How does that work, especially the part where you assign the next free Id to each of Table2 duplicate records?

The really important question is: Is there any easier way to do this? Like some (pseudocode) Select * from table2 into table1 take next free Id if Id already exist?

Oh, and just to make it clear: Altering the table structure of Table1 is not allowed. So don't say "just make it a primary key of Table1".

Thanks for any answers in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-29 : 04:31:55

[code]
; with
cte as
(
select t1.ID, t1.Content
from table1 t1

union all

select ID = case when t1.ID is null then t2.ID
else NULL
end,
t2.Content
from table2 t2
left join table1 t1 on t2.ID = t1.ID
)
select ID = case when ID is null
then max(ID) over()
+ row_number() over (partition by ID order by Content)
else ID
end,
Content
from cte
order by ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-03-29 : 04:33:56

Thanks khtan!

I'll check this when I get to my testing environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources