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
Showing posts with label gang. Show all posts
Showing posts with label gang. Show all posts
Subscribe to:
Posts (Atom)