CODEWITHSUNDEEP
sql-servert-sql
CallumVass
CallumVass

Reputation: 11448

SQL Server : insert a new row as ID of 1

I have a table called ComplaintCodes which contains about 15 rows and 2 columns: ComplaintCodeId and ComplaintCodeText.

I want to insert a new row into that table but have its ID set to 1 which will also add 1 to all of the ID's that exist already. Is this possible?

EDIT

Using SQL Server and ComplaintCodeId is an identity / PK column

Upvotes: 0

Views: 491

Answers (3)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

After some thought, it seems to me that the best solution to your problem is to change the PK to be non-identity. Then you can set the value to whatever you'd like.

I still think that using a Display Order column (which is the only reason I can think you'd care the order in the table) would be a fine solution, but if you really want the PK order to be the display order, then changing the PK to non-identity would be a good long-term solution as you wouldn't have these problems in the future.

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

update <table> set ComplaintCodeId =ComplaintCodeId +1

insert into <table>
select 1,'other column'

Edit:

If its a PK+Identity column, then its a very bad idea to do like this. You cannot update an identity column..

Instead of updating you could do something like this:

select row_number() over (order by ComplaintCodeId desc) as row_num,
       ComplaintCodeId 
from table

and use row_num instead of ComplaintCodeId

Upvotes: 1

David M
David M

Reputation: 72870

It's possible as two separate DML statements, an UPDATE to update the ID and a subsequent INSERT. But this will fail if you are using the ID as a foreign key in another table of course, so you'd need to find a way to update across all related tables.

Why would you want to do this though? Suggest you take a step back and reconsider the design decision that has brought you to this question.

And yes, as podiluska says in his/her(/its!) comment, please specify which DBMS you are using in your question and/or tags.

Upvotes: 5

Related Questions