Generating Seed Data Through Select Statements

Often times you will need to generate seed data for a table such as a cross reference table when the data for this already exists in the database already.  Sometimes this is done when you decide to refactor your table design to include a cross reference table (many to many) where a one to many relationship existed previously.  If you have the seed data in your table already you can generate a host of SQL statements (usually INSERTS) for your database.

By issuing a SQL Statement like below, you need to understand the SQL Statement that you desire.  In this instance if you have a cross reference table that you need to generate between Customers and Orders you would have a desired INSERTS like below:

INSERT INTO customer_orders_xref VALUES (1, 1)

INSERT INTO customer_orders_xref VALUES (1, 2) 

INSERT INTO customer_orders_xref VALUES (3, 1)

In instances where you can have dozens or hundreds of desired SQL statements this can be tedious and error prone.  Instead if the data exists in some form in the database you can have it outputted with a simple SQL statement like the one below:

SELECT 'INSERT INTO customer_orders_xref VALUES (' + CAST(c.order_id AS VARCHAR(10)) + CAST(c.customer_id AS VARCHAR(10)) + ')'

FROM orders o

INNER JOIN customer c ON o.customer_id = c.customer_id

WHERE c.name LIKE '%Williams%'

If the first parameter was a fixed value you would just hard code it in your SQL.

SELECT 'INSERT INTO customer_orders_xref VALUES (2, ' + CAST(c.customer_id AS VARCHAR(10)) + ')'

FROM orders o

INNER JOIN customer c ON o.customer_id = c.customer_id

WHERE c.name LIKE '%Williams%'

 You could use techniques like this to generate all sorts of SQL Statements.  Let's say we added an Account Alias column (acct_alias) to the Account table and now we need to change them all to some sort of intelligible name.

SELECT 'UPDATE account SET acct_alias = ''Account# ' + c.name + ''') WHERE account_id = ' + c.account_id FROM customer 

For more complicated SQL generation you can use a code generation tool such as MyGeneration (free Open Source).