August 24, 2017 at 3:19 am
Hello , i am writing simple query to create and insert values into table variable, table is being created without any error but it showing following error when i am trying to insert values in it or selecting the table data. Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@tbBooks".
DECLARE @tbBooks TABLE
(
BookId INT NOT NULL IDENTITY(1,1),
BookName VARCHAR(100),
Author VARCHAR(100),
Publisher VARCHAR(100),
Price DECIMAL(10,2)
)
insert into @tbBooks(Bookname,Author, Publisher, price)values('xyz','yzx','abc', 1200)
August 24, 2017 at 3:37 am
Your code runs fine.
What version of SQL are you running?
Edit: I see what you're doing.
Table variables are batch-scoped, like normal variables. You can't create it in one batch and insert or query it in a later batch of statements, it's automatically dropped at the end of a batch. So if you highlight the declare and run it, the table is created and then automatically dropped at the end of the batch. If you then try to run the insert as a separate batch, the variable's not there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2017 at 3:47 am
GilaMonster - Thursday, August 24, 2017 3:37 AMYour code runs fine.What version of SQL are you running?
Are you running the declare first and the insert separately?
Thanks i got my query running just by analyzing your words "Are you running the declare first and the insert separately" . My query my works fine when i run these three together (i mean create table variable first , insert and then select statement).... I want to ask you if there there is way to run them separately ? like selecting the data without running the create statement like we do in normal SQL table ?
August 24, 2017 at 4:23 am
sunilchand - Thursday, August 24, 2017 3:47 AMThanks i got my query running just by analyzing your words "Are you running the declare first and the insert separately" . My query my works fine when i run these three together (i mean create table variable first , insert and then select statement).... I want to ask you if there there is way to run them separately ? like selecting the data without running the create statement like we do in normal SQL table ?
Variables and any interaction need to be in the same batch. If you enter a new batch, the variable doesn't exist anymore, as you're out of scope. For example:USE Sandbox;
GO
--Declare Variables and create some values
DECLARE @Variable int;
DECLARE @Table TABLE (Number int);
SET @Variable = 0;
INSERT INTO @Table
VALUES (1),(2);
--Return the contents, this will work.
SELECT @Variable;
SELECT *
FROM @Table;
GO
--New batch, so we need to delcare the variables again:
DECLARE @Variable int;
DECLARE @Table TABLE (Number int);
--Firstly, you'll notice these are empty, they don't have the values from the previous batch
SELECT @Variable;
SELECT *
FROM @Table;
--Create some values
SET @Variable = 0;
INSERT INTO @Table
VALUES (1),(2);
GO
--New Batch, now we'll try to return the values
--This will error.
SELECT @Variable;
SELECT *
FROM @Table;
GO
If you want to be able to interact in other areas, you either want to use a (global) Temporary table (CREATE TABLE #Temp (Number int); (double # for a global)) or, a staging table in one of your databases. Either way, you'll need to ensure you drop the table once you're done with it, or check for it's existence first before you try to create it. Which you use also depends on if you're using it in the same session (as Gail points out in her post below) or a separate one entirely.
For example, if we use the above code, just focusing on the TABLE variable, and change it to a temporary table:USE Sandbox;
GO
--Declare Variables and create some values
CREATE TABLE #Table (Number int);
INSERT INTO #Table
VALUES (1),(2);
--Return the contents, this will work.
SELECT *
FROM #Table;
GO
--New Batch, so trying to Create the table again now will fail
CREATE TABLE #Table (Number int);
--None of below (in this batch) will run, as the above statement failed
SELECT *
FROM #Table;
SET @Variable = 0;
INSERT INTO #Table
VALUES (3),(4);
GO
--New Batch, now we'll try to return the values
--This will return 1 and 2 only, as the previous batch failed (due to the table existing)
SELECT *
FROM #Table;
GO
--Clean up
DROP TABLE #Table;
Edit: Clarification on (Global) Temp Tables and Staging and when to use.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 24, 2017 at 4:42 am
sunilchand - Thursday, August 24, 2017 3:47 AMI want to ask you if there there is way to run them separately ? like selecting the data without running the create statement like we do in normal SQL table ?
No. Variables, table or normal ones, are scoped to the batch.
If you want a table that persists past the end of the batch, use a temp table. They're session-scoped.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2017 at 4:54 am
GilaMonster - Thursday, August 24, 2017 4:42 AMsunilchand - Thursday, August 24, 2017 3:47 AMI want to ask you if there there is way to run them separately ? like selecting the data without running the create statement like we do in normal SQL table ?No. Variables, table or normal ones, are scoped to the batch.
If you want a table that persists past the end of the batch, use a temp table. They're session-scoped.
Ahhh ..ok .. i got it ! Thanks for sharing useful insight it was indeed very helpful !
August 24, 2017 at 5:28 am
Thom A - Thursday, August 24, 2017 4:23 AMsunilchand - Thursday, August 24, 2017 3:47 AMThanks i got my query running just by analyzing your words "Are you running the declare first and the insert separately" . My query my works fine when i run these three together (i mean create table variable first , insert and then select statement).... I want to ask you if there there is way to run them separately ? like selecting the data without running the create statement like we do in normal SQL table ?Variables and any interaction need to be in the same batch. If you enter a new batch, the variable doesn't exist anymore, as you're out of scope. For example:
USE Sandbox;
GO
--Declare Variables and create some values
DECLARE @Variable int;
DECLARE @Table TABLE (Number int);SET @Variable = 0;
INSERT INTO @Table
VALUES (1),(2);--Return the contents, this will work.
SELECT @Variable;
SELECT *
FROM @Table;
GO
--New batch, so we need to delcare the variables again:
DECLARE @Variable int;
DECLARE @Table TABLE (Number int);
--Firstly, you'll notice these are empty, they don't have the values from the previous batch
SELECT @Variable;
SELECT *
FROM @Table;--Create some values
SET @Variable = 0;
INSERT INTO @Table
VALUES (1),(2);
GO
--New Batch, now we'll try to return the values
--This will error.
SELECT @Variable;
SELECT *
FROM @Table;
GO
If you want to be able to interact in other areas, you either want to use a (global) Temporary table (CREATE TABLE #Temp (Number int); (double # for a global)) or, a staging table in one of your databases. Either way, you'll need to ensure you drop the table once you're done with it, or check for it's existence first before you try to create it. Which you use also depends on if you're using it in the same session (as Gail points out in her post below) or a separate one entirely.For example, if we use the above code, just focusing on the TABLE variable, and change it to a temporary table:
USE Sandbox;
GO
--Declare Variables and create some values
CREATE TABLE #Table (Number int);
INSERT INTO #Table
VALUES (1),(2);--Return the contents, this will work.
SELECT *
FROM #Table;
GO
--New Batch, so trying to Create the table again now will fail
CREATE TABLE #Table (Number int);
--None of below (in this batch) will run, as the above statement failed
SELECT *
FROM #Table;SET @Variable = 0;
INSERT INTO #Table
VALUES (3),(4);
GO
--New Batch, now we'll try to return the values
--This will return 1 and 2 only, as the previous batch failed (due to the table existing)
SELECT *
FROM #Table;
GO
--Clean up
DROP TABLE #Table;Edit: Clarification on (Global) Temp Tables and Staging and when to use.
Yeah ..your example has cleared many doubts which were remaining ..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply