How to Write SQL to Dynamically Script Mass INSERT Statement Scripts
The following is an article that describes a unique approach of scripting out the contents of a table.
The reasons for doing so are many. The context that I will provide is one where you have a development environment where static tables exist for the purpose of storing application lookup data (i.e. a ZipCode table), but the contents of the table change often enough that it is often tedious to modify an INSERT script you may have saved off somewhere.
For instance, let’s suppose we have a ZipCode table that is defined in the following manner:
CREATE TABLE [dbo].[local_ZipCodes]
[ZipCode_ID] [INT] NOT NULL,
[ZipCode] [VARCHAR] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ZipPlus] [VARCHAR] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
If the table defined above has several hundred entries to start out with during an initial development cycle, and several hundred more are added during each cycle, maintaining an INSERT script can get very tedious very quickly.
Let’s suppose our ZipCode table has the following values in it:
ZipCode_ID ZipCode ZipPlus
—————– ———— ———–
1 43402 1111
2 43403 2222
3 43404 1111
. . .
. . .
650 89140 2358
One way in which a developer can quickly script out the contents of the ZipCode Table is in the following manner:
‘INSERT Local_ZipCodes (ZipCode_ID, ZipCode, ZipPlus) ‘ + ‘SELECT ‘ +
CONVERT(VARCHAR(20), Loc_Zip.ZipCode_ID) + ‘, ‘ + ”” + CONVERT(VARCHAR(20), Loc_Zip.ZipCode) + ”” +
‘, ‘ + ”” + CONVERT(VARCHAR(4), Loc_Zip.ZipPlus) + ””
When you run the above statement, your result set should look like the following:
INSERT Local_ZipCodes (ZipCode_ID, ZipCode, ZipPlus) SELECT 1, ‘43402’, ‘1111’
INSERT Local_ZipCodes (ZipCode_ID, ZipCode, ZipPlus) SELECT 2, ‘43403’, ‘2222’
INSERT Local_ZipCodes (ZipCode_ID, ZipCode, ZipPlus) SELECT 3, ‘43404’, ‘1111’
INSERT Local_ZipCodes (ZipCode_ID, ZipCode, ZipPlus) SELECT 650, ‘89140’, ‘2358’
That is all there is to it!
Published with the express written permission of the author. Copyright 2002.