Create table with one first column then copy all columns from another table

Question

Is there a way to do what the title says without having to use something like this

Insert into TableB
(Col1, Col2 ,Col3)
Select Col1, Col2 ,Col3 from TableA

TableA has over 40 columns, so this is tedious.

TableB will end up with one column that is manually created and is the first column, then all other column names and data structures will be copied from TableA.

To clarify, TableA will not be needed after this operation. the ultimate goal is to get a new column at the beginning of TableA. I thought the only way to do this was to create a new table that has the new column and the data from tabelA, but any solution that will accomplish the task is fine.

The tedium might be a suggestion that there's a better way. Perhaps a [view](https://www.w3schools.com/SQL/sql_view.asp)? Duplicating data often enough that writing out columns gets tedious is generally a bad idea. Could you tell us what you're trying to accomplish? — Schwern, Mar 29 '21 at 19:05
Due to the way we have triggers set up, I need to insert a column into a table, but it has to be at the start of the table, not at the end. That is the ultimate objective. I was thinking of creating a new table with the new column and then copying data from the old table. But if there is another method that will work, it is all the same to me then — kravb, Mar 29 '21 at 19:10
Oh no, queries that assume column ordering. :( Try to get that query fixed, and yes, a view is a much better solution. — Schwern, Mar 29 '21 at 19:20
I appreciate the input and agree with you. But I haven't been tasked with optimizing what other's put in place. Just with inserting a column at the beginning of a table. I am not about to ruin my week and start a battle on how other DBA should do their job. Is what I am asking for possible? — kravb, Mar 29 '21 at 19:27
You now have all the options. And feel free to point your DBAs here. Tsk tsk. ;) — Schwern, Mar 29 '21 at 19:29

Schwern · Answer 1 · 2021-03-29T19:32:58.513

You can do that by giving the new column an alias, then wildcard the rest.

insert into TableB
select
  (some expression) as newColumn, *
from TableA

You can also create the table this way.

create table TableB as
select
  (some expression) as newColumn, *
from TableA

TableB will end up with one column that is manually created and is the first column, then all other column names and data structures will be copied from TableA.

Due to the way we have triggers set up, I need to insert a column into a table, but it has to be at the start of the table, not at the end. That is the ultimate objective.

Duplicating data is to be avoided. Consider a view instead. It looks like a table, but it doesn't copy anything. It's perfect for maintaining compatibility with old or bad queries.

create view tableB as
select
  (some expression) as newColumnName, *
from tableA

Alternatively to "reorder" the table: create the new table, drop the old table, and rename the new table. See this answer for more.

create table TableB as
select
  (some expression) as newColumn, *
from TableA;

drop table tableA;

alter table tableB rename to tableA;

Meanwhile, any queries which rely on column order should be fixed.

Create table with one first column then copy all columns from another table

1 Answers1