Column Extraction

Question

Post reply

Column Extraction

Girish Sharm

SSC Journeyman

Points: 82
More actions
August 25, 2007 at 11:42 pm

#153776

There are around 40 columns in my table. I want to get all the table data excluding a particular column; without mentioning all the column names. I used following code :
declare @sql varchar(2000)
select @sql=''
Select @sql=@sql+case when ordinal_position=1 then '' else ',' end +'['+column_name+']' from information_schema.columns
where table_name='COMTR' and column_name<>'GROUP'
order by ordinal_position
SELECT @sql='SELECT '+@SQL+' FROM COMTR'
--EXEC @sql
--PRINT @sql
but, if print the @sql it print the exacutable query; but when i try to execute it; it shows error "Server: Msg 203, Level 16, State 2, Line 7".
Please guide me, how can i execute this string.
Thanks & Regards

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

Really?

Post the "PRINTED" query here.

N 56°04'39.16"
E 12°55'05.25"

David McFarland SSChampion Points: 11815 More actions · Answer 2

Put parentheses around the @sql in the EXEC statement.

Girish Sharm SSC Journeyman Points: 82 More actions · Answer 3

I gave EXEC {@SQL} but still showing error : [Microsoft][ODBC SQL Server Driver]Syntax error or access violation. But if i print the @sql string it shows executable query i.e.

SELECT [NEWCODE],[DTCD],[DIST],[NAME],[GP],[CAPACITY06],[CENT2004],[CENT2005],[CENT2006],[FO_CODE],[LINE1],[LINE2],[LINE3],[LINE4],[LINE5],[LINE6],[DISTNAME],[HDISTNAME],[ADD1],[ADD2],[ADD3],[ADD4],[RU],[TYPE],[PLACE],[HPLACE],[SCHLEVEL],[AA],[AB],[AC],[AD],[AE],[AF],[AG],[AH],[AI],[AJ],[AK],[AL] FROM COMTR

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

{} are not parenthesis...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Girish Sharm SSC Journeyman Points: 82 More actions · Answer 5

then plz. tell me how can i execute this query ? if {} are not parenthesis; then what are parenthesis...

Thanks & Regards

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

{} are curly brackets

EXEC (@SQL)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Since the use of dynamic SQL seems to be warranted only by not wanting 'to type too much', do also read this, so you're aware of the cosequences of introducing dynamic SQL.

http://www.sommarskog.se/dynamic_sql.html

/Kenneth

philcart SSC-Forever Points: 47797 More actions · Answer 8

Actually to be precise { and } are braces, [ and ] are brackets and ( and ) are parentheses.

--------------------
Colt 45 - the original point and click interface