noob syntax question

What am I doing wrong?:
DELETE FROM tblmainacs2 WHERE tblmainacs2.IDnum = Meter.id

I get this message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Meter.id" could not be bound.

Is Meter a table? If so you do contain an explicit reference to it in the DELETE statement in the FROM clause or part of subquery for example. You can write it like below assuming you want to perform a join:

delete from tblmainacs2

where IDnum in (select id from Meter)

|||Thank you. That query did exaclty what I wanted. Could someone show me some different queries that do exactly the same thing, just for learning purposes. Thanks in advancesql

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure[vbcol=seagreen]
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>
90[vbcol=seagreen]
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
>
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don
>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
structure
>> address, char,30
>> thanks,
>> Don
>>
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don
>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>>
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>>--Original Message--
>>Just to be clear you can never have a non-logged
> operation in SQL Server.
>>You can get what is called a Minimally Logged operation
> by doing a Select
>>Into, BCP or Bulk Insert when the proper conditions are
> set. This still
>>logs entries to the transaction log but are minimal
> compared to the fully
>>logged operations. In the case of the Select Into you
> need to be in the
>>Bulk Logged or Simple recovery mode for this to occur.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>><news.optonline.net> wrote in message
>>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
> 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
> structure
>> address, char,30
>> thanks,
>> Don
>>
>>
>>.|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:
> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> >--Original Message--
> >To do this I would create a view on the new table
> >create view vw_newtable
> >as
> >select * from newtable
> >
> >than bcp the data out
> >
> >master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
> N'
> >then bcp in
> >master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
> N'
> >
> >Note there must be no indexes on the dest table.
> >For 90 million recs I would consider doing the above but
> maybe 10 million
> >recs at a time. Use a query to extract the data with
> queryout on an indexed
> >column (or you can use views in the same way)
> >
> >
> >
> >
> >
> >
> >"Don" wrote:
> >
> >> SQL 7.0
> >>
> >> I need to use nonlogged bcp to copy from a table with
> 90
> >> millin records to an empty table structure that's
> >> different than the source structure.
> >> Does someone know the syntax?
> >>
> >> old table example:
> >> name, char, 30
> >> address,char,30
> >>
> >> new table example:
> >> name,char,30
> >> id,decimal,10 this is NULL on the source file structure
> >> address, char,30
> >>
> >> thanks,
> >> Don
> >>
> >>
> >.
> >
>sql

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
Don
Use select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>
|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the[vbcol=seagreen]
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure
>
>.
>
|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed[vbcol=seagreen]
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
90
>.
>
|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure
|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

Non-equi Joins using <>

Can we replace the where clause operator of '<>' with a join syntax condition.

Could someone explain me the dynamics behind the following query:

select a.b, c.d from a inner join c on a.b <> c.d

This could effectively replace the NOT IN and EXCEPT operators

No.. <> wont work as you expected. You query is similar to the following query,

select * from a cross join cWhere a.b <> c.d

Since it is set opertation your query will guide you to make a cross join and it only filter those values which are present on both the tables.

You have to use NOT IN or NOT EXISTS on your query. Join will be compared on both tables row by row. The IN & EXISTS will be verified with the one table value with other tables rows of value.

|||

Since, I am avoiding the use of NOT IN & NOT EXISTS due to its obvious perofrmance degradations, I also wouldn't use a Cross Join (which I think for tables with more rows would be terribly slow). Hence, would I get a Join query to have a performance upgrade over them.

|||

Hi there,

NOT IN and NOT EXISTS are no the same when it comes to performance. NOT EXISTS is much better in terms of performance especially if you're checking for a specific value as the query would return a TRUE value if a match is found and therefore avoid further iterations.

|||

I tend to use NOT EXISTS in these situations to take advantage of the semi-joins. Also, beware that the EXCEPT operator is typically slower.

Here are some previous threads that discuss NOT IN versus NOT EXISTS:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726903&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087482&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=637335&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654087&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532892&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

Here is a thread that discusses NOT IN versus EXCEPT:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1021998&SiteID=1