Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
BenHi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
David Portas
SQL Server MVP
--

Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
Ben
Hi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>
|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
David Portas
SQL Server MVP

Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
BenHi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
--
David Portas
SQL Server MVP
--

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!
Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL...pare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegr oups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>
|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQ...mpare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL_Data_Compare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?