February 18, 2015 at 5:25 am
I have this script:
if object_id('tempdb..#tmp') is not null
drop table #tmp
go
create table #tmp (
--id int not null identity (1,1),
place_id int not null,
data_id int not null
primary key (place_id) with (ignore_dup_key = on))
go
create unique index ix_data_id on #tmp (data_id) with (ignore_dup_key = on)
go
truncate table #tmp
go
declare @rules table
(
place_id tinyint not null,
property_id int not null
primary key (place_id, property_id)
);
declare @data_table table
(
data_id int not null primary key,
property_id int
);
insert into @data_table (
data_id ,
property_id)
values (10, 1),
(20,1),
(30,2),
(40,2)
insert into @rules ( place_id , property_id )
values (1,1),
(2,2),
(3,1),
(4,1),
(4,2),
(4,3),
(4,4),
(4,5)
insert into #tmp (place_id, data_id)
select
place_id,
data_id
from @rules r
join @data_table pp
on r.property_id = pp.property_id
order by
place_id,
r.property_id
select [place_id], [data_id] from #tmp
-- I get this result set:
/*
place_iddata_id
----------------------
110
230
*/
If I add an identity column for the table, then I get a different result set:
create table #tmp (
id int not null identity (1,1),
place_id int not null,
data_id int not null
primary key (place_id) with (ignore_dup_key = on))
go
/*
place_iddata_id
----------------------
110
320
230
440
*/
Does anyone know why?
Thanks
February 18, 2015 at 5:36 am
Bizarre. Haven't used Ignore_dup_key for a while, but this looks wrong to me.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 19, 2015 at 4:05 pm
I've got a funny feeling both of the ignore_dupe_keys on the indexes is causing records to be spat out of the tables on insert:
Add a select above your insert and have a look at the values being returned:
SELECT place_id, data_id
FROM @rules r
INNER JOIN @data_table pp ON r.property_id = pp.property_id
ORDER BY place_id, r.property_id
INSERT INTO #tmp (place_id, data_id)
SELECT place_id, data_id
FROM @rules r
INNER JOIN @data_table pp ON r.property_id = pp.property_id
ORDER BY place_id, r.property_id
SELECT [place_id], [data_id]
FROM #tmp
I think what's happening is if a duplicate row is found both of the records are being spat from the tables as per the below. Weird hey!!!
/*
--can't have a duplicate place_id
--can't have a duplicate data_id
place_id, data_id
110 --No duplicate
120 --Duplicate place_id (spit the row from the @rules table, 2,3,4 left, also spit 20)
230 --No duplicate
240 --Duplicate place_id (spit the row from the @rules table, 3,4 left, also spit 40)
310 --Duplicate data_id (spit the row from the @rules table 4 left, also spit 10)
320 --(20 doesn't exist in the @data_table anymore so nothing to join on)
410 --Duplicate data_id (spit 4 from the @rules table. nothing left in the table.)
420 --table empty
430 --Duplicate data_id
440 --table empty
*/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply