While googling I found about the OVERRIDING USER VALUE parameter for an INSERT
for IBM's iSeries.
Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to insert into a table that has an IDENTITY
column and use the auto-assigned value instead of the user value passed in?
In this instance, the main goal is to help convince a superior from moving us off of using a GUID as the clustered primary key (the schema was developed pre-SQL 2000 where that was a decent idea, vs. now where it is a horrible idea) to adding an identity column to the tables and moving the clustered primary key to that and converting the old index to a non-clustered unique index.
The biggest push back is:
There are a lot of places in the legacy code that use things like
Insert into XXXX Select * from YYYY where ...
where the number of columns is different based on run-time adjustable settings on the client's front end. Using a identity column would break those queries whereas GUIDs are unique and won't have the issue and it is too much work to rewrite all of the queries to use named columns due to the fact that the column names are variable per installed client.
So that is why SET IDENTITY_INSERT ON
is not a option, as this will be a primary key column. I was hoping to make the argument that if we could use the SQL Server equivalent of OVERRIDING USER VALUE
I could use an argument like "We can just add this small bit to the insert statements and we don't need to include the column names."
Best Answer
In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well:
Results:
Now I can also change the primary key and repeat the process:
Results:
So I haven't had to change my bad code that uses insert/select without any column lists, as long as the source table doesn't also change and assuming that the only change to the target is the addition of an identity column.
PS here is how you can automate the generation of the IDENTITY columns (assuming you will want
<tablename>ID
):(Note that the
SELECT
output will show you roughly what the command looks like, but due to output limitations in SSMS and depending on how many tables you have, it won't necessarily show you the full command that will get executed when you uncomment theEXEC
.)And the drop / re-create of the primary keys:
And you'll want to do this while the database is in
SINGLE_USER
mode or while the application(s) are otherwise not able to connect to the database. You'll also want to test all this on a QA or dev system before unleashing any of it on production.Now, this still isn't exactly best practice - I highly recommend you stop embedding SQL code in your apps, especially SQL code that does insert/select without specifying column lists.