Adding a new primary key column

SQL SERVER, I'm working with a table that uses a guid for the key instead of an int but for the integration I'm working on, I need it to be an int. So, I want to write something that will create an ID column if it doesn't exist and populate it with the next highest ID. I'm not really sure how to do this though. Does anyone have code that does this?

i've tried this but the update doesn't work because they're null

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'c_Product'AND COLUMN_NAME = 'Produ开发者_如何学编程ctId')
BEGIN
ALTER TABLE c_Product ADD ProductId INT
END
UPDATE c_Product SET ProductId = (SELECT Max(ProductId) + 1 END FROM c_Product)

---

Am I being dense here. Are you not just wanting to do:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'c_Product'AND COLUMN_NAME = 'ProductId')
BEGIN
    ALTER TABLE c_Product ADD ProductId INT IDENTITY(1,1) not null
END

Which will assign identity values for all existing rows, and then provide new numbers as new rows are inserted.

---

Ok, so you add a column called ProductId of type INT, which by the way is NULL. SQL Server stores values in one of three states. TRUE, FALSE, NULL. Now remember Null is not empty, it is not zero, it is not true and it is not false. It is pretty much that thing that never happens and doesnt exist. Get the point? Ok, so your row with this highest value in the 'newly' created column is ---- NULL. So what is the MAX(PorductId)? NULL. What is Null + 1? NULL. So, unless you set one value to 1 anywhere, you will be stuck in this never ending loop forever.

Back to table design. Using a GUID for a primary key, is a very bad no no! This will severely negatively impact the performance of your table. Unless you write an app that has to join over multiple servers, and platforms, STAY AWAY FROM USING GUIDS as the primary key. Long explanation, but basically all inserts are random, and then index page splits occur just about with every insert, and searches are slower too, and to use 16 bytes per row just for the PK column, not good. And then that is added to every other NC index, bad bad bad. If you can, change your table structure for good. Use and int.