Solved

Query to Create Primary Keys

Posted on 2013-11-16
3
292 Views
Last Modified: 2013-11-21
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.
0
Comment
Question by:sherbug1015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 39654112
Add more columns in the temp table with column names like
tablename and primarykey1,primarykey2,...

Then check whether the parameters @PKColName2, @PKColName3,... has values. If there create dynamic queries accordingly

Raj
0
 

Author Comment

by:sherbug1015
ID: 39654549
Raj

Any chance you can help me with the query.  I have attached a subset of the dataset

This is the query that compiles the datatset

insert into #tmppkrows

SELECT KU.table_name as tablename,column_name as primarykeycolumn1,ku.ordinal_position
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

 Instead of a flat dataset you are saying to create a pivoted table liked:

tablename   1  2   3

where 1,2,3 represent the ordinal position.

Can you help me with the pivoted table query.  I am not that familiar with them.

Thanks.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39665576
Hi

I appologize as I couldn't keep in touch with your question, as I am busy these days.

Hope everything went fine ?

Raj
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question