Access / SQL Query issue

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" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0309081059.1817f089@.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|||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.|||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.10380$_26.6571@.newsread2.news.atl.earthlink. 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.

About unicode string comparison

Hi,
Can anyone here explain why the 2 Japanese strings are equal?
select case when N'あきよ' = N'ア_ヨ' then 'true' else 'false' end
select convert(varbinary,N'あきよ')
select convert(varbinary,N'ア_ヨ')
Thanks,
JamesJames,
They are equal under a Kana-insensitive collation. If you need to
distinguish them, use a Kana-sensitive collation. Equality of strings
is based on the rules of the collation in effect, not the strings' binary
representations.
select
case when N'あきよ' = N'ア_ヨ' collate Latin1_General_CS_AS_KS_WS
then 'true' else 'false'
end
This returns 'false'
Steve Kass
Drew University
James Ma wrote:

>Hi,
>Can anyone here explain why the 2 Japanese strings are equal?
>select case when N'あきよ' = N'ア_ヨ' then 'true' else 'false' end
>
>select convert(varbinary,N'あきよ')
>select convert(varbinary,N'ア_ヨ')
>Thanks,
>James
>|||Thank you so much.
"Steve Kass" wrote:

> James,
> They are equal under a Kana-insensitive collation. If you need to
> distinguish them, use a Kana-sensitive collation. Equality of strings
> is based on the rules of the collation in effect, not the strings' binary
> representations.
> select
> case when N'あきよ' = N'ア_ヨ' collate Latin1_General_CS_AS_KS_WS
> then 'true' else 'false'
> end
> This returns 'false'
> Steve Kass
> Drew University
> James Ma wrote:
>
>

About the security

Dear all,
How to indetify if there is a trace running against a sql server or not? I
will try to explain better. I've got lots of sql2k remote servers added into
my EMC client. Up to here that's fine. But due to a big complexity of our
organization sometimes oneself have serious doubts about of the "do and
donts" (you could think that's ridiculous or even amazing but that's the
truth)
If I perfectly know that auditing process are running then I don't want do
any SELECT, obviously.
"exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
Profiler' obvioulsy say us that Sql tool for that is running. But the
question is, if were another tool disguissed doing that?
You never will have the total security of that anybody is spying one server.
thanks in advance,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)It may be going off at a bit of a tangent, but if you need to be that
sure that no-ones going to capture your SQL statements, have you
considered encrypting the statement, sending that to an SP that will
decrypt it and then run it using sp_executesql. This way the actual
statement should never be seen as it is executed as part of the outer
stored procedure. Only I can't see why if someone really wanted to get
at your statements they couldn't just read the packets going through,
and never actually interface with the SQL server (though I admit I do
not know how SQL server actually sends its instructions - perhaps they
are already encrypted while being sent).|||fn_trace_getinfo
"Enric" wrote:

> Dear all,
> How to indetify if there is a trace running against a sql server or not? I
> will try to explain better. I've got lots of sql2k remote servers added in
to
> my EMC client. Up to here that's fine. But due to a big complexity of our
> organization sometimes oneself have serious doubts about of the "do and
> donts" (you could think that's ridiculous or even amazing but that's the
> truth)
> If I perfectly know that auditing process are running then I don't want do
> any SELECT, obviously.
> "exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
> Profiler' obvioulsy say us that Sql tool for that is running. But the
> question is, if were another tool disguissed doing that?
> You never will have the total security of that anybody is spying one serve
r.
> thanks in advance,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)