Solved

Query to Create Primary Keys

Posted on 2013-11-16
3
288 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Auditing in Azure SQL Database 3 38
Log Backup 2 21
Excel conversion issue with Sql server 14 45
Trying to identify overlapping date ranges 5 10
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now