t-sql help

Question

t-sql help

Mh-397891

SSCertifiable

Points: 6704
More actions
March 9, 2010 at 3:38 pm

#145702

I have the following tables:
1. orders_company1
2. orders_company2
3. customers_company1
4. customers_company2
5. po_company1
6. po_company1
etc..
I am trying to create a stored procedure to which I will be passing in table1 and
table2 as parameters, then want to compare the primary keys from the two tables
and produce the following output:
DupsNo.ofdupsColValuethatsDup
Create table #orders_company1
(ordernum int Primary Key,
itemdesc varchar(10))
Create table #orders_company2
(ordernum int Primary Key,
itemdesc varchar(10))
Insert into #orders_company1
Select 1, 'AA'
union all
Select 2, 'BB'
union all
Select 3, 'CC'
union all
Select 4, 'CC'
union all
Select 5, 'DD'
Insert into #orders_company2
Select 1, 'AA'
union all
Select 2, 'BB'
union all
Select 3, 'CC'
union all
Select 10, 'CC'
union all
Select 11, 'DD'
select * from #orders_company1
select * from #orders_company2
Final output:
Dups(Y/N)No.ofDupsColValuethatsDup

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

~~So what constitutes a dupe in your example code? Duplicate PK's, duplicate descriptions, or the combination of both?~~

{edit} Sorry... I missed what you said about... "then want to compare the primary keys from the two tables"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 2

I want to see if the ordernum of first table if it's present in the second table. Then show the following output:

Dups(Y/N) No.ofDups ColValuethatsDup

The main purpose of this is I want to merge both the tables into a single table, but before doing that I want to check the duplicates as the ordernum is a primary key column. So if there are any duplicates we shall clean the data before merging into single table. So I want to create a stored proc by passing in the table1 and table2 for which we have to check the primary key column if it's present in the second table. I have lot of tables for which I have to do that so I want to create a proc.

Thanks.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Then, a simple INNER JOIN will find the dupes. Since it's a PK, the only 2 counts you could get is 0 or 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

SELECT OC1.ordernum

FROM #orders_company1 OC1

INTERSECT

SELECT OC2.ordernum

FROM #orders_company2 OC2;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 5

How do I identify the primary key in both the tables, as I will be making a script to check if primary key in the first table is present in the second table. I need to make a generic script which I will be applying for many tables.

LutzM SSC Guru Points: 107049 More actions · Answer 6

To meet the requirement

...create a stored procedure to which I will be passing in table1 and table2 as parameters,...

Pauls query need to be changed into dynamic SQL, I guess:

DECLARE @table1 nvarchar(128)

DECLARE @table1IdCol nvarchar(128)

DECLARE @table2 nvarchar(128)

DECLARE @table2IdCol nvarchar(128)

DECLARE @sql nvarchar(4000)

SELECT

@table1='t1',

@table1IdCol='c1',

@table2='t2',

@table2IdCol='c2'

SET @sql=

'SELECT OC1.'+ @table1IdCol + '

FROM ' + @table1 + ' OC1

INTERSECT

SELECT OC2.'+@table2IdCol + '

FROM ' + @table2 + ' OC2;'

SELECT @sql

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 7

Mh-397891 (3/10/2010)
How do I identify the primary key in both the tables, as I will be making a script to check if primary key in the first table is present in the second table. I need to make a generic script which I will be applying for many tables.

Wouldn't it have been just awesome to include that information in your first post? 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Going to need more dynamic SQL for this. Here is a start on your search for the answer. the following code will help you identify the primary key. I'm going to leave it to you to figure out how to determine what column(s) make up the primary key on a table. Just remember, Books Online is your friend.

select

object_name(object_id),

name,

is_primary_key

from

sys.indexes

where

is_primary_key = 1;

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 9

This script works fine if the table has primary key defined on single column, but it gets complicated if the table has primary key defined on more than one column. Could you please send me a sample code I can use for tables which has primary key defined on more than one column. Thanks.

DECLARE @table1 nvarchar(128)

DECLARE @table1IdCol nvarchar(128)

DECLARE @table2 nvarchar(128)

DECLARE @table2IdCol nvarchar(128)

DECLARE @sql nvarchar(4000)

SELECT

@table1='TABLE1',

@table2='TABLE2'

SET @table1IdCol =

(SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE TABLE_NAME = @table1

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey')

= 1

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +

QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

)

SET @table2IdCol =

(SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE TABLE_NAME = @table2

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey')

= 1

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +

QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

)

SET @sql=

'SELECT OC1.'+ '[' + @table1IdCol + ']' + '

FROM ' + '[' + @table1 + ']' +' OC1

INTERSECT

SELECT OC2.'+ '[' + @table2IdCol + ']' + '

FROM ' + '[' + @table2 + ']' + ' OC2'

Print @sql

--Execute (@sql)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

I won't (but someone else might). You need to try writing it first and if you have problems, show us what you have done and we will help you work through writing the code.

I will give you a hint, you need to concatenate values, and there have been numerous threads, articles, and blog posts on how to accomplish this task.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Mh-397891 (3/10/2010)
This script works fine if the table has primary key defined on single column, but it gets complicated if the table has primary key defined on more than one column. Could you please send me a sample code I can use for tables which has primary key defined on more than one column. Thanks.
DECLARE @table1 nvarchar(128)
DECLARE @table1IdCol nvarchar(128)
DECLARE @table2 nvarchar(128)
DECLARE @table2IdCol nvarchar(128)
DECLARE @sql nvarchar(4000)
SELECT
@table1='TABLE1',
@table2='TABLE2'
SET @table1IdCol =
(SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @table1
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey')
= 1
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
SET @table2IdCol =
(SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @table2
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey')
= 1
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
SET @sql=
'SELECT OC1.'+ '[' + @table1IdCol + ']' + '
FROM ' + '[' + @table1 + ']' +' OC1
INTERSECT
SELECT OC2.'+ '[' + @table2IdCol + ']' + '
FROM ' + '[' + @table2 + ']' + ' OC2'
Print @sql
--Execute (@sql)

Lookup the "EXCEPT Operator" in Books Online... you will find example code for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)