Red Gate forums :: View topic - Generate table with self reference
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

Generate table with self reference

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



Joined: 13 Mar 2007
Posts: 224
Location: Cornwall

PostPosted: Mon May 10, 2010 2:38 pm    Post subject: Generate table with self reference Reply with quote

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



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed May 12, 2010 6:21 pm    Post subject: Reply with quote

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-framework/embedding-ironruby-and-the-dlr-into-sql-data-generator/
Back to top
View user's profile Send private message
fatherjack



Joined: 13 Mar 2007
Posts: 224
Location: Cornwall

PostPosted: Thu May 13, 2010 9:59 am    Post subject: Reply with quote

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



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Thu May 13, 2010 11:06 am    Post subject: Reply with quote

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



Joined: 13 Mar 2007
Posts: 224
Location: Cornwall

PostPosted: Thu May 13, 2010 11:39 am    Post subject: Reply with quote

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
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