SQL Temp Tables Dynamic Columns

I am creating a temp table with one column.

create table #temp(JobID int);

Then I dynamically add more columns to the temp table.

SET     @script = 'ALTER TABLE #temp ADD '

Select @script = @script + QUOTENAME(job) + ' INT DEFAULT 0 WITH VALUES,'
from Jobs

Now my table looks like this.

JobID  Station1 Station2 Station3 Station4
3000         0              0              0             0
3002         0              0              0             0
3152         0              0              0             0
4125         0              0              0             0

Now I need to Update the dynamic columns (Station1 Station2 Station3 Station4)
for each JobID to get results like this.

JobID  Station1 Station2 Station3 Station4
3000         52             48            55           60
3002         62             59            63           55
3152         79             71            58           60
4125         21             28            34          19

Any help would be helpful.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi triple,

I've seen too many applications written this way and they always become maintenance nightmares.

Much better would be to design the table with some flexibility.  Updating and querying becomes a piece of cake.

  JobId         integer,
  location    nvarchar(20),
  count        integer

Your sample data:

JobID  Station1 Station2 Station3 Station4
3000         52             48            55           60
3002         62             59            63           55
3152         79             71            58           60
4125         21             28            34          19

is stored as

JobId location count
3000 Station1 52
3000 Station2 48
3000 Station3 55
3000 Station4 60
3002 Station1 62
3002 Station2 59

That's much more flexible and still gives you the same functionality that you described.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I agree with KDO. I also think we've suggested the same data structure before and recommended the use of the PIVOT command instead of the dynamically created temp table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.