I am tasked to create a script to archive tables in a database. The script should be reusable with minor tweaking.
I have created a dynamic query to create and populate the tables.
Now I need to add Primary Keys to the archive tables to match the primary keys on the non-archive tables.
I have created a #table that has 2 columns (tablename and primarykey)
I am reading the #table in a while loop and running the following statement
select @vssql = 'ALTER TABLE ' + @vstablename + '_ARCHIVE' + ' ADD CONSTRAINT ' + 'pk_' + @PKColName + ' PRIMARY KEY ' +'(' + @PKColName + ')'
Everything goes OK, until I get to one of the 5 tables that has a composite primary key.
There are over 200 tables with a single column primary key and only 5 with a composite primary key.
Can someone suggest a query that would handle both the single and composite primary keys in one pass.
Thanks in advance.