Access / SQL Query issue

[Jason]

I will explain (or at least try to) first and then give an example
after.

I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns (which are UNIQUE
columns in the master table) in the source tables that are repeated in
the same table or across the source tables.

Example:
Source 1 Source 2

[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
MSFT STOCK AAPL STOCK
AA STOCK MER OPTION
MER OPTION
MSFT OPTION

Master

[SECURITY] --- Unique Key
[SECURITY_TYPE]
As you can see in the example, MSFT is listed twice in Source 1 -- as
a stock and an option. MER is listed as an option in both Source 1 and
Source 2.

My first solution (when I wrote it in Access) was to read in a source
row, check if it existed in the master table, and then add it if it
did not. This of course ran very slow. To fix that I added all the
source rows to a temp table and then appended that temp table to the
master. Since I was able to turn off warnings with the SetWarnings
action in access any row that caused a duplicate key error got ignored
BUT the query continued to the end.

(Note: DISTINCT on a source table does not work since MSFT-Stock is a
row while MSFT-Option is another.)

I rewrote the query to run in SQL Query Analyzer but cannot seem to
figure out how to turn off errors so the query runs to completion. It
may not even be possible to do so in which case I am hoping for a
solution other than checking each row to see if it exists before I add
it.

[Simon Hayes]

"Jason" <Ja*******@hotmail.com> wrote in message
news:f0**************************@posting.google.c om...

I will explain (or at least try to) first and then give an example
after.

I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns (which are UNIQUE
columns in the master table) in the source tables that are repeated in
the same table or across the source tables.

Example:
Source 1 Source 2

[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
MSFT STOCK AAPL STOCK
AA STOCK MER OPTION
MER OPTION
MSFT OPTION

Master

[SECURITY] --- Unique Key
[SECURITY_TYPE]
As you can see in the example, MSFT is listed twice in Source 1 -- as
a stock and an option. MER is listed as an option in both Source 1 and
Source 2.

My first solution (when I wrote it in Access) was to read in a source
row, check if it existed in the master table, and then add it if it
did not. This of course ran very slow. To fix that I added all the
source rows to a temp table and then appended that temp table to the
master. Since I was able to turn off warnings with the SetWarnings
action in access any row that caused a duplicate key error got ignored
BUT the query continued to the end.

(Note: DISTINCT on a source table does not work since MSFT-Stock is a
row while MSFT-Option is another.)

I rewrote the query to run in SQL Query Analyzer but cannot seem to
figure out how to turn off errors so the query runs to completion. It
may not even be possible to do so in which case I am hoping for a
solution other than checking each row to see if it exists before I add
it.

Your example isn't really clear without DDL (CREATE TABLE statements) and
sample data. You seem to indicate that Master.Security is the primary key,
but if so, you could have only one row in Master for MSFT, not two, which is
what I think you want. If my understanding is correct, you probably want
something like this, but without extra details, it's only a guess:

insert into
dbo.Master (Security, Security_Type)
select
Symbol,
Symbol_Type
from
dbo.Source1 s1
where
not exists (select *
from dbo.Master m
where s1.Symbol = m.Security and
s1.Symbol_Type = m.Security_Type)

You can modify the same query to use Source2.

Simon

[Steve Kass]

Jason,

It's not clear to me what you want, particularly in
the SECURITY_TYPE column of the master table. If the
primary key of that table is SECURITY, then you can't put
MSFT in twice - so what is SECURITY_TYPE? Is it something
different than STOCK or OPTION?

If you want the master table to represent what the source
tables represent, you will need to have (SECURITY,SECURITY_TYPE)
as the primary key (and you won't have the trouble you're having.

If SECURITY_TYPE is something completely different, and you
want MSFT in the table only once, then you can just insert
select SYMBOL, NULL from [Source 1]
union
select STOCK_SYMBOL, NULL from [Source 2]
-- union will eliminate duplicates

and then you can update the SECURITY_TYPE column as needed.

If you want to list MSFT-STOCK and MSFT-OPTION as the values
in the first column of the master table, then insert
select SYMBOL+'_'+SYMBOL_TYPE, NULL -- still don't know what type is
from [Source 1]
union
....

-- Steve Kass
-- Drew University
-- Ref: C3330EF8-EA21-4840-8969-1BC9DCF019B0

Jason wrote:

I will explain (or at least try to) first and then give an example
after.

I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns (which are UNIQUE
columns in the master table) in the source tables that are repeated in
the same table or across the source tables.

Example:
Source 1 Source 2

[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
MSFT STOCK AAPL STOCK
AA STOCK MER OPTION
MER OPTION
MSFT OPTION

Master

[SECURITY] --- Unique Key
[SECURITY_TYPE]
As you can see in the example, MSFT is listed twice in Source 1 -- as
a stock and an option. MER is listed as an option in both Source 1 and
Source 2.

My first solution (when I wrote it in Access) was to read in a source
row, check if it existed in the master table, and then add it if it
did not. This of course ran very slow. To fix that I added all the
source rows to a temp table and then appended that temp table to the
master. Since I was able to turn off warnings with the SetWarnings
action in access any row that caused a duplicate key error got ignored
BUT the query continued to the end.

(Note: DISTINCT on a source table does not work since MSFT-Stock is a
row while MSFT-Option is another.)

I rewrote the query to run in SQL Query Analyzer but cannot seem to
figure out how to turn off errors so the query runs to completion. It
may not even be possible to do so in which case I am hoping for a
solution other than checking each row to see if it exists before I add
it.

[Jason]

My bad. When I type this out, SECURITY_TYPE should NOT have been
included in the Master table.

- Jason

Steve Kass <sk***@drew.edu> wrote in message news:<W2******************@newsread2.news.atl.eart hlink.net>...

Jason,

It's not clear to me what you want, particularly in
the SECURITY_TYPE column of the master table. If the
primary key of that table is SECURITY, then you can't put
MSFT in twice - so what is SECURITY_TYPE? Is it something
different than STOCK or OPTION?

If you want the master table to represent what the source
tables represent, you will need to have (SECURITY,SECURITY_TYPE)
as the primary key (and you won't have the trouble you're having.

If SECURITY_TYPE is something completely different, and you
want MSFT in the table only once, then you can just insert
select SYMBOL, NULL from [Source 1]
union
select STOCK_SYMBOL, NULL from [Source 2]
-- union will eliminate duplicates

and then you can update the SECURITY_TYPE column as needed.

If you want to list MSFT-STOCK and MSFT-OPTION as the values
in the first column of the master table, then insert
select SYMBOL+'_'+SYMBOL_TYPE, NULL -- still don't know what type is
from [Source 1]
union
...

-- Steve Kass
-- Drew University
-- Ref: C3330EF8-EA21-4840-8969-1BC9DCF019B0

Jason wrote:

I will explain (or at least try to) first and then give an example
after.

I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns (which are UNIQUE
columns in the master table) in the source tables that are repeated in
the same table or across the source tables.

Example:
Source 1 Source 2

[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
MSFT STOCK AAPL STOCK
AA STOCK MER OPTION
MER OPTION
MSFT OPTION

Master

[SECURITY] --- Unique Key
[SECURITY_TYPE]
As you can see in the example, MSFT is listed twice in Source 1 -- as
a stock and an option. MER is listed as an option in both Source 1 and
Source 2.

My first solution (when I wrote it in Access) was to read in a source
row, check if it existed in the master table, and then add it if it
did not. This of course ran very slow. To fix that I added all the
source rows to a temp table and then appended that temp table to the
master. Since I was able to turn off warnings with the SetWarnings
action in access any row that caused a duplicate key error got ignored
BUT the query continued to the end.

(Note: DISTINCT on a source table does not work since MSFT-Stock is a
row while MSFT-Option is another.)

I rewrote the query to run in SQL Query Analyzer but cannot seem to
figure out how to turn off errors so the query runs to completion. It
may not even be possible to do so in which case I am hoping for a
solution other than checking each row to see if it exists before I add
it.