minus and intersect functionality

Hi,
I've used the minus functionality which is available in Oracle and
i would like to use it in SQL server, but i don't know how to. The
folllowing is how it works in Oracle

Select symbols from symbol_table
minus
select tsymbols from trade

It returns a list of all the symbols from symbol_table which are not
present in trade.
Similarly, the intersect will return only those which are common to
both.

I was wondering if someone throw some light on this problem for me.

Thanks in advance,
SumanthA generalised minus query using NOT EXISTS:

SELECT symbol
FROM symbol_table
WHERE NOT EXISTS
(SELECT *
FROM trade
WHERE tsymbol = symbol_table.symbol)

Alternatively, "Minus JOIN", assuming the Symbol column is unique in both
tables:

SELECT S.symbol
FROM symbol_table AS S
LEFT JOIN trade AS T
ON S.symbol = T.tsymbol
AND T.tsymbol IS NULL

Generalised intersection, using EXISTS:

SELECT symbol
FROM symbol_table
WHERE EXISTS
(SELECT *
FROM trade
WHERE tsymbol = symbol_table.symbol)

Or just an INNER JOIN if Symbol is unique in both tables:

SELECT S.symbol
FROM symbol_table AS S
JOIN trade AS T
ON S.symbol = T.tsymbol

--
David Portas
----
Please reply only to the newsgroup
--

Migration of SQL 7.0 package to SQL 2005

Did you try to migrate this package to SQL 2005. The migration process will
migrate the DTS package into SSIS, and encapsulate all functionality it
can't upgrade into mini DTS 2000 packages which will run as subpackages of
the SSIS Package. You can then at your leisure redesign the mini DTS 2000
packages into your SSIS package.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We are migrating from SQL server 7.0 to SQL server 2005.
> In 7.0 I have made a DTS package that does a complex task on pre-scheduled
> times, started by a SQL job.
> This is a brief description of the complex task:
> -Copy files from several computers in the network to a local "working
> directory"
> -Verify the contents of the files (pre-formatted plain text files)
> -Move files with size or content errors to a different directory
> -Load the files that have no errors into a SQL server table (tblImport)
> -Mark records in tblImport that already exist in tblHistory
> -Gather user information about the files that have been succesfully loaded
> into tblImport
> -Create several reports (plain text files) based on the information in the
> files
> -Print the reports to different printers
> -Add the non-marked records in tblImport to tblHistory
> -Delete the non-marked records in tblImport
> -Send e-mails to several recipients with statistical information about the
> task
> This package is made with a number of activex scripts and SQL tasks.
> Because the server this package is running on now is outdated, I have the
> challenge to make this task work in SQL server 2005.
> The database it is using is already transferred to SQL 2005.
> I have seen that creating packages in 2005 is totally different than
> creating packages in 7.0.
> In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
> only
> load a .dtsx file.
> To me it is ok if I have to build the package in 2005 from scratch or
> build
> it in a totally different way, just as long as it is not taking me too
> much
> time. A different way could be that I copy and paste the scripts and SQL
> statements into several steps of a 2005 job, with some minor changes, but
> I'm not sure if that is a good solution. Especially when something goes
> wrong, I would like to know exactly where things went wrong and what steps
> have to be executed to finish the job.
> Any help or ideas are appreciated!
>
>
Can you save the dts packages as structured storage and then open them up in
SQL 2000 Package Designed and save them there and then try to use the
migration wizard?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> Thanks for your response Hilary.
> Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> gives
> me an error saying "This SQL Server version (7.0) is not supported."
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> will
> news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> pre-scheduled
> loaded
> the
> the
> the
> but
> steps
>