Currently showing entries with the tag: query

This came up at work the other day, but it's a very helpful trick if you find yourself replicating the same set of tables over and over again.

Let's say you have the following table:



Let's say I use a common list of countries everytime I create a new website. The above data is prepopulated and we'll say its filled with about 200 rows. While there are a few easy ways to generate the script, it seems like a waste to do something like create an entire C# program or even a write Python script.

Fortunately, you can use the fact that SELECT can return static data to quickly generate the script, like so:



This would return the following:

INSERT INTO countries (country_id, country) VALUES (1, 'United States');
INSERT INTO country (country_id, country) VALUES (2, 'Afghanistan');
INSERT INTO country (country_id, country) VALUES (3, 'Albania');
...


All you're doing is SELECTing out a concatenated string that will generate your commands. Then you can copy and paste it to a text file, which you can use as a reusable utility script.

This can be very helpful when you use a common framework that gets replicated for several projects. I personally use it for doing things like the above country example, prepopulating geographical information, adding default administrator accounts to new websites, and propagating a list of banned IPs.

I haven't found this feature documented anywhere, but I picked it up from one of Itzik Ben-Gan's articles.

Normally, you use the keyword GO when you need to force the previous batch of code to immediately execute, such as when using DDL or CTEs, like so:




While not useful in the case above, you can repeat a batch by placing an integer value immediately after the GO statement. This trick is very helpful when populating a table with random data or when doing repeated testing. So, for example, if we want to fill the above table with 1,000,000 rows of random integers, we can easily do it!