Add a Column to Existing Table and Uniquely Number Them on Ms SQL Server

Add a column to existing table and uniquely number them on MS SQL Server

This will depend on the database but for SQL Server, this could be achieved as follows:

alter table Example
add NewColumn int identity(1,1)

Adding a primary key column to an existing table in SQL Server?

Simply alter the table and add the column with the appropriate characteristics.

alter table x add id smallint identity(1,1) not null primary key; 

Thrown together quickly and you probably should get in the habit of naming constraints. fiddle. I will note that you may or may not want to use an identity column - think carefully about your actual goal and how you want to continue using this table after the alteration.

Add additional column to give a count on number of occurance on an existing column

We can try using COUNT as an analytic function here:

SELECT
    UniqueID,
    Person1Data1,
    PersonalData2,
    COUNT(*) OVER (PARTITION BY UniqueID) CountID
FROM yourTable;

adding a unique id and PK to existing tables

In T-SQL:

ALTER TABLE table_name 
ADD id INTEGER IDENTITY PRIMARY KEY NOT NULL

But as Yaakov says, it's also very easy to do in SSMS.

Add row number to existing column with additional criteria

Use partition by:

update item
    set ItemDescription = concat(ItemDescription, row_num)
from (select ItemDescription,
             row_number() over (partition by i.ItemDescription order by i.ItemId) as row_num 
      from Item i
     ) item;

Note that you should not use varchar without a length specification. To get around this, I used concat() rather than +, because it will convert the number to a string.

How add unique key to existing table (with non uniques rows)

You can do as yAnTar advised

ALTER TABLE TABLE_NAME ADD Id INT AUTO_INCREMENT PRIMARY KEY

OR

You can add a constraint

ALTER TABLE TABLE_NAME ADD CONSTRAINT constr_ID UNIQUE (user_id, game_id, date, time)

But I think to not lose your existing data, you can add an indentity column and then make a composite key.

How to add a unique constraint using a column from another table?

You could use an indexed view as an external constraint:

CREATE VIEW dbo.CompanyServices
WITH SCHEMABINDING
AS
SELECT
  c.COMPANY_ID,
  s.SERVICE_CODE
FROM       dbo.COMPANY c
INNER JOIN dbo.LSP     l ON c.COMPANY_ID = l.COMPANY_ID
INNER JOIN dbo.SERVICE s ON l.LSP_ID     = s.LSP_ID
GO

CREATE UNIQUE CLUSTERED INDEX UQ_CompanyServices
ON dbo.CompanyServices (COMPANY_ID, SERVICE_CODE);

The index will make sure there's no duplicates of (COMPANY_ID, SERVICE_CODE) in your data.

---

Related Topics