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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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;
SELECT pi, Pres5 as MOPI , MAX(Pres_Start) as max_date
INTO T_1213_SAYDate
FROM T_1213_OnlySSN
GROUP BY pi, Pres5;
Hi Sarah
One simple answer is if the table already exists, delete it before repopulating it.
Spell out for us what you tried, and the resulting error message. Should work.
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. Spell out for us what you tried, and the resulting error message. Should work.
ASKER
Entered reply into wrong question.
ASKER
I ran it again and it worked, so very happy :-) Thanks.
ASKER
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