Solved

VB.net SQL Create table from table with Identity column

Posted on 2014-10-22
6
251 Views
Last Modified: 2014-10-22
Hi

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?
0
Comment
Question by:murbro
  • 3
  • 3
6 Comments
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40396529
You can use ROWNUMBER to produce a new column with codes:
SELECT ROW_NUMBER() OVER(ORDER BY Table1.ColumnName) AS NewID, *
INTO Table2
FROM Table1

Open in new window

0
 

Author Comment

by:murbro
ID: 40396560
Hi
Thanks
I don't quite understand Table1.ColumnName
0
 
LVL 47

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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).
0
 

Author Closing Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

786 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