Solved

VB.net SQL Create table from table with Identity column

Posted on 2014-10-22
6
240 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 45

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 45

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 45

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
VB.NET HttpWebRequest 12 34
Test a query 23 19
sql query Help 12 34
SQL Query for Periods 3 0
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

14 Experts available now in Live!

Get 1:1 Help Now