Avoid listing hundreds of columns

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

General SQL Server Forums

New to SQL Server Programming

Avoid listing hundreds of columns

Author

Topic

mcinvalek
Starting Member

5 Posts

Posted - 2014-06-03 : 18:00:09

i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length

Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again
SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below (which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

thanks for all advise in advance

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-03 : 18:17:55

Do one of the following:

1) Make new column:

SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END  AS ACCT1_CUSTOMER_ST_MANIPULATED
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

2) Copy the table, and run update afterwards:

SELECT *
INTO #A1
FROM STAGE.CUSTOMER_ACCTS;

UPDATE #A1
SET ACCT1_CUSTOMER_ST = 'DC'
WHERE ACCT1_CUSTOMER_ST = ' ';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources