Copy Table with Self-Join - bug report?

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

Copy Table with Self-Join - bug report?

Postby xhead » Tue Apr 22, 2008 6:08 pm

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.

Code: Select all
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.

Code: Select all
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:

Code: Select all
SELECT * FROM [dbo].[Breed]


Then this against the target table:

Code: Select all
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:

Code: Select all
update dbo.Breed
   set ParentBreedId = ref.ParentBreedId
from
   dbo.Breed b
   join sourceDB.dbo.breed ref on ref.BreedId = b.BreedId
xhead
 
Posts: 2
Joined: Fri Mar 28, 2008 9:29 pm

Postby benhall » Wed Apr 23, 2008 11:26 am

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
benhall
 
Posts: 69
Joined: Fri Dec 14, 2007 11:15 am

Postby xhead » Wed Apr 23, 2008 3:27 pm

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.
xhead
 
Posts: 2
Joined: Fri Mar 28, 2008 9:29 pm

Postby benhall » Fri Apr 25, 2008 3:26 pm

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
benhall
 
Posts: 69
Joined: Fri Dec 14, 2007 11:15 am


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests