Solved SQL Create table from table with Identity column

Posted on 2014-10-22
Last Modified: 2014-10-22

I have a number of tables in a SQL database that don't have Identity columns
I know that you can copy a table using the following statement
SELECT * INTO Table2 FROM Table1
What SQL code would I use to copy a table but add an extra identity column
at the beginning of the table?
Question by:murbro
  • 3
  • 3
LVL 48

Accepted Solution

Vitor Montalvão earned 500 total points
ID: 40396529
You can use ROWNUMBER to produce a new column with codes:
INTO Table2
FROM Table1

Open in new window


Author Comment

ID: 40396560
I don't quite understand Table1.ColumnName
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40396586
I don't know your table structure so you need to substitute "Table1.ColumnName" with a real column name from your table.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


Author Comment

ID: 40396608
If my column names are Col1,Col2,Col3 all of type nvarchar(40) can I select any of these columns?
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40396614
Using the '*' all your columns will be returned. The need for a column name it's only to order the rows so SQL Server can give them a number (an identity column for you). So you can keep the '*' and provide a column name to the ORDER BY clause (preferential a primary key column).

Author Closing Comment

ID: 40396620
Thanks very much Vitor. I worked it out

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSAS Store Forecasting data in the cube 1 20
Query for timesheet application 3 20
Groupbox Control ? 2 19
SQL Error when trigger fires in SQL Server 2008 R2 6 16
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question