Composite key "NOT IN" query?

I have a table with a composite key formed by the unique combination of columns w, x, y, z

I'm trying to write an INSERT statement along the following lines

INSERT INTO myTable

(SELECT w, x, y, z FROM someTable) t1

WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already)

Help would be appreciated. Can you use the NOT IN keyword on composite values?

Can you use the NOT IN keyword on composite values?

Yes,but that is expensive.

FromQuery SQL Server Performance Tuning Tips

But If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

Use EXISTS or NOT EXISTS|||

INSERT INTO MyTable

SELECT t1.w,t1.x,t1.y,t1.z

FROM someTable t1

LEFT JOIN MyTable t2 ON t1.w=t2.w and t1.x=t2.x and t1.y=t2.y and t1.z=t2.z

WHERE t2.w IS NULL

This should also work:

INSERT INTO MyTable

SELECT w,x,y,z

FROM someTable

EXCEPT

SELECT w,x,y,z

FROM MyTable

Complicated Licensing Question

We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
CPU box. Along with this, we are hoping to upgrade to SQL Server 2005 within
a year after the hardware upgrade.
The only reason we are currently running SQL Server 2000 Enterprise is
because our server has over 2 GB of memory. In 2005, the 2 GB memory
limitation has been removed for the Standard Edition so we are moving over to
the Standard Edition when we upgrade in a year.
So currently, we are looking to add two more SQL Server 2000 Ent licenses
for the addtional CPUs. Can I buy two SQL Server Standard licenses and use
the downgrade rights to temporarily run 2000 Enterprise?
"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
The best thing you can do is contact a Microsoft licensing specialist:
http://www.microsoft.com/licensing/index/worldwide.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
I never got an answer to taht when I asked a similar question, directly to
Microsoft.
BUT, they are the only ones who can really answer that.
Any advice given here, unless by an official MS person is worth what you're
paying for it.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per proc,
retail). Sounds unlikely.
David
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ej3Tc2RXHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
> "licensing_confusion" <licensing_confusion@.discussions.microsoft.com>
> wrote in message
> news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
> retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per
> proc, retail). Sounds unlikely.
> David
I think that's his question, which is similar to the case we had. 2
node-cluster with 8 gig of ram with 2 CPUs each.
Wanted to upgrade to 4 CPUs.
We're running SQL 2000, but plan on updating to SQL 2005.
Now, SQL 2005 Standard works on that config, but 2000 requires Enterprise.
So kind of puts some folks in a bind. :-/

Complicated Licensing Question

We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
CPU box. Along with this, we are hoping to upgrade to SQL Server 2005 withi
n
a year after the hardware upgrade.
The only reason we are currently running SQL Server 2000 Enterprise is
because our server has over 2 GB of memory. In 2005, the 2 GB memory
limitation has been removed for the Standard Edition so we are moving over t
o
the Standard Edition when we upgrade in a year.
So currently, we are looking to add two more SQL Server 2000 Ent licenses
for the addtional CPUs. Can I buy two SQL Server Standard licenses and use
the downgrade rights to temporarily run 2000 Enterprise?"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
The best thing you can do is contact a Microsoft licensing specialist:
http://www.microsoft.com/licensing/index/worldwide.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
I never got an answer to taht when I asked a similar question, directly to
Microsoft.
BUT, they are the only ones who can really answer that.
Any advice given here, unless by an official MS person is worth what you're
paying for it.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per proc,
retail). Sounds unlikely.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ej3Tc2RXHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
> "licensing_confusion" <licensing_confusion@.discussions.microsoft.com>
> wrote in message
> news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
> retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per
> proc, retail). Sounds unlikely.
> David
I think that's his question, which is similar to the case we had. 2
node-cluster with 8 gig of ram with 2 CPUs each.
Wanted to upgrade to 4 CPUs.
We're running SQL 2000, but plan on updating to SQL 2005.
Now, SQL 2005 Standard works on that config, but 2000 requires Enterprise.
So kind of puts some folks in a bind. :-/

Complicated Licensing Question

We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
CPU box. Along with this, we are hoping to upgrade to SQL Server 2005 within
a year after the hardware upgrade.
The only reason we are currently running SQL Server 2000 Enterprise is
because our server has over 2 GB of memory. In 2005, the 2 GB memory
limitation has been removed for the Standard Edition so we are moving over to
the Standard Edition when we upgrade in a year.
So currently, we are looking to add two more SQL Server 2000 Ent licenses
for the addtional CPUs. Can I buy two SQL Server Standard licenses and use
the downgrade rights to temporarily run 2000 Enterprise?"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
The best thing you can do is contact a Microsoft licensing specialist:
http://www.microsoft.com/licensing/index/worldwide.mspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
I never got an answer to taht when I asked a similar question, directly to
Microsoft.
BUT, they are the only ones who can really answer that.
Any advice given here, unless by an official MS person is worth what you're
paying for it.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per proc,
retail). Sounds unlikely.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ej3Tc2RXHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
> "licensing_confusion" <licensing_confusion@.discussions.microsoft.com>
> wrote in message
> news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
>> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a
>> 4
>> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
>> within
>> a year after the hardware upgrade.
>> The only reason we are currently running SQL Server 2000 Enterprise is
>> because our server has over 2 GB of memory. In 2005, the 2 GB memory
>> limitation has been removed for the Standard Edition so we are moving
>> over to
>> the Standard Edition when we upgrade in a year.
>> So currently, we are looking to add two more SQL Server 2000 Ent licenses
>> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
>> use
>> the downgrade rights to temporarily run 2000 Enterprise?
> So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
> retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per
> proc, retail). Sounds unlikely.
> David
I think that's his question, which is similar to the case we had. 2
node-cluster with 8 gig of ram with 2 CPUs each.
Wanted to upgrade to 4 CPUs.
We're running SQL 2000, but plan on updating to SQL 2005.
Now, SQL 2005 Standard works on that config, but 2000 requires Enterprise.
So kind of puts some folks in a bind. :-/