SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to qualify columns wthin temporary tables


Unable to qualify columns wthin temporary tables

Author
Message
raotor
raotor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 239
Hello,

It appears that i cannot qualify columns within temporary tables. The following example generates an error:

DECLARE @MyTable TABLE
(
COl1 INT,
COl2 INT
);

SELECT @MyTable.Col1 FROM @MyTable;

The same is true if I use the #MyTable version too

I need to use a temporary table within a stored procedure and want to do something like the following:

UPDATE @MyTable
SET Co2 = A,Cik2
FROM Table2 AS A
WHERE A.Col1 = @MyTable.Col1;

I need a temporary table within the stored procedure rather than a permanent one due to the fact that the SP might be executed concurrently by multiple users and I need to maintain local versions of the temporary table.

Any suggestions welcome.

Regards

Steve
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
You have to give a table variable an alias in order to do this kind of thing.

DECLARE @MyTable TABLE
(
COl1 INT,
COl2 INT
);

SELECT MT.Col1 FROM @MyTable AS MT;



UPDATE MT
SET Co2 = A.Cik2
FROM @MyTable AS MT
INNER JOIN Table2 AS A
WHERE A.Col1 = MT.Col1;



Even better yet, on the Update From, use Merge instead.

MERGE INTO @MyTable AS Tgt
USING (SELECT Cik2, Col1 FROM dbo.Table2) AS Src
ON Tgt.Col1 = Src.Col1
WHEN MATCHED THEN UPDATE
SET Col1 = Src.Col1;



Merge has a few advantages over Update From, in terms of ACID-compliance in the update. It's also ISO compliant, if that matters to you, where Update From is T-SQL proprietary. But the ACID properties, like Update From can have multiple, conflicting updates to the same row, while Merge can't, are what really matter.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
raotor
raotor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 239
Thanks so much for the excellent and comprehensive answers to my question Smile

I've never used MERGE, but it appears as if it's something to look into.
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 1019
Be careful in using @ type temporary tables.
The optimizer has no statistics on @ tables and assumes it has ONE row.
Even if you put a primary key on it, the PK only acts as a constraint.

If you have more than a handful of rows in the @ table, you may have performance issues.

You are better off using # temp table instead, if the table will contain more than a handful of rows.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
arnipetursson (1/15/2013)
Be careful in using @ type temporary tables.
The optimizer has no statistics on @ tables and assumes it has ONE row.
Even if you put a primary key on it, the PK only acts as a constraint.

If you have more than a handful of rows in the @ table, you may have performance issues.

You are better off using # temp table instead, if the table will contain more than a handful of rows.


Just to clarify, handful is approximately 1,000 rows. Depends on the data-sizes involved. Below that size, SQL Server will often ignore indexes, etc., anyway.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
raotor
raotor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 239
Thanks once more for pointing out the best uses for, and against using @ type table variables.

I do seem to recall reading somewhere that such table variables should be only used to store small record sets and in my case there's only 20 rows in the table variable.

I did also recall reading that the benefits of using @ type table variables were reduced overhead or something, but I can't be sure. I'm sure there's always a pro as well as a con.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
The only real benefit, in my experience (about 12 years now), to table variables over temp tables, is that an explicit rollback command won't roll back data changes in a table variable. Makes them absolutely great for recording things that you want to use in error-handling. You can put data in a table variable, then issue a rollback, then use the data in the table variable to insert into a log table, for example. If you just insert into the log table and then issue the rollback, you lose the log data. Rollback and then log is great.

Other than that, I pretty much stick with temp tables.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 1019
Another valid use case is of course as input parameter to a stored proc.

Again, if i have a substantial amount of data in the table variable input parameter,
I immediately move the data into an appropriately indexed # temp table.
raotor
raotor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 239
Thanks so much for the benefit of your experiences with table variables as opposed to temporary tables. It's just such insights that I find very helpful.

Thanks.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
arnipetursson (1/15/2013)

Another valid use case is of course as input parameter to a stored proc.

Again, if i have a substantial amount of data in the table variable input parameter,
I immediately move the data into an appropriately indexed # temp table.


Good point. Forgot about those, since they don't use them the place I currently work. Apparently, ColdFusion can't call those in its SQL modules. Or, at least, the version they're using can't. Maybe a later version can.

Table Value Parameters are very cool. Easy to pass datasets to from .NET applications.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search