Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to qualify columns wthin temporary tables Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 6:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, 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
Post #1407231
Posted Tuesday, January 15, 2013 6:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1407241
Posted Tuesday, January 15, 2013 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, Visits: 239
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.
Post #1407262
Posted Tuesday, January 15, 2013 9:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 282, Visits: 889
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.
Post #1407349
Posted Tuesday, January 15, 2013 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1407353
Posted Tuesday, January 15, 2013 1:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, 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.
Post #1407464
Posted Tuesday, January 15, 2013 2:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1407494
Posted Tuesday, January 15, 2013 2:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 282, Visits: 889

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.
Post #1407496
Posted Tuesday, January 15, 2013 3:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, 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.
Post #1407509
Posted Wednesday, January 16, 2013 6:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1408121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse