locked
creating a Table in SQL Server with fields from other tables and some fields user defined RRS feed

  • Question

  • How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
    can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
    Wednesday, February 20, 2008 8:34 AM

Answers

  • Try follwowing syntax.

     

     

    Select IdenCol Identity(int,1,1), ashu.Field1, ashu.Field2, ashu.Field2

    Into dbo.NewTableName

    From ashu

    Where 1=2

     

     

    ..offcourse you have to put the correct values in above statemetn..

     

    all the best..

     

     

     

    Wednesday, February 20, 2008 9:00 AM
  • select * into #ashutosh2 from attribute where 1=2
    ALTER table #ashutosh2 add newfid int identity(1,1)
    insert into #ashutosh2 select * from attribute
    //THIS WILL 1.CREATE TABLE FROM "ATTRIBUTE" TABLE,2.ADDS NEW FIELD "NEWFID" AS IDENTITY,3.COPIES VALUES FROM "ATTRIBUTE" TO "ASHUTOSH" USED # TO INDICATE ITS TEMPORARY TABLE//
    Wednesday, February 20, 2008 9:32 AM

All replies

  • Try follwowing syntax.

     

     

    Select IdenCol Identity(int,1,1), ashu.Field1, ashu.Field2, ashu.Field2

    Into dbo.NewTableName

    From ashu

    Where 1=2

     

     

    ..offcourse you have to put the correct values in above statemetn..

     

    all the best..

     

     

     

    Wednesday, February 20, 2008 9:00 AM
  •  

    If there is not identity field in ASHU table.. you can use * instead of wrting the fields names explicitly..

     

     

    Wednesday, February 20, 2008 9:01 AM
  • select * into #ashutosh2 from attribute where 1=2
    ALTER table #ashutosh2 add newfid int identity(1,1)
    insert into #ashutosh2 select * from attribute
    //THIS WILL 1.CREATE TABLE FROM "ATTRIBUTE" TABLE,2.ADDS NEW FIELD "NEWFID" AS IDENTITY,3.COPIES VALUES FROM "ATTRIBUTE" TO "ASHUTOSH" USED # TO INDICATE ITS TEMPORARY TABLE//
    Wednesday, February 20, 2008 9:32 AM
  • Yes that will work too.. but In above scenario.. you only need one single statement..

     

    select idetity(int, 1,1) as newfid, * into #ashutosh2 from attribute

     

    dont need to add where clause "Where 1=2" as you want the whole data from attribute table..

     

     

    Wednesday, February 20, 2008 9:50 AM
  • identity(int,1,1) is throwing error as "Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'IDENTITY'."
    Wednesday, February 20, 2008 10:05 AM
  • you have to give alias name for this..

     

    identity(int, 1, 1) as ColumnName...

     

     

    Wednesday, February 20, 2008 10:12 AM