[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

VB.net SQL Create table from table with Identity column

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
Murray Brown
Asked:
Murray Brown
  • 3
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi
Thanks
I don't quite understand Table1.ColumnName
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know your table structure so you need to substitute "Table1.ColumnName" with a real column name from your table.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
If my column names are Col1,Col2,Col3 all of type nvarchar(40) can I select any of these columns?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much Vitor. I worked it out
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now