Generate table with self reference

fatherjack2

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

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- ... generator/

fatherjack2

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

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.

fatherjack2

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