t-sql question, please help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

t-sql question, please help!

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2010-03-09 : 22:18:08

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, I want to see if the ordernum of first table if it's present in the second table.
and produce 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 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.

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.ofDups ColValuethatsDup

Thanks.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 23:18:44

Hi,
Good Day,

could you please try this..


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 
Select 11, 'DD'

select * from #orders_company1
select * from #orders_company2

;with cte(ordernum,itemdesc)
as
(
select ordernum,itemdesc from #orders_company1
union all
select ordernum,itemdesc from #orders_company2
)



select ordernum,itemdesc,dupcounts,case when dupcounts=1 then 'N' else 'Y' end as Dups from
(
 select ordernum,itemdesc,count(ordernum)as dupcounts from cte 
 group by ordernum,itemdesc
)as t 



drop table #orders_company1
drop table  #orders_company2

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2010-03-10 : 13:03:08

This script works fine if the table has primary key defined on single column, but I have some tables with primary key defined on more than
one column.
Could you please help me 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)

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2010-03-10 : 14:29:47

Please help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources