option for SET IDENTITY_INSERT ON/OFF

Apr 15, 2009 at 8:55 PM
Edited Apr 15, 2009 at 8:59 PM
(*** SORRRY ABOUT THE MESSED UP POST - I CANNOT SEEM TO GET IT TO FORMAT PROPERLY!!! ***)
In order to generate INSERT's that use existing IDENTITY column values, I added a new parameter to my copy of the script

 [bool] $allowIdentityInsert = $true,

Next, just above this line:

if ($resultsets -gt 0) {$obj = "$($originalobj)_$($resultsets)"}

I added this line:

 

[

 

bool] $foundAutoIncrementColumn = $false

Next, just above this line:

if ($col.AutoIncrement -and $noidentity)

 

 

 

 

 

 

 

 

 

 

 

I added these lines:

if ($col.AutoIncrement -and $allowIdentityInsert)
{
    $foundAutoIncrementColumn = $true
    WriteLine "SET IDENTITY_INSERT $obj ON;" $file
    WriteLine 'GO' $file
    WriteLine ' ' $file
}

 

 

 

 

 

 

 

 

Next,  just above this line:

    } #foreach $obj in $tables

I added the following lines:
 

 

 

 

 

 

 

 

 

if ($foundAutoIncrementColumn -and $allowIdentityInsert)
{
    WriteLine
' ' $file
    WriteLine "SET IDENTITY_INSERT $obj OFF;" $file
    WriteLine 'GO' $file
}


This creates output like the following:

SET IDENTITY_INSERT HR.regions ON;
GO

 

 

 

INSERT INTO HR.regions(region_id, region_name) VALUES(1, 'Europe');
INSERT INTO HR.regions(region_id, region_name) VALUES(2, 'Americas');
INSERT INTO HR.regions(region_id, region_name) VALUES(3, 'Asia');
INSERT INTO HR.regions(region_id, region_name) VALUES(4, 'Middle East and Africa');
GO

SET IDENTITY_INSERT HR.regions OFF;
GO

 

 

 

 

 

 

Hope this proves useful.  Feel free to implement it differently than what I did.