Last post May 16, 2019 06:07 AM by Yuki Tao
May 15, 2019 11:54 AM|Peter Cong|LINK
In one of my SQL table, a row of data with the primary key is 26, I want to change it to 7, is it possible?
I did some searches, and tried to create a new row with the set of the primary key change like this, after this row is successful created, then I will delete the existing row with primary key of '26',
SET IDENTITY_INSERT table1 -- this is not compile at all.
insert into table1
value(7, 'data1', 'data2') -- the '7' is the primary key value
can anybody help please?
May 15, 2019 12:20 PM|PatriceSc|LINK
The syntax is SET IDENTITY_INSERT table1 ON and you'll use SET IDENTITY_INSERT table1
OFF once done which can be seen in the documentation at https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017
Of course you are supposed to do this kind of thing only to fix some very unusual siutuation and not as part of the usual application life...
May 15, 2019 12:39 PM|yogyogi|LINK
This is what you can do:
SET IDENTITY_INSERT MyTable ON
-- Your update query
SET IDENTITY_INSERT MyTable OFF
May 16, 2019 06:07 AM|Yuki Tao|LINK
Hi Peter Cong,
You really could refer to my
SET IDENTITY_INSERT StudentAccounts ON;
INSERT INTO StudentAccounts (Userid, Username, Password) VALUES (21, 'xx', 'zxczxc')
GO SET IDENTITY_INSERT StudentAccounts OFF;
How it works: