Insert Variables to a table.

Hi all. New to MS SQL, so this is probably dumb.
I've looked at all kinds of examples on the web and just can't seem to get
it.
I am trying to insert into a column (in an SQL2K) table a value from a
variable.
Here is an example of what I'm coding in Query Analyzer:
<< Code Start>>
declare @.RecNo as int
select count(Recno) from Help as RecNO
print @.Recno
Code Ends>>
UP TO HERE, this works fine and actually prints the number 1012 (which are
the correct number of records in the table).
OtherFile is just a name I'm using as an example.
<< Code Starts
insert into OtherFile (recno)
values (@.RecNo)
Code End>>
When I execute this, I get the message "Server: Msg 137, Level 15, State 2,
Line 2
Must declare the variable '@.RecNo'.
I thought I had declared it as I can print it, but I can't insert it into
anything.
What the heck am I doing wrong?
Thanks to all.Variables have a scope of one batch. Thus, the variable evaporated before
it got to you INSERT statement.
BTW, the first batch would have done the SELECT, but the PRINT should have
given you NULL, since the variable was never assigned. Therefore, run the
following in its entirety:
declare @.RecNo as int
select @.RecNo = count(Recno) from Help
print @.Recno
insert into OtherFile (recno)
values (@.RecNo)
go
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
Hi all. New to MS SQL, so this is probably dumb.
I've looked at all kinds of examples on the web and just can't seem to get
it.
I am trying to insert into a column (in an SQL2K) table a value from a
variable.
Here is an example of what I'm coding in Query Analyzer:
<< Code Start>>
declare @.RecNo as int
select count(Recno) from Help as RecNO
print @.Recno
Code Ends>>
UP TO HERE, this works fine and actually prints the number 1012 (which are
the correct number of records in the table).
OtherFile is just a name I'm using as an example.
<< Code Starts
insert into OtherFile (recno)
values (@.RecNo)
Code End>>
When I execute this, I get the message "Server: Msg 137, Level 15, State 2,
Line 2
Must declare the variable '@.RecNo'.
I thought I had declared it as I can print it, but I can't insert it into
anything.
What the heck am I doing wrong?
Thanks to all.|||Thanks Tom, that did it.
Also, I exited Query Analyzer, relaunched and ran the first part of my
script. It now returned 1013, so the insert worked.
I'm not sure why the Print displayed 1012 orignally. Maybe a ghost?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ODLfw6NGIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Variables have a scope of one batch. Thus, the variable evaporated before
> it got to you INSERT statement.
> BTW, the first batch would have done the SELECT, but the PRINT should have
> given you NULL, since the variable was never assigned. Therefore, run the
> following in its entirety:
> declare @.RecNo as int
> select @.RecNo = count(Recno) from Help
> print @.Recno
> insert into OtherFile (recno)
> values (@.RecNo)
> go
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
> news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Hi all. New to MS SQL, so this is probably dumb.
> I've looked at all kinds of examples on the web and just can't seem to get
> it.
> I am trying to insert into a column (in an SQL2K) table a value from a
> variable.
> Here is an example of what I'm coding in Query Analyzer:
> << Code Start>>
> declare @.RecNo as int
> select count(Recno) from Help as RecNO
> print @.Recno
> Code Ends>>
> UP TO HERE, this works fine and actually prints the number 1012 (which are
> the correct number of records in the table).
> OtherFile is just a name I'm using as an example.
> << Code Starts
> insert into OtherFile (recno)
> values (@.RecNo)
> Code End>>
> When I execute this, I get the message "Server: Msg 137, Level 15, State
> 2,
> Line 2
> Must declare the variable '@.RecNo'.
> I thought I had declared it as I can print it, but I can't insert it into
> anything.
> What the heck am I doing wrong?
> Thanks to all.
>
>|||There's difference in the output between a PRINT and a SELECT. Your
original script would have output one row with the correct number. If you
click on the Messages tab, that's where you see the output of any print
statements, assuming output in a grid. Oh, and I tried it and I got no
output in the Messages tab when the PRINT statement was printing a variable
that had not been assigned. I had thought it would have printed a NULL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
news:OM%23ChAOGIHA.3360@.TK2MSFTNGP04.phx.gbl...
Thanks Tom, that did it.
Also, I exited Query Analyzer, relaunched and ran the first part of my
script. It now returned 1013, so the insert worked.
I'm not sure why the Print displayed 1012 orignally. Maybe a ghost?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ODLfw6NGIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Variables have a scope of one batch. Thus, the variable evaporated before
> it got to you INSERT statement.
> BTW, the first batch would have done the SELECT, but the PRINT should have
> given you NULL, since the variable was never assigned. Therefore, run the
> following in its entirety:
> declare @.RecNo as int
> select @.RecNo = count(Recno) from Help
> print @.Recno
> insert into OtherFile (recno)
> values (@.RecNo)
> go
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
> news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Hi all. New to MS SQL, so this is probably dumb.
> I've looked at all kinds of examples on the web and just can't seem to get
> it.
> I am trying to insert into a column (in an SQL2K) table a value from a
> variable.
> Here is an example of what I'm coding in Query Analyzer:
> << Code Start>>
> declare @.RecNo as int
> select count(Recno) from Help as RecNO
> print @.Recno
> Code Ends>>
> UP TO HERE, this works fine and actually prints the number 1012 (which are
> the correct number of records in the table).
> OtherFile is just a name I'm using as an example.
> << Code Starts
> insert into OtherFile (recno)
> values (@.RecNo)
> Code End>>
> When I execute this, I get the message "Server: Msg 137, Level 15, State
> 2,
> Line 2
> Must declare the variable '@.RecNo'.
> I thought I had declared it as I can print it, but I can't insert it into
> anything.
> What the heck am I doing wrong?
> Thanks to all.
>
>