Red Gate forums: SQL Data Generator 1

RE: UK postcode unit are restricted

Brian Donahue — Wed, 30 Jan 2013 15:45:12 GMT

Is your previous post the regular expression that works for you? If so, I can suggest future versions of Data Generator use your regular expression.

RE: UK postcode unit are restricted

icecurtain — Tue, 29 Jan 2013 10:38:19 GMT

(AB|B|BA|BB|BD|BH|BL|BN|BR|BS|BT|CA|CB|CF|CH|CM|CO|CR|CT|CV|CW|DA|DE|DG|DH|DL|DN|DT|DY|E|EC|EH|EN|EX|FK|FY|G|GL|GU|H|HG|HP|HR|HS|HU|HX|IG|IM|IP|IV|KA|KT|KW|KY|L|LA|LD|LE|LL|LN|LS|LU|M|ME|MK|MK|N|NE|NG|NN|NP|NR|NW|OL|OX|PA|PE|PH|PL|PO|PR|RG|RH|RM|S|SA|SE|SG|SK|SL|SM|SN|SO|SP|SR|SS|ST|SW|SY|TA|TD|TF|TN|TQ|TR|TS|TW|UB|W|WA|WC|WD|WF|WN|WR|WS|WV|Y|ZE)([1-9]|[1-9][0-9]) [1-9](A|B|D|E|F|G|H|J|N|P|Q|R|S|T|U|V|W|X|Y|Z){2}

UK postcode unit are restricted

icecurtain — Tue, 29 Jan 2013 10:20:58 GMT

The final two letters, the UK postcode unit are restricted to the set ABDEFGHJLNPQRSTUWXYZ (which excludes the six letters CIKMOV)

The code on the download still has these in and is causing validation data issues has anyone a amended version?

Code:

<?xml version="1.0" encoding="iso-8859-1"?>
<generators>
<generator
   type="RedGate.SQLDataGenerator.ExpressionGenerators.RegexpGenerator"
   name="UK PostCode"
   description="CB4 1AS, AS1 9LJ, RD4 1LB, "
   category="Geographical">

   <property name="Expression">(AB|B|BA|BB|BD|BH|BL|BN|BR|BS|BT|CA|CB|CF|CH|CM|CO|CR|CT|CV|CW|DA|DE|DG|DH|DL|DN|DT|DY|E|EC|EH|EN|EX|FK|FY|G|GL|GU|H|HG|HP|HR|HS|HU|HX|IG|IM|IP|IV|KA|KT|KW|KY|L|LA|LD|LE|LL|LN|LS|LU|M|ME|MK|MK|N|NE|NG|NN|NP|NR|NW|OL|OX|PA|PE|PH|PL|PO|PR|RG|RH|RM|S|SA|SE|SG|SK|SL|SM|SN|SO|SP|SR|SS|ST|SW|SY|TA|TD|TF|TN|TQ|TR|TS|TW|UB|W|WA|WC|WD|WF|WN|WR|WS|WV|Y|ZE)([1-9]|[1-9][0-9]) [1-9][A-Z]{2}</property>

   <matches field=".*Post.*Code.*" score="50" minlen="8"/>
   <matches field=".*Addr.*4" score="12" minlen="8"/>

   <type type="string"/>
</generator>
</generators>

RE: globally set bigint and datetime

icecurtain — Thu, 24 Jan 2013 12:58:37 GMT

Code:

<?xml version="1.0" encoding="iso-8859-1"?>
<generators>
<generator
type = "RedGate.SQLDataGenerator.Generators.Number.Int64Generator"
name = "BigInt"
description = "Generates BigInts"
category = "Generic">
<property name = "MinValue">1</property>
<property name = "MaxValue">999999</property>
<property name = "UseSequence">False</property>
<property name = "Unique">True</property>
<property name = "ValidateIncrement">1</property>
<property name = "NullsAllowed">False</property>
<matches field = "." score = "999"/>

Code:

works but thanks for the right direction
path = C:\Program Files (x86)\Red Gate\SQL Data Generator 1\Config

RE: globally set bigint and datetime

james.billings — Wed, 23 Jan 2013 18:23:19 GMT

The datatypes you describe are built in ones so you can't edit an XML to override them. You can create a new one though, and give it a higher score so it gets picked up. For example the BigInt one could have a file defined as this:

Code:

<?xml version="1.0" encoding="iso-8859-1"?>
<generators>
<generator type="RedGate.SQLDataGenerator.Generators.Number.Int64Generator" name="MyBigInt" description="Generates BigInts" category="Generic">
<property name="MinValue">1</property>
<property name="MaxValue">999999</property>
<property name="UseSequence">False</property>
<property name="Unique">True</property>
<property name="ValidateIncrement">1</property>
<property name="NullsAllowed">False</property>
<matches field="." score="999" />
<type type="Int64" />
</generator>
</generators>

You can create a new file in C:\Program Files (x86)\Red Gate\SQL Data Generator 2\Config and the tool will pick it up. The field name matching takes a regular expression, so a . simply means "anything" - thus all fields with a bigint datatype will use this generator.

You can do a similar process to create other generators too.

globally set bigint and datetime

icecurtain — Tue, 22 Jan 2013 16:56:08 GMT

Hi,
How can I globally set that for all Bigint and datetime data types, the default for all tables the project should be 1 thru 999,999 unique with no null ?

Date time 01-01-1901 00:00 - 01-01-1901 00:00 Randomize Time = false

which Xmls do I need to change?

Thanks,

RE: CSV import not working

Brian Donahue — Tue, 20 Nov 2012 09:08:13 GMT

There are some types of fields where you can't change the generator type, such as some foreign key fields. Is that the problem? If so, you're rather stuck. SDG sometimes tries to prevent you from doing things that won't work.

RE: CSV import not working

pk_davidson — Mon, 19 Nov 2012 18:34:44 GMT

Brian:
I see no where to select columns.
I went through the Use existing data source, browsed to the CSV file, made sure the columns were mapped correctly to the appropriate data types. I can see the data read correctly in the CSV File Data Import Settings dialog box. But when I click Finish, the preview of data is still showing random data and if I click Generate Data, it will import the random data, not the csv data.

I've read through help, searched this forum and nothing seems to work.

I click on a column in Tables to Populate and look at the Column generation settings and the only option I have for the two foreign key fiels is Foreign Key generator. I want to read those from a CSV file!

I can set a string and date field by click on the columns and going through the entire browse and map again but this begs the question, why did I did it in the first place for the table in the first place ?

It appears that the populate from csv will not populate FK fields ?

RE: CSV import not working

pk_davidson — Mon, 19 Nov 2012 16:27:12 GMT

I'll look at it again Brian.
But sure thought I had everything selected correctly...

RE: CSV import not working

Brian Donahue — Mon, 19 Nov 2012 14:19:11 GMT

If you select a CSV file as a data source for a table, that makes it possible for you to choose the columns from the CSV to supply values for the columns of generated data. You then must select the column settings in SQL Data Generator, and pick the CSV as the generator, choosing the column from the CSV that you want to map to the column in the generated data.

If you do not select the column from the CSV, then SQL Data Generator will continue using the default generator for the column.

CSV import not working

pk_davidson — Fri, 16 Nov 2012 01:11:59 GMT

I have a very simple table (many to many) that I am trying to populate from a csv file. There are only 5 columns.
Primary Key, Foreign Key 1, Foreign Key 2, LastUpdateBy, LastUpdate

I run the SQL Data Generator, select the csv file, go through the mapping and click the Red Button Generate Data.

What happens is that random data is put into the table.
This is what I see in the lower half of your program.
This lower data never changes from random data.
And yes, I have selected to use a csv file....

Frustrated at such a simple task.
Thanks

RE: Anonymizing data and preserving foreign key relationships

anna.p — Tue, 16 Oct 2012 23:21:36 GMT

A feature to allow simple updates would be fantastic.

I'm getting around this limitation by creating views against the source tables that select only the columns I don't want to "update". I use the Table/View generator to populate those columns in the target and randomizing generators for the columns I want to "update". The only columns for which this doesn't work are those with a Foreign Key constraint and IDENTITY columns.

2 possible enhancements to address this, which would help my situation but possibly not the other posters', are:

RE: How can I use relative paths with sqlgen files?

tvg — Wed, 10 Oct 2012 11:29:09 GMT

Has this issue been resolved? I'm also having trouble with the absolute paths here and was hoping, that there is a way or workaround to use relative paths for those files.

RE: Multiple columns as unique

gvsoft — Mon, 01 Oct 2012 22:03:20 GMT

What happened to this requested suggestion? Did it get tossed out? Four years later, version 2.0.3.1 still doesn't have this capability.

RE: Generating rows with a composite PK

gvsoft — Mon, 01 Oct 2012 21:58:40 GMT

What is the status of this bug? I have DG 2.0.3.1 and it still has the same problem.