Copying a SQL Server table and adding and rearranging columns

Question

I know that if I want to make a copy of a SQL Server table, I can write a query akin to this:

SELECT * 
INTO NewTable
FROM OldTable

But what if I wanted to take the contents of OldTable that may look like this:

| Column1 | Column2 | Column3 |
|---------|---------|---------|
| 1       | 2       | 3       |
| 4       | 5       | 6       |
| 7       | 8       | 9       |

and make a copy of that table but have the new table look like this:

| Column1   | Column3   | Column2   | Column4   | Column5   |
|---------  |---------  |---------  |---------  |---------  |
| 1         | 3         | 2         | 10        | 11        |
| 4         | 6         | 5         | 12        | 13        |
| 7         | 9         | 8         | 14        | 15        |

So now I've swapped Columns 2 and 3 and added Column 4 and Column 5. I don't need to have a query that will add that data to the columns, just the bare columns.

where are the numbers in `column4` and `column5` coming from? — Vamsi Prabhala, Nov 20 '15 at 18:02

score 1 · Answer 1 · answered Nov 20 '15 at 18:03

1

It's a matter of modifying your select statement. SELECT * takes only the columns from the source table, in their order. You want something different - so SELECT it.

SELECT * INTO NewTable
  FROM OldTable

->

SELECT Col1, col3, col2, ' ' AS col4, ' ' AS col5
  INTO NewTable
  FROM OldTable

This gives you very little flexibility as far as how the table's columns are specced and indices and such - so it's probably a bad idea, probably better to do this another way (properly CREATE TABLE), but if you need quick and dirty, I suppose...

answered Nov 20 '15 at 18:03

Joe

62,789
6
49
67

Obviously select NULL as col4/col5 if you want them numeric or specify them appropriately (within the limits of a SELECT specification). – Joe Nov 20 '15 at 18:03
How is `CREATE TABLE` better than this way? This gets all the old data straight into the table. – JohnN Nov 20 '15 at 18:28
`CREATE TABLE` has more options than this in terms of indices and keys and such. As far as I know, anyway... – Joe Nov 20 '15 at 18:29

score 1 · Answer 2 · answered Nov 20 '15 at 18:05

You can just name the columns:

Select
[Column1], [Column3], [Column2], Cast(null as bigint) as [Column4], 0 as [Column5]
Into CopyTable
From YourTable

Just like any query, it is always preferable to use the Column names and avoid using *.

You can then add any value as [ColumnX] in the select. You can use a cast to get the type you want in the new table.

Copying a SQL Server table and adding and rearranging columns

2 Answers2