Alternate way to Fill Columns with Specified SQL

OmegaMan

During the column generation setting for specific columns, I have the need to use specific SQL on a set of columns, in the `User Defined` -> `SQL Statement` option, and it works just fine. My issue is that I have to do this multi-step operation to set the sql for 200+ columns. 

Is there a way to modify the sqlgen file's XML so I can add the sql operation to multiple columns in an outside editor?

-----

The sql I set is specific to each column such as `select OpId from ref.Operation RO

where RO.OperationCategoryId = 101` where the number is different for each of the columns.

Jon_Kirkwood

Hi @OmegaMan,

Thanks for reaching out on the Redgate forums.

As you have mentioned, the sqlgen files are XML structured, so it is possible to modify them externally if you're not able to get them fully as you want in SQL Data Generator.

We don't have any specific software recommendations for doing this work and would suggest keeping a backup of a working sqlgen file whilst making modifications outside of Redgate solutions..

 

I would suggest looking at the <Generator> node inside the XML to see what would need to be modified. 

Doing a quick test I was able to change from a regex field to a custom sql field by copying the generator section from another column in my file.

 In my testing I followed this logic trail to find the values to modify

• Find node <value version='3' type="SDGTable">

      where <Name> = your database table

•  Then find <value version="1" type="SDGField">

       where <Name> = your record name

•  Then modify <Generator> node within that to match your required output.

 

Depending on your comfort level in modifying XML directly we could suggest setting the 200+ columns inside SQL Data Generator to the Custom SQL data type, save the project and then you only need to modify the SQL Query in the sqlgen file:

<element>
  <key type="string">SQLQuery</key>
   <value type="string">(sql query)</value>
</element>

 

 Sample of the SQL Generator code I copy/pasted in the XML and I then modified the bolded values

 

<Generator version="1" type="GeneratorDetails">
            <GeneratorTypeName>RedGate.SQLDataGenerator.Generators.SQL.SQLColumnGenerator</GeneratorTypeName>
            <DisplayName>SQL Statement</DisplayName>
            <Description>Imports data using a SELECT statement</Description>
            <CategoryName>Generic</CategoryName>
            <GeneratorProperties type="Dictionary&lt;string, object&gt;" version="1">
              <element>
                <key type="string">LiveDatabaseSource</key>
                <value version="4" type="LiveDatabaseSource">
                  <ServerName>server\</ServerName>
                  <DatabaseName>databasename</DatabaseName>
                  <Username />
                  <SavePassword>False</SavePassword>
                  <Password />
                  <ScriptFolderLocation />
                  <MigrationsFolderLocation />
                  <AuthenticationType>WindowsIntegrated</AuthenticationType>
                </value>
              </element>
              <element>
                <key type="string">SQLQuery</key>
                <value type="string">SQLQUERY</value>
              </element>
              <element>
                <key type="string">ShuffleData</key>
                <value type="string">True</value>
              </element>
              <element>
                <key type="string">ShuffleRows</key>
                <value type="string">1000</value>
              </element>
              <element>
                <key type="string">LoopQuery</key>
                <value type="string">False</value>
              </element>
              <element>
                <key type="string">NullsAllowed</key>
                <value type="string">False</value>
              </element>
              <element>
                <key type="string">NullProportion</key>
                <value type="string">0.01</value>
              </element>
              <element>
                <key type="string">Seed</key>
                <value type="string">1010</value>
              </element>
            </GeneratorProperties>
          </Generator>

 

Hope this helps you find a way to automate your bulk updates in your data generator project

OmegaMan

I have removed the original comment, but I am seeing the structure. 

OmegaMan

Ok, I see the structure now, under each column contains a `Generator` node. I can change the initial one by hand, save, and then copy that whole `Generator` node into each of the following columns til I reach the end.

Thanks for the insights. I can change the xml by hand with that solution, but I run the risk of having an xml issue.

I am trying to come up with a program to do such a change...or just continue to do it in the program by hand....uggg decisions. 

:-)

Thanks for the help.