Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asm
Posting Yak Master
140 Posts |
 Posted - 2011-07-05 : 20:16:20
|
| Hi, How to create a new table based on existing table with extra field from a query.Thanks:) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-05 : 20:27:00
|
| [code]select * into YourNewTable from YourOldTable[/code]If you want to add another column, then simply add that column in the appropriate place, for example:[code]select col1, col2, cast('PlaceHolder' as varchar(255)) as DummyCol, col3, col4 into YourNewTable from YourOldTable[/code]Here, I am assuming that the Old Table has four columns col1, col2, col3, col4 and that you want to have another DummyCol in ordinal position 3.If you do not want any rows, just the table structure, you can add a TOP 0 clause to the query, for example[code]select TOP 0 * into YourNewTable from YourOldTable[/code]The examples above will not copy the primary keys, indexes etc. You will need to add them on.Alternatively, you can script the original table, change the names etc. and run the script. |
 |
|
|
asm
Posting Yak Master
140 Posts |
Posted - 2011-07-05 : 21:11:38
|
| I run the script and working fine but i want to put the added column should be identity column.Select top 0 cast('PlaceHolder' as int) as ID, * into Temp1 From Temp |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-06 : 02:54:03
|
| I don't think that can be done in the same query (but I might be wrong!). Another DDL-query will do the trick:ALTER TABLE Temp1 ADD ID int NOT NULL IDENTITY(1,1)alternatively, if you prefer to create the ID in the select itself you'll need to modify the column:ALTER TABLE Temp1 ALTER COLUMN ID int NOT NULL IDENTITY(1,1)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
|
|
Creating a Table from existing table