How to SUM to change every row record to a new value?

Question

I am using Microsoft SQL Server Management Studio.

I want to sum each row of my record (say, column ID) with a constant value (say 50).

So here is a better picture:

Click here for the image that i prepared for better understanding of my requirement.

I know SUM can be used to sum up the entire column and come out with a TOTAL at the end of the row, but how about adding every row's record with a constant value?

Once the sum is done, I need to do my last step, which is to reset the ID of the next new row (which doesn't need to SUM like above) from 154 to 999. Can anyone also advice me on resetting primary key?

[modify primary key](https://msdn.microsoft.com/en-us/library/ms189251.aspx) — ɐlǝx, Jul 07 '16 at 16:36
What's with the [tag:sas] tag? Is that accidental, or is this actually going into SAS? — Joe, Jul 07 '16 at 18:31
Possible duplicate of [How to change identity column values programmatically?](http://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically) — Martin Brown, Jul 08 '16 at 13:01
ID is your unique reference to a row, it should not matter at all what value is in ID as long as it is unique for that table. If you have some other human reason for a "reference number" that is "in sequence" do NOT USE ID, use some other field. — Paul Maxwell, Jul 09 '16 at 04:47

score 1 · Answer 1 · answered Jul 08 '16 at 12:54

Dick,

You can change the identity column's next value easily by executing DBCC CheckIdent command

Unfortunately, there is not an easy way to update an identity column value. For existing records, what I experienced till now is to copy all data into a new temp table and organize values on this helper table. Then drop existing table. As following step rename the temp table to original table name.

If you try to remove identity property of a column, the SSMS generates a script as follows

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_MyTable
    (
    id int NOT NULL,
    value int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_MyTable SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
     EXEC('INSERT INTO dbo.Tmp_MyTable (id, value)
        SELECT id, value FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT' 
GO
COMMIT

I know this does not sound an easy way to update identity column values.

How to SUM to change every row record to a new value?

1 Answers1