Currently showing entries with the tag: SQL Server 2005
|
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
Repeating a Batch in SQL Server 2005
August 08, 2007 • 4:56PM • permalink
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:
CREATE TABLE [dbo].[my_table]
(
id INT
)
GO
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!
INSERT INTO [dbo].[my_table]
(id)
VALUES
(FLOOR(RAND() * 10000))
GO 1000000
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:
CREATE TABLE [dbo].[my_table]
(
id INT
)
GO
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!
INSERT INTO [dbo].[my_table]
(id)
VALUES
(FLOOR(RAND() * 10000))
GO 1000000
|
page 1 of 1
|
