Solved

VB.net SQL Create table from table with Identity column

Posted on 2014-10-22
6
249 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 46

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 46

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 46

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now