locked
Copy data from one table to another RRS feed

  • Question

  • hi,

      i am in need to create back up of some tables accoridng to some criteria..

    I used select into statement to create and copy first data. From second time i need to just copt to new table from original table..

    For this i wrote the following sql

    INSERT INTO backupTable SELECT * FROM originalTabe

    But i got error as follows:

    An explicit value for the identity column in table 'backupTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    I tried after set identity on but that time also same error. Whe i try with a specific column then the query work correctly. but i need to copy all the columns...

    Friday, July 2, 2010 6:23 AM

Answers

  • i finished by geting all the column from existing table and create new one if not exist and copy..thanks for the help friends
    • Marked as answer by akhilrajau Monday, July 5, 2010 5:19 AM
    Monday, July 5, 2010 5:19 AM

All replies

  • The IDENTITY_INSERT should be set within Begin ... END. This is because before you execute you Insert statement the IDENTITY_INSERT should have executed on Server. Check below code for help. 

     

    BEGIN
    SET IDENTITY_INSERT XYZTable ON
    END
    
    BEGIN
    insert into XYZTable(Columns) 
    select * from ABCTable
    END
    
    BEGIN
    SET IDENTITY_INSERT XYZTable OFF
    END


    Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. My Blog: http://ashishkhandelwal.arkutil.com
    Friday, July 2, 2010 7:12 AM
  • hi friend,

     Actually i disable IDENTITYSPECIFICATION in the new table and my query worked nice...

    So can i create table by identity speciafcation false in select into statement

    Friday, July 2, 2010 7:28 AM
  • i finished by geting all the column from existing table and create new one if not exist and copy..thanks for the help friends
    • Marked as answer by akhilrajau Monday, July 5, 2010 5:19 AM
    Monday, July 5, 2010 5:19 AM