Stupid Dev Trick #1
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 ) ...
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.
As you said, there are tools, and one of my favorites is the SSMS Tools Pack. Relative to your post, it will export a query result to a script which inserts into a temp table. This can be easily modified to an insert into a table. The tool does many other things but this one feature has saved me days worth of time.
Thanks for the tip, I never heard of SSMS Tools Pack. Unfortunately, it looks like their site (http://ssmstoolspack.com/) is having issues at the moment so I can’t try it out, but its on my list.
What a lame trick! I’ve done it many times! For exactly the same purpose (generating sql statements). Nice to see someone else using the same stupid trick 🙂 I also used concat() to construct them
It’s so lame! I use a tool for this now, but I will still use this when I don’t want to fight tools. Glad to see we’re in the same club. 😀