2

I wish to make a new table with some data from the old, but also with new empty columns I can edit.

I would start with:

CREATE TABLE new_table AS SELECT ID, title, summary FROM old_table;

and then alter the table with new columns:

ALTER TABLE new_table ADD note datatype;

But I need my new empty column to be between title & summary, and not 'in the end' as ALTER gives me.

I would like to combine CREATE TABLE and CREATE TABLE AS SELECT - but is that possible?

Karl Kristiansen
  • 117
  • 9
  • Columns do not have an order. It is meaningless to say that the new columns are "between" title & summary. – John Saunders May 05 '15 at 14:57
  • Why does the column ordinal matter? The order of the columns is irrelevant in SQL. But to answer your underlying question, just do a `CREATE TABLE` first, then populate it. – Siyual May 05 '15 at 14:57
  • This sounds like a duplicate of http://stackoverflow.com/questions/5327545/adding-column-between-two-other-columns-in-sql-server – Mattgb May 05 '15 at 14:59
  • Just a point in light of "columns do not have an order". .. their order *can* matter in some side cases - in general it's probably not going to make a lot of difference, but it can: http://stackoverflow.com/questions/894522/is-there-any-reason-to-worry-about-the-column-order-in-a-table – Ditto May 05 '15 at 15:04
  • The order is important, because we can't manually edit the content if we do a view or a query (we're using Valentina Studio), and we need to do a content analysis where we write memos and content-codes inside the data. I know there must be other ways around this - but we're running out of time for our thesis. – Karl Kristiansen May 05 '15 at 15:08
  • @KarlKristiansen If that is the case, then you should do a `CREATE TABLE` with all of the columns you will need, in the order you desire, then do an `INSERT INTO ... SELECT` with the columns from before. – Siyual May 05 '15 at 15:10

1 Answers1

7

What about: CREATE TABLE new_table AS SELECT ID, title, '' AS note, summary FROM old_table;

If you wanted to have a specific datatype, you could try CAST. For example, CAST(1 as INTEGER) AS note

Sam Cohen-Devries
  • 2,025
  • 2
  • 18
  • 35