I am building a temp table dynamically and while I need all columns at the end of my stored proc I need to do the following:
<SQL>
SELECT * FROM MyTempTable
</SQL>
However I need to ignore the first 2 columns in my select, the first column is a RowID identity field, is there a way that I can do a "SELECT *..." and also ignore the first 2 columns in the table?
The answer to your original question is no. It is not possible to write a syntax that selects all columns except for the first two.
Your best two options, therefore, depending on your requirements:
1. Do a SELECT *, getting all columns in your query results, but ignore the first two columns in your front end application.
2. While you are dynamically creating your columns, you also dynamically create a comma-separated list of your column names, so that you can use that list to generate your final SELECT statement instead of SELECT *
-Tab Alleman
Marked as answer by MyronCope on Feb 23, 2012 02:37 PM
The answer to your original question is no. It is not possible to write a syntax that selects all columns except for the first two.
Your best two options, therefore, depending on your requirements:
1. Do a SELECT *, getting all columns in your query results, but ignore the first two columns in your front end application.
2. While you are dynamically creating your columns, you also dynamically create a comma-separated list of your column names, so that you can use that list to generate your final SELECT statement instead of SELECT *
I pretty much do num 2 and realize now that the syntax that I use will be the same syntax that I use to dynamically create my column names.
MyronCope
Participant
1656 Points
1345 Posts
Ignore first 2 columns in SELECT
Feb 22, 2012 08:42 PM|LINK
using sql server 2005
I am building a temp table dynamically and while I need all columns at the end of my stored proc I need to do the following:
<SQL>
SELECT * FROM MyTempTable
</SQL>
However I need to ignore the first 2 columns in my select, the first column is a RowID identity field, is there a way that I can do a "SELECT *..." and also ignore the first 2 columns in the table?
thanks
MC
Shellymn
Contributor
2600 Points
484 Posts
Re: Ignore first 2 columns in SELECT
Feb 22, 2012 08:47 PM|LINK
I think you need to do a select by explicity specifying the column names you needed.
adamturner34
Contributor
3854 Points
963 Posts
Re: Ignore first 2 columns in SELECT
Feb 23, 2012 02:22 AM|LINK
Yes don't add the columns to the temp table in the first place...or drop them before the select.
In any event, it's bad practice to use the wildcard in the SELECT. Too many potential issues.
arifcse12
Contributor
4600 Points
755 Posts
Re: Ignore first 2 columns in SELECT
Feb 23, 2012 08:36 AM|LINK
The basic query structure: 'Select * from tableName'
here '*' means all columns. How can you get something else while you are asking for all ?
Just write the columns you need.
Regards
Please do not forget to click 'Mark as Answer' if this answer helps.
MyronCope
Participant
1656 Points
1345 Posts
Re: Ignore first 2 columns in SELECT
Feb 23, 2012 12:56 PM|LINK
ok but the point that I am making is that my table columns are created dynamically.
I assume that I can stored the column names in a string when I am creating them and select by that string but not sure of the syntax.
the other 2 columns are required for looping and other purposes but in the final select I do not want to show them
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Ignore first 2 columns in SELECT
Feb 23, 2012 01:46 PM|LINK
The answer to your original question is no. It is not possible to write a syntax that selects all columns except for the first two.
Your best two options, therefore, depending on your requirements:
1. Do a SELECT *, getting all columns in your query results, but ignore the first two columns in your front end application.
2. While you are dynamically creating your columns, you also dynamically create a comma-separated list of your column names, so that you can use that list to generate your final SELECT statement instead of SELECT *
MyronCope
Participant
1656 Points
1345 Posts
Re: Ignore first 2 columns in SELECT
Feb 23, 2012 02:37 PM|LINK
I pretty much do num 2 and realize now that the syntax that I use will be the same syntax that I use to dynamically create my column names.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Ignore first 2 columns in SELECT
Feb 23, 2012 02:49 PM|LINK
This sounds like a very bad database design. You do realize that there is a maximum size to a table row, right?