January 15, 2013 at 6:32 am
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
January 15, 2013 at 6:42 am
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
January 15, 2013 at 7:15 am
Thanks so much for the excellent and comprehensive answers to my question 🙂
I've never used MERGE, but it appears as if it's something to look into.
January 15, 2013 at 9:53 am
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.
January 15, 2013 at 9:57 am
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
January 15, 2013 at 1:27 pm
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.
January 15, 2013 at 2:20 pm
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
January 15, 2013 at 2:25 pm
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.
January 15, 2013 at 3:30 pm
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.
January 16, 2013 at 6:49 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply