comparing tables

Hi,

I have two tables containing (ahem) lists of mp3 tunes. One is the "master" list (table name "mp3_master") - everything I've got on my home pc. The other (table name "mp3") details everything I've got on my work pc.

Both tables have an id field, an "artist" field and a "title" field.

I'd like to simply compare the two tables and return a list of any artist/title combinations that are in the mp3 table (ie: that I have at work) but not in the mp3_master table (ie: that I haven't yet taken home).standard sql solution:

select id, artist, title from mp3
except
select id, artist, title from mp3_master

microsoft sql/server syntax has minus instead of except, i believe

if that don't work, do it the old-fashioned way:

select id, artist, title from mp3
where id not in (select id from mp3_master)

rudy
http://rudy.ca/|||Except is a function used in Analysis Services - MDX

The query

select id, artist, title from mp3
where id not in (select id from mp3_master)

will work fine, however it is a slow way, performance wise. Once a match is found the main query will not stop scanning the sub-query. A faster query is to use the NOT EXISTS

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

Once a match is found the search stops|||Hmm - maybe I should have added that the id numbers in these tables don't match up.

select id, artist, title from mp3
where title not in (select title from mp3_master)

I used that. As long as I've not got the same tune by different artists, I should be ok.

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

I get a "syntax error near "." on that - is that some sort of shorthand for a join?

Cheers anyway :)|||i don't have sql/server to test on, so i'm guessing, but like i said, i think the operator is MINUS

select artist, title from mp3
minus
select artist, title from mp3_master

if the id numbers don't match up, you don't want to match on id number in the subselect, neither a's way nor mine

rudy|||Hi,

The 'minus' clause certainly won't work in Query Analyzer. There is no syntax even simlar in SQL Server.

The 'not in' or 'where not exists' are the correct syntax.

You had some typos in the SQL that failed with the syntax error. It should be:

select id, artist, title from mp3
where not exists (select * from mp3_master where mp3_master.mp3.id = mp3_master.id)

Hope this helps.

- Andy Abel|||thanks andy, it wouldn't be the first time sql/server didn't support standard sql ;)

spudhead, since you can't match on ids, try this --

select id, artist, title from mp3
where not exists
( select 1 from mp3_master
where artist = mp3.artist
and title = mp3.title )

rudy|||I love reading these forums, to help people and also to read SQLServer bashing. I'm a big fan of SQLServer, so when I read a jab or a bash I have to find out if what is said is true.

it wouldn't be the first time sql/server didn't support standard sql

I tried to find out the ANSI standards for SQL and the only thing I could find on MINUS is:
The MINUS keyword is not ANSI-compliant, the implementation of the MINUS operator is implemented in Oracle.
So bully for Oracle|||yeah, i love these syntax discussions too

actually, the ansi standard operator is EXCEPT

oracle's support of MINUS is non-standard

:cool:

rudy

compare two tables

Hi all,
We have one master table with all records of the second table but the second
table doesn't contains all the records of the master table.
Like:
Master Table
ID Value
1 A
1 B
1 C
1 D
2 A
2 B
Second Table
ID Value
1 A
1 D
2 A
How can i compare so I can have this result for 2 million records?
1 B
1 C
2 B
Tks in advance
JFBSELECT MasterTable.Id, MasterTable.Value, SecondTable.Id
FROM MasterTable
LEFT OUTER JOIN SecondTable
ON MasterTable.Id = SecondTable.Id
AND MasterTable.Value = SecondTable.Value
WHERE SecondTable.ID IS NULL
Should display all the records from the Master Table that do not have a
match in the SecondTable.
"JFB" <help@.jfb.com> wrote in message
news:eQhPgH56FHA.3388@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> We have one master table with all records of the second table but the
> second table doesn't contains all the records of the master table.
> Like:
> Master Table
> ID Value
> 1 A
> 1 B
> 1 C
> 1 D
> 2 A
> 2 B
> Second Table
> ID Value
> 1 A
> 1 D
> 2 A
> How can i compare so I can have this result for 2 million records?
> 1 B
> 1 C
> 2 B
> Tks in advance
> JFB
>|||Great Bill... tks for you help :)
Rgds
JFB
"Bill Edwards" <billedwards@.msn.com> wrote in message
news:u8sxPT56FHA.3648@.tk2msftngp13.phx.gbl...
> SELECT MasterTable.Id, MasterTable.Value, SecondTable.Id
> FROM MasterTable
> LEFT OUTER JOIN SecondTable
> ON MasterTable.Id = SecondTable.Id
> AND MasterTable.Value = SecondTable.Value
> WHERE SecondTable.ID IS NULL
> Should display all the records from the Master Table that do not have a
> match in the SecondTable.
> "JFB" <help@.jfb.com> wrote in message
> news:eQhPgH56FHA.3388@.TK2MSFTNGP11.phx.gbl...
>sqlsql

Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)
You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)
|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:

> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>
|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:

>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:

Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> > How do I commit a nested transaction while rolling back the outer
> > transaction
> >
> > BEGIN MASTER TRANSACTION
> >
> > Action 1
> >
> > BEGIN NESTED TRANSACTION
> > Action2
> > COMMIT NESTED TRANSACTION
> >
> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> > action
> > 2 committed.)
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
>>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
>>Atomicity
>>A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>> You can't. If you roll back an outer transaction all the nested ones
>> roll
>> back as well. If you used a SAVE TRAN you can roll it back but any work
>> done inside that (even nested trans ) are rolled back as well. If you
>> issue
>> a plain ROLLBACK (not associated with a nested tran) then everything is
>> rolled back.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>> > How do I commit a nested transaction while rolling back the outer
>> > transaction
>> >
>> > BEGIN MASTER TRANSACTION
>> >
>> > Action 1
>> >
>> > BEGIN NESTED TRANSACTION
>> > Action2
>> > COMMIT NESTED TRANSACTION
>> >
>> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
>> > action
>> > 2 committed.)
>>

Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transactio
n
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. Th
e
action of that call will be rolled back right? Or is just if the action is
a
statement in the same procedure?
"Andrew J. Kelly" wrote:

> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you iss
ue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:

>What about if one of the action's is a call to another stored procedure. T
he
>action of that call will be rolled back right? Or is just if the action is
a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>

command line and insert

I'm trying to insert into a table 2 values one of which is an

exec master..xp_cmdshell @.command where I have assigned @.command with a value

this statement gives me the result into a 1 col. table fine:

insert into mytable99(col1) exec master..xp_cmdshell @.command

now what I want to do is put col2 in there as well!!

ie. insert into mytable99(col1,col2) values (exec master..xp_cmdshell @.command, '123')

I get a sytax error ... on the exec ??

Could anyone help re the proper way of doing this ... thanks in advancedid'nt know U could do something like this - sounds possibly clever

Syntax wise this command expects the optional parameter no_output after the comma

xp_cmdshell {'command_string'} [, no_output]

Your probably better off with with assigning each value to a local variable then inserting the contents of the variables into the table - something like

SET @.CmdShellOutput = exec master..xp_cmdshell @.command
SET @.StringNumber = '123'

insert into mytable99(col1,col2)
values (@.CmdShellOutput , @.StringNumber)

Sorry not had time to test this syntax but hopefully U get the picture.

GW