Sql-server – Generated bug data script how not to set the IDENTITY in the insert statements

azure-sql-databasesql serverssms

I'm using SQL Server Management Studio to generate sql script (big one)
to update Azure DB.
The problem is that the script is generated with IDENTITY on and the insert statements are exported with the ID column which as result gives me the errors :

Cannot insert explicit value for identity column in table 'Table_Name' when IDENTITY_INSERT is set to OFF.

My question is how to generate the script right that is will not use IDENTITY on and the insert.

Best Answer

bcp is probably a better way to achieve this and works well with Azure SQL Database. It's a command-line tool that enables you to extract and load data easily. For example:

bcp "dbo.yourTable" out c:\temp\temp.bcp -S "yourSourceServer" -d "yourSourceDatabase" -T -n

bcp "dbo.yourTable" in c:\temp\temp.bcp -S "yourTargeteServer" -d "yourTargetDatabase" -U someSQLLogin -P somePassword -E -n

Specify the -E switch in the bcp in statement to keep your IDENTITY values. Replace the table, server and database names in my template above and see if you can get the first statement to run. Note in the first example I am using Windows Authentication (-T) but SQL Authentication in the second one (-U for User and -P for password). The main MSDN page for bcp provides some good examples:

https://msdn.microsoft.com/en-us/library/ms162802.aspx

If your table already has some data in it, consider inserting your data into a staging table first, then INSERT to your main table, eg

SET IDENTITY_INSERT yourTargetTable ON
GO

INSERT INTO yourTargetTable ( columnList )
SELECT columnList
FROM yourStagingTable s
WHERE NOT EXISTS
    (
    SELECT *
    FROM yourTargetTable t
    WHERE s.yourPrimaryKey = t.yourPrimaryKey
    )
GO

SET IDENTITY_INSERT yourTargetTable OFF
GO

There are other methods of achieving this.

  • For example, you could accomplish the same thing using SQL Server Integration Services (SSIS) but there is some overhead to creating an SSIS project.
  • For example, if your table is not too big, create a copy of your table in tempdb using something like SELECT [all columns except the identity column] INTO tempdb.dbo.scratchTable FROM yourMainTable. Run the Generate Scripts option against this copy then delete your scratch table.
Related Question