Post reply

Generate Insert Statements for List of Tables

  • November 1, 2006 at 7:11 am

    #192898

    Hi,

    I need to automate the process of populating newly created database tables with static data. 

    I've been able to use a DTS package for this purpose along with the DTSRun utility, but my static data source tables have not really been settled in design or content and maintanence on the DTS package is tedious.

    Does anyone know of a way to easily generate an sql script containing insert statements for a subset of the tables in a particular database?

    Thanks in advance!

  • November 1, 2006 at 11:53 am

    #669291

    there's a famous full featured script that does exactly that;

    http://vyaskn.tripod.com/code.htm#inserts

    you'd do something like

    EXEC sp_generate_inserts  'tbcounty' and get results like this:

    or a subset:

    EXEC sp_generate_inserts  'tbcounty' ,@from="from tbcounty where state='NY'"

    INSERT INTO [tbcounty] ([COUNTYTBLKEY],[INDEXTBLKEY],[STATE],[DESCRIP],,[STATETBLKEY],[REGIONTBLKEY],[EDREGIONTBLKEY])VALUES(1,137,'NY','Allegany','003',35,NULL,NULL)
    INSERT INTO [tbcounty] ([COUNTYTBLKEY],[INDEXTBLKEY],[STATE],[DESCRIP],,[STATETBLKEY],[REGIONTBLKEY],[EDREGIONTBLKEY])VALUES(2,137,'NY','Albany','001',35,NULL,NULL)
    INSERT INTO [tbcounty] ([COUNTYTBLKEY],[INDEXTBLKEY],[STATE],[DESCRIP],,[STATETBLKEY],[REGIONTBLKEY],[EDREGIONTBLKEY])VALUES(3,137,'NY','Broome','007',35,NULL,NULL)
    INSERT INTO [tbcounty] ([COUNTYTBLKEY],[INDEXTBLKEY],[STATE],[DESCRIP],,[STATETBLKEY],[REGIONTBLKEY],[EDREGIONTBLKEY])VALUES(4,137,'NY','Cattaraugus','009',35,NULL,NULL)
    ...

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply