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] GO
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:
SELECT ‘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) + ”” FROM dbo.Local_ZipCodes Loc_Zip ORDER BY Loc_Zip.ZipCode_ID ASC
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.
]]>