Hey gang, I have 2 100,000 record tables, with a ID & address field. (I've exclude all the other field for now) I need to make sure the ID field in table A is equal to table B and that the address in table A is equal to table B. If the Id's or address do not match create a separate table. Any good ideascreate table table3
( ID integer
, address varchar(255)
)
insert into table3
select ID
, address
from table1
where not exists
( select * from table2
where ID = table1.ID )
union all
select ID
, address
from table2
where not exists
( select * from table1
where ID = table2.ID )
union all
select table1.ID
, table1.address
from table1
inner
join table2
on table1.ID = table2.ID
where table1.address <> table2.address
union all
select table2.ID
, table2.address
from table1
inner
join table2
on table1.ID = table2.ID
where table1.address <> table2.address|||USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table1([ID] int, address varchar(255))
CREATE TABLE Table2([ID] int, address varchar(255))
GO
INSERT INTO Table1([ID],address)
SELECT 1, 'Here' UNION ALL
SELECT 2, 'There' UNION ALL
SELECT 3, 'And Everywhere'
INSERT INTO Table2([ID],address)
SELECT 0, 'Here' UNION ALL
SELECT 2, 'There' UNION ALL
SELECT 3, 'And Everywheres'
GO
SELECT [ID], address
INTO Table3
FROM (
SELECT [ID]
, address
FROM table1
UNION ALL
SELECT [ID]
, address
FROM table2) AS XXX
GROUP BY [ID], address
HAVING COUNT(*) = 1
GO
SELECT * FROM Table3
GO
SET NOCOUNT OFF
DROP TABLE Table1
DROP TABLE Table2
DROP TABLE Table3
GO
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment