Temporary Table Operations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Temporary Table Operations

Author

Topic

pgm575
Starting Member

1 Post

Posted - 2010-11-11 : 16:14:06

So I have created a temporary table using the code shown below, and the table correctly displays all the information I want to see-

CREATE TABLE #Test (
part_number text,
point_name text,
position_tol float)

INSERT INTO #Test (part_number, point_name, position_tol)
SELECT tpf.part_number, fpl.point_name, tpf.position_tolerance FROM true_position_features tpf
INNER JOIN feature_point_list fpl
ON tpf.part_number = fpl.part_number AND tpf.feature_name = fpl.feature_name

Unfortunately, however, all I can seem to do with this table is

SELECT * FROM #Test

or select individual columns; what I have been trying to do is use something like

SELECT COUNT(DISTINCT point_name) FROM #Test

but I keep receiving the error "Operand data type text is invalid for count operator."

I have used COUNT with text columns before, so I don't what is going on. Anyone have an idea?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-11 : 17:43:38

It's the DISTINCT that's the problem.

Why Text? Are the part name and point_name really likely to be between 8000 and 2 000 000 000 characters long? If not, use appropriate data types.

In addition, TEXT is deprecated in 2008, it should not be used any longer. Varchar(max) is the replacement.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources