Generate table with self reference

[fatherjack]

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

[Brian Donahue]

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/
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[fatherjack]

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

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[fatherjack]

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