SELECT INTO with Column Name Change

Hi

I'm using the following to create a new table (code provided by an Expert):

SELECT pi, Pres5, MAX(Pres_Start) as max_date
INTO T_1213_SAYDate
FROM T_1213_OnlySSN
GROUP BY pi, Pres5;

but would like the change the column name Pres5 to MOPI in the new table.  I'm not sure how to do this.  I've tried changing the column name after the table has been created, using sp_RENAME but can't get that working and it would be much neater to change it when I create the table in the first place.

I'm awarding 500 points.

Thanks
Sarah
ScuzzyJoAsked:
Who is Participating?
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.

Bill BachPresident and Btrieve GuruCommented:
Use the alias, in the same way as the other fields:

SELECT pi, Pres5 as MOPI , MAX(Pres_Start) as max_date
INTO T_1213_SAYDate
FROM T_1213_OnlySSN
GROUP BY pi, Pres5;

You may have to also change the last line to "GROUP BY pi, MOPI"
0

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
ScuzzyJoAuthor Commented:
Hi Bill

Thanks for replying.  I tried that (with both last line possibilities) but I get an error:

Msg 207, Level 16, State 1, Line 29
Invalid column name 'MOPI'.

Thanks
Sarah
0
Bill BachPresident and Btrieve GuruCommented:
Line 29?  Are you editing the query to make it easier to work with?  

I did a simple test in another database like this:

select First_Name, Last_Name as LN, Count(*) as Number
into MyTestFile
from "Person"
group by First_Name, LN;

It works just fine.  (In fact, it worked for me with both LN and "Last_Name" in the GROUP BY clause.)

Clearly, the line number is wrong.  Is there something else to the statement that you haven't posted?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Aneesh RetnakaranDatabase AdministratorCommented:
The original post should work

SELECT pi, Pres5 as MOPI , MAX(Pres_Start) as max_date
INTO T_1213_SAYDate
FROM T_1213_OnlySSN
GROUP BY pi, Pres5;
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi Sarah

One simple answer is if the table already exists, delete it before repopulating it.
IF OBJECT_ID('MyTestFile') IS NOT NULL
   DROP TABLE MyTestFile
GO

-- Your SELECT.. INTO, with changed column names, goes here. 

Open in new window

>I've tried changing the column name after the table has been created, using sp_RENAME but can't get that working
Spell out for us what you tried, and the resulting error message.  Should work.
0
ScuzzyJoAuthor Commented:
Entered reply into wrong question.
0
ScuzzyJoAuthor Commented:
I ran it again and it worked, so very happy :-)  Thanks.
0
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

From novice to tech pro — start learning today.