Generate table with self reference

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

Generate table with self reference

Postby fatherjack » Mon May 10, 2010 2:38 pm

Hi,

I may just be missing something so hopefully there is a quick solution to this. How do I configure a column to reference another column in the same table. For example I want to fill an Employees table having on column as Staff_Number which is unique and NOT NULL ie every member of staff has a Staff Number. There is also a Managers_Staff_No column that isnt unique and is NOT NULL ie everyone has a manager but some people share managers.

In a SQL Data Generator project I have created a RegEx [A-Z][A-Z][A-Z][0-9][0-9] for the Staff_Number and I now want the Managers_Staff_No to be values from that column... How should I do that please?

Currently the table has no records so there is nothing to generate the Managers_Staff_No

thanks

Jonathan
-----------------------------------------
Senior DBA
Careers South West Ltd
fatherjack
 
Posts: 224
Joined: Tue Mar 13, 2007 12:33 pm
Location: Cornwall

Postby Brian Donahue » Wed May 12, 2010 6:21 pm

Hi Jonathan,

As far as I know, the only way to generate data based on generated data in another column of the same table is using the SDK and the IronRuby generator. There is some information near the bottom of this blog post:

http://www.simple-talk.com/dotnet/.net- ... generator/
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby fatherjack » Thu May 13, 2010 9:59 am

Hi Brian,

Thanks for that. All a bit too complicated and involved. I think I'll just add a static value and run a bespoke TSQL to update the column after the import is done.

May be worth adding as a new feature though ...

Cheers

Jonathan
-----------------------------------------
Senior DBA
Careers South West Ltd
fatherjack
 
Posts: 224
Joined: Tue Mar 13, 2007 12:33 pm
Location: Cornwall

Postby Brian Donahue » Thu May 13, 2010 11:06 am

Hi Jonathan,

We probably need to do something in this are, but as the Data Generator uses BULK INSERT as the underlying technology, I'd assume this makes it more difficult.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby fatherjack » Thu May 13, 2010 11:39 am

Yeah, I expect you'd have to do it as a second pass or maybe create the values for referenced columns in a primary step so they were available. You just need a copy of what will be inserted into col1 ready beforehand so that col2 can pick some values from it ...
-----------------------------------------
Senior DBA
Careers South West Ltd
fatherjack
 
Posts: 224
Joined: Tue Mar 13, 2007 12:33 pm
Location: Cornwall


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests