How can we help you today? How can we help you today?

Set a column in all tables to same value

I have a common column in all tables of my database. I would like SQL Generator to use the same value in all tables per round. For example, I have 149 tables and I want to generate data in batches and be able to filter based on the batch that was generated. I did see that I could change the value in the project file but that is tedious, at least for the first round. After the first round I can use a find and replace but getting help with the first round would be nice.
JeremyR
0

Comments

6 comments

Sort by
  • Jessica R
    Hi @JeremyR!

    You should still be able to use a Find and Replace for the first round by changing the value for one of the columns first and then opening the .sqlgen project in a text editor to find/replace the .

    For example, using a SQL Expression to set the value of a CategoryID column to 'Round1' has the following xml in the .sqlgen file:
    <value version="1" type="SDGField">
    <Name>CategoryID</Name>
    <FieldType>Generated</FieldType>
    <PrimaryKey>False</PrimaryKey>
    <ForeignKey>False</ForeignKey>
    <Unique>False</Unique>
    <AllowsNulls>False</AllowsNulls>
    <SqlType>Integer32</SqlType>
    <Precision>10</Precision>
    <Scale>0</Scale>
    <DefinedSize>4</DefinedSize>
    <Generator version="1" type="GeneratorDetails">
    <GeneratorTypeName>RedGate.SQLDataGenerator.ExpressionGenerators.Python.CrossColumnExpressionGenerator</GeneratorTypeName>
    <DisplayName>Simple expression</DisplayName>
    <Description>Generates data using a simple IronPython expression</Description>
    <CategoryName>Generic</CategoryName>
    <GeneratorProperties type="Dictionary<string, object>" version="1">
    <element>
    <key type="string">Seed</key>
    <value type="string">1030</value>
    </element>
    <element>
    <key type="string">Randomize</key>
    <value type="string">False</value>
    </element>
    <element>
    <key type="string">Code</key>
    <value type="string">'Round1'</value>
    </element>
    ...
    ...
    ...
    </Generator>

    You could find and replace the <Generator> node as needed for the matching columns in the other tables.
    Jessica R
    0
  • JeremyR
    Thanks, I had been using a text editor. I was hoping that there was something in the tool. Like I said, the first round is very tedious but using a text editor later is fairly easy.
    JeremyR
    0
  • Jessica R
    Hi @JeremyR,

    Ah I see, thanks for clarifying that!

    There currently isn't a built-in feature to make the same change to multiple columns at once, unfortunately. We do have a feature request for this with reference SDG-1002 though and I have +1'd this with your feedback.

    Thank you and so sorry again that there isn't an easier way to do this at the moment!
    Jessica R
    0
  • Jessica R
    Hi @JeremyR!

    You should still be able to use a Find and Replace for the first round by changing the value for one of the columns first and then opening the .sqlgen project in a text editor to find/replace the Generator details.

    For example, using a SQL Expression to set the value of a CategoryID column to 'Round1' has the following xml in the .sqlgen file:
    <value version="1" type="SDGField">
    <Name>CategoryID</Name>
    <FieldType>Generated</FieldType>
    <PrimaryKey>False</PrimaryKey>
    <ForeignKey>False</ForeignKey>
    <Unique>False</Unique>
    <AllowsNulls>False</AllowsNulls>
    <SqlType>Integer32</SqlType>
    <Precision>10</Precision>
    <Scale>0</Scale>
    <DefinedSize>4</DefinedSize>
    <Generator version="1" type="GeneratorDetails">
    <GeneratorTypeName>RedGate.SQLDataGenerator.ExpressionGenerators.Python.CrossColumnExpressionGenerator</GeneratorTypeName>
    <DisplayName>Simple expression</DisplayName>
    <Description>Generates data using a simple IronPython expression</Description>
    <CategoryName>Generic</CategoryName>
    <GeneratorProperties type="Dictionary<string, object>" version="1">
    <element>
    <key type="string">Seed</key>
    <value type="string">1030</value>
    </element>
    <element>
    <key type="string">Randomize</key>
    <value type="string">False</value>
    </element>
    <element>
    <key type="string">Code</key>
    <value type="string">'Round1'</value>
    </element>
    ...
    ...
    ...
    </Generator>

    You could find and replace the <Generator> node as needed for the matching columns in the other tables.
    Jessica R
    0
  • Jessica R
    Hi @JeremyR!

    You should still be able to use a Find and Replace for the first round by changing the value for one of the columns first and then opening the .sqlgen project in a text editor to find/replace the .

    For example, using a SQL Expression to set the value of a CategoryID column to 'Round1' has the following xml in the .sqlgen file:
    <value version="1" type="SDGField">
    <Name>CategoryID</Name>
    <FieldType>Generated</FieldType>
    <PrimaryKey>False</PrimaryKey>
    <ForeignKey>False</ForeignKey>
    <Unique>False</Unique>
    <AllowsNulls>False</AllowsNulls>
    <SqlType>Integer32</SqlType>
    <Precision>10</Precision>
    <Scale>0</Scale>
    <DefinedSize>4</DefinedSize>
    <Generator version="1" type="GeneratorDetails">
    <GeneratorTypeName>RedGate.SQLDataGenerator.ExpressionGenerators.Python.CrossColumnExpressionGenerator</GeneratorTypeName>
    <DisplayName>Simple expression</DisplayName>
    <Description>Generates data using a simple IronPython expression</Description>
    <CategoryName>Generic</CategoryName>
    <GeneratorProperties type="Dictionary<string, object>" version="1">
    <element>
    <key type="string">Seed</key>
    <value type="string">1030</value>
    </element>
    <element>
    <key type="string">Randomize</key>
    <value type="string">False</value>
    </element>
    <element>
    <key type="string">Code</key>
    <value type="string">'Round1'</value>
    </element>
    ...
    ...
    ...
    </Generator>

    You could find and replace the <Generator> node as needed for the matching columns in the other tables.
    Jessica R
    0
  • Jessica R
    Hi @JeremyR!

    You should still be able to use a Find and Replace for the first round by changing the value for one of the columns first and then opening the .sqlgen project in a text editor to find/replace the Generator details.

    For example, using a SQL Expression to set the value of a CategoryID column to 'Round1' has the following xml in the .sqlgen file:
    <value version="1" type="SDGField">
    <Name>CategoryID</Name>
    <FieldType>Generated</FieldType>
    <PrimaryKey>False</PrimaryKey>
    <ForeignKey>False</ForeignKey>
    <Unique>False</Unique>
    <AllowsNulls>False</AllowsNulls>
    <SqlType>Integer32</SqlType>
    <Precision>10</Precision>
    <Scale>0</Scale>
    <DefinedSize>4</DefinedSize>
    <Generator version="1" type="GeneratorDetails">
    <GeneratorTypeName>RedGate.SQLDataGenerator.ExpressionGenerators.Python.CrossColumnExpressionGenerator</GeneratorTypeName>
    <DisplayName>Simple expression</DisplayName>
    <Description>Generates data using a simple IronPython expression</Description>
    <CategoryName>Generic</CategoryName>
    <GeneratorProperties type="Dictionary<string, object>" version="1">
    <element>
    <key type="string">Seed</key>
    <value type="string">1030</value>
    </element>
    <element>
    <key type="string">Randomize</key>
    <value type="string">False</value>
    </element>
    <element>
    <key type="string">Code</key>
    <value type="string">'Round1'</value>
    </element>
    ...
    ...
    ...
    </Generator>

    You could find and replace the <Generator> node as needed for the matching columns in the other tables.
    Jessica R
    0

Add comment

Please sign in to leave a comment.