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]

Re: Access / SQL Query issue


"Jason" <JayCallas@hotm ail.com> wrote in message
news:f01a7c89.0 309081059.1817f 089@posting.goo gle.com...[color=blue]
> 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.[/color]

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]

Re: Access / SQL Query issue

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,SECUR ITY_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+'_'+SYMB OL_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:[color=blue]
> 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.[/color]

[Jason]

Re: Access / SQL Query issue

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

- Jason

Steve Kass <skass@drew.edu > wrote in message news:<W287b.103 80$_26.6571@new sread2.news.atl .earthlink.net> ...[color=blue]
> 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,SECUR ITY_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+'_'+SYMB OL_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:[color=green]
> > 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.[/color][/color]