Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Create a table with identity selecting from view
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Loneliness
Starting Member

Italy
10 Posts

Posted - 09/27/2013 :  06:16:18  Show Profile  Reply with Quote
Hello, i need to create a table selecting from a view:

select *
into Mytable
from Myview

but i want Mytable to have an IDENTITY column that is not present in Myview...can i achieve this directly in the select statement without altering the table afterwards?

Tank you.

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 09/27/2013 :  08:08:03  Show Profile  Reply with Quote
You can. Example:
SELECT *,
IDENTITY(INT,1,1) As NewColumn
INTO MyTable
FROM MyView


See documentation here: http://technet.microsoft.com/en-us/library/ms189838.aspx

Edited by - James K on 09/27/2013 08:08:35
Go to Top of Page

Loneliness
Starting Member

Italy
10 Posts

Posted - 09/27/2013 :  08:25:23  Show Profile  Reply with Quote
Thank you very much James.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 09/28/2013 :  04:36:14  Show Profile  Reply with Quote
if you want identity values to be generated based on order of particular column make sure you use something like



SELECT *,
ROW_NUMBER() OVER (ORDER BY YourColumn ) As NewColumn
INTO MyTable
FROM MyView



SELECT INTO with IDENTITY will not guarantee the order in which identity values are generated

http://support.microsoft.com/kb/273586

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Loneliness
Starting Member

Italy
10 Posts

Posted - 10/02/2013 :  05:25:53  Show Profile  Reply with Quote
Thanx visakh16.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/04/2013 :  01:29:40  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 10/08/2013 :  08:52:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Why do you want to create a new table? Can't you do it when selecting data from view?


SELECT *,
ROW_NUMBER() OVER (ORDER BY YourColumn ) As NewColumn
FROM MyView


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000