Copy Table with Self-Join - bug report?

xhead

I have a table "Breed" that is a self-referencing table (PK = BreedId, ParentBreedId is FK to BreedId).

I am using the Copy table functionality to copy a list of breeds from a source table to the target. When I run the data generation, the ParentBreedId column values are all the same as the BreedId.

CREATE TABLE [dbo].[Breed]
(
[BreedId] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_Breed_BreedId] DEFAULT (newid()),
[ParentBreedId] [uniqueidentifier] NULL,
[Breed] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)

GO
ALTER TABLE [dbo].[Breed] ADD CONSTRAINT [PK_Breed] PRIMARY KEY CLUSTERED  ([BreedId])
GO
ALTER TABLE [dbo].[Breed] ADD CONSTRAINT [FK_Breed_Breed] FOREIGN KEY ([ParentBreedId]) REFERENCES [dbo].[Breed] ([BreedId])
GO


declare @parentId uniqueidentifier
select @parentId = newid()
insert into dbo.Breed (BreedId, Breed) values (@parentId, 'Dogs')

insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Affenpinscher')
insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Afghan Hound')
insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Airedale Terrier')
insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Akita')

set @ParentId = newId()
insert into dbo.Breed (BreedId, Breed) values (@ParentId, 'Cats')

insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Aegean cat') 
insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'American Longhair') 
insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Asian Semi-longhair (or Tiffanie)') 
insert into dbo.Breed (ParentBreedId, Breed) values(@parentId,'Balinese') 

After running the data generator, I execute this script on the target table to tell me how many rows I have with different ParentBreedId.

select count(*), ParentBreedId from dbo.Breed
group by ParentBreedId

I am expecting 3 rows returned:
2 Null
4 (Cats ID)
4 (Dogs ID)

What I am getting is 2 Null, then 8 rows of 1 each, and looking at the target table data, BreedId == ParentBreedId for all rows except the two rows where ParentBreedId is null.


Running a profiler trace while the data generation occurs doesn't show me much to indicate a problem.

I see this statement running on against the source table:

SELECT * FROM [dbo].[Breed]

Then this against the target table:

select @@trancount; SET FMTONLY ON select * from [dbo].[Breed] SET FMTONLY OFF exec ..sp_tablecollations_90 N'dbo.Breed'
insert bulk [dbo].[Breed] ([BreedId] UniqueIdentifier, [ParentBreedId] UniqueIdentifier, [Breed] NVarChar(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [DisplayImageId] UniqueIdentifier) with (KEEP_NULLS, CHECK_CONSTRAINTS, FIRE_TRIGGERS)
SELECT COUNT_BIG(*) FROM [dbo].[Breed]

I think there may be a bug in the routine that copies a table that has a self-join somehow.

I have overcome this bug by using a post-generation script:

update dbo.Breed
	set ParentBreedId = ref.ParentBreedId
from
	dbo.Breed b
	join sourceDB.dbo.breed ref on ref.BreedId = b.BreedId

benhall

Hello,

Thank you for the detail post. We have had reports about the issue with self referencing tables and the data being the same.

However, if I understand your requirement correctly you want to have your data grouped. so you have multiple values the same.

One solution which I think will work for you is to modify the population method on the FK column. If you select Repeat key values between, you can specify the number of times the PK should appear in the FK column.

The data would then look something like this:

BreedId ParentBreedId Breed

---

---

---

1 1 Scent hounds
2 1 Guard dogs
3 1 Scent hounds
4 1 Cur dogs

I hope this makes sense and helps solve your problem, if it doesn't then please let me know.

Thanks

Ben

xhead

However, if I understand your requirement correctly you want to have your data grouped. so you have multiple values the same.

No, I was only using the GROUP BY query as an assertion that the data was populated correctly. It was my "unit test" to be executed after the data generation was completed.

The original requirement was to copy the data from one table to the other, with no changes.

benhall

Hello,

Sorry for the delay in getting back to you. Sadly, this is not currently supported, I have added a note to see if we can address this for later versions.

You might want to look at using SQL Statement to pull in the required data. However, you will have to run the generation twice, appending the data so we can use it as the source of the FK. Like I said, we do not currently support this, but you might have some luck with the SQL Statement generator.

Sorry I could be of more help. Hopefully we can address this at a later point.

Thank you for your feedback.

Ben