Currently showing entries with the tag: MySql
|
page 1 of 1
|
Writing SQL Scripts to Generate SQL Scripts
September 01, 2007 • 6:43AM • permalink
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:
CREATE TABLE [countries]
(
country_id INT,
country NVARCHAR(64)
);
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:
SELECT
'INSERT INTO [countries]
(country_id, country)
VALUES
(' + CAST (country_id AS VARCHAR(3)) + ', ''' + country + ''');'
FROM [countries];
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.
Let's say you have the following table:
CREATE TABLE [countries]
(
country_id INT,
country NVARCHAR(64)
);
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:
SELECT
'INSERT INTO [countries]
(country_id, country)
VALUES
(' + CAST (country_id AS VARCHAR(3)) + ', ''' + country + ''');'
FROM [countries];
--Note the use of the '' to escape the single-quote for output
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.
0 comments
|
page 1 of 1
|
