Someone on my team needed to create a ton of SQL insert statements to create some test data. We actually have a tool that does this, but he needed to pull data from a complex query with multiple joins and other nasty stuff. The insert statement that he needed was only 3 columns, but he had to insert a lot of data and didn’t want to write the insert scripts manually.

Stupid Dev Trick #1 to the Rescue

I showed him the super special column combinitorial power of a spread sheet copy and paste. With this he was able to create the insert statements in less than the time it took to write the query to get the data.

Here is the gist of the trick. Open a spread sheet, in the first column write the first half of the insert statement you want to produce, up to the point that you need to add your data:

| INSERT INTO Table1 (Column1, Column2, Column3) VALUES ( |

Then run your query and copy the first column of data into Column 2.

| Datavalue 1 |
| Datavalue 2 |
| Datavalue 3 |
| remaining...|

Next add a comma in Column 3 next to each data value (use copy and paste), then do the same for the remaining values from your query results. Finally in the last columns close the insert statement.

| INSERT INTO Table1 (Column1... | Datavalue 1 | ,        | Other value 1 | )        |
| INSERT INTO Table1 (Column1... | Datavalue 2 | ,        | Other value 2 | )        |
| INSERT INTO Table1 (Column1... | Datavalue 3 | ,        | Other value 3 | )        |
| INSERT INTO Table1 (Column1... | remaining...| ,        | remaining.... | )        |

Next copy the insert statement you wrote in column 1 to each row. Then you can just copy all of the columns to a text editor or SQL Management Studio in my case and the columns will be magically turned into white space and ready for running.

INSERT INTO Table1 (Column1, Column2, Column3) VALUES ( Datavalue 1, Other value 1 )
INSERT INTO Table1 (Column1, Column2, Column3) VALUES ( Datavalue 2, Other value 2 )
...

Conclusion

I have used this stupid trick and variations thereof to save a ton of time to produce one-off solutions. Yes, it would be better to create a script or update our test data extraction tool to handle this, but with the pressure of time and not wanting to solve edge cases, this stupid trick is actually smart when you can’t squeeze another brain cell for a quick idea and you have nothing else up your sleeve.