Red Gate forums :: View topic - Copy Table with Self-Join - bug report?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 1
SQL Data Generator 1 forum

Copy Table with Self-Join - bug report?

Search in SQL Data Generator 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
xhead



Joined: 28 Mar 2008
Posts: 4

PostPosted: Tue Apr 22, 2008 6:08 pm    Post subject: Copy Table with Self-Join - bug report? Reply with quote

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:
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 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 * FROM [dbo].[Breed]


Then this against the target table:

Code:
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:
update dbo.Breed
   set ParentBreedId = ref.ParentBreedId
from
   dbo.Breed b
   join sourceDB.dbo.breed ref on ref.BreedId = b.BreedId
Back to top
View user's profile Send private message
benhall



Joined: 14 Dec 2007
Posts: 98

PostPosted: Wed Apr 23, 2008 11:26 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
xhead



Joined: 28 Mar 2008
Posts: 4

PostPosted: Wed Apr 23, 2008 3:27 pm    Post subject: Reply with quote

Quote:
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.
Back to top
View user's profile Send private message
benhall



Joined: 14 Dec 2007
Posts: 98

PostPosted: Fri Apr 25, 2008 3:26 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group