Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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."

share|improve this question
    
No there isn't. Maybe if you explain why you need it someone can suggest an alternative. –  Martin Smith Jan 9 '13 at 18:22
1  
I'm not sure I quite catch your meaning. A "normal" insert doesn't allow you to push a value onto an identity field. You have to explicitly tell SQL Server that you're going to supply the identity value via SET IDENTITY_INSERT ON. If you still want to prevent them, one could use an INSTEAD OF trigger to always bypass provided values. Am I missing something in your question? –  billinkc Jan 9 '13 at 18:22
    
I agree with Bill... if users are generating INSERT queries with their own identity values, you can use an instead of trigger to discard the user-provided values, set identity_insert on, or you can correct the behavior (e.g. tell them to not specify that column in the insert). –  Aaron Bertrand Jan 9 '13 at 18:36
    
the schema was developed pre-SQL 2000 where that was a decent idea - no, GUID as a clustering key has never been a great idea, but it is occasionally used in more modern versions which offer NEWSEQUENTIALID(). –  Aaron Bertrand Jan 9 '13 at 18:43
    
@AaronBertrand, I did not say it was a great idea, i just said it was decent idea, up to 2000 where it all changed and became a bad idea. Also I am stuck between a rock and a hard-place as for NEWSEQUENTIALID() as the legacy code also does do named column inserts and specifies the Guid in a lot of places too (for example the code that interacts with the UI and knows how to handle the dynamic columns). –  Scott Chamberlain Jan 9 '13 at 18:47
show 2 more comments

2 Answers

up vote 2 down vote accepted

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:

CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
GO
CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER PRIMARY KEY);
GO

INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

ALTER TABLE dbo.[target] ADD TargetID INT IDENTITY(1,1);
GO

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2

Now I can also change the primary key and repeat the process:

ALTER TABLE [dbo].[target] DROP CONSTRAINT PK__target__3BD019E50386B4EA;
ALTER TABLE [dbo].[target] ADD CONSTRAINT PK__target__3BD019E50386B4EA  
  PRIMARY KEY (targetID);

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2
1     41FE97FF-7D45-46EB-8A0D-B2C3BA1E67EA   3

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):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD ' + t.name + 'ID INT IDENTITY(1,1);'
FROM sys.tables AS t
WHERE name IN (...) -- you will need to fill in this part
AND NOT EXISTS 
(
  SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id] 
    AND (is_identity = 1 OR name = t.name + 'ID')
);

SELECT @sql;
-- EXEC sp_executesql @sql;

(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 the EXEC.)

And the drop / re-create of the primary keys:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + 
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + k.name + ';
  ALTER TABLE ' + 
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT ' 
  + k.name + ' PRIMARY KEY (' + t.name + 'ID);' 
FROM sys.key_constraints AS k
INNER JOIN sys.tables AS t
ON k.parent_object_id = t.[object_id]
WHERE k.[type] = 'PK'
AND t.name IN (...); -- again, you'll want to identify the list of tables

SELECT @sql;
-- EXEC sp_executesql @sql;

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.

share|improve this answer
2  
A caveat: this insert/select syntax without the column list might not always work in future versions of SQL Server, and of course it won't survive most other changes that only occur to either the source table or the target table (and not both). –  Aaron Bertrand Jan 10 '13 at 1:16
add comment

The example in the link you provided looks like this:

INSERT INTO ORDERS OVERRIDING USER VALUE
   (SELECT * FROM TODAYS_ORDER)

Besides of the OVERRIDING USER VALUE that SQL Server does not know, this statement is following bad coding practices. For several reasons you always want to specify the columns you are inserting to as well as the columns in the select. With that it is however easy to duplicate the functionality - just leave out the identity column:

INSERT INTO dbo.Orders(col2, col3, col4)
SELECT Col2, Col3, Col4 FROM dbo.TodaysOrders;

Now that you added a lot more detail to your question, I guess the column list does not help. You could however add an instead-of trigger to discard the identity column by providing a full column list insert inside the trigger.

You could even auto-generate those triggers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.