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]

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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |