articles >> developer >> How to Write SQL to Dynamically Script ...

How to Write SQL to Dynamically Script Mass INSERT Statement Scripts

By : Josh Calderonello Jul 26, 2002

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]

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.