Sql-server – Adding a new primary key ID column to an existing table

ddlsql servert-sql

I want to add a new column to a table. The table is already populated with data.

I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it.

I can create a new table thus:

create table myTestTable
(
    ID uniqueidentifier NOT NULL,
    SomeId uniqueidentifier NOT NULL,
    SomeOtherId uniqueidentifier NOT NULL,
    constraint table_constraint PRIMARY KEY (ID)
)

-- modify the script below to copy the old table across to the new one,
-- generating a unique ID (GUID) into

insert into myTestTable
(ID,
ClaimPaymentId,
TemplateId)
select
    NEWID(),
    SomeId ,
    SomeOtherId 
from [dbo].[TableToBeCopied]

I can then drop the copied table and rename the copy to the old table name.

But I have to do about sixty such tables and I want to write a script which will take a table's name as a parameter and automatically generate the column definitions. But I can't see how to do this. Any ideas?

Best Answer

You can add your GUID ID column with the following.

ALTER TABLE [yourSchema].[yourTable] add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL

To do this for multiple tables you could use something like this:

SELECT 'ALTER TABLE '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL'

FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id

WHERE (     (s.name = 'Schema1' AND t.name IN ('Schema1_TableList'))
            OR
            (s.name = 'Schema2' AND t.name IN ('Schema2_TableList'))
            ... -- Add more schemas and their tables as needed
        )

This will give you all the ALTER TABLE statements to create an ID UNIQUEIDENTIFER coulmn with the ID values pre populated.

Related Question