Post reply

easy syntax question

  • April 13, 2005 at 7:12 am

    #66187

    Maybe not exactly a syntax question..  this is what I'm trying to do.I was to save table data before I delete some rows from it.

    Select * into temp_table from orig_table

    Then I'm deleting a row of data

    Delete orig_table where VCHRNMBR='12345678'

    Now I'm trying to figure out...  what if I want to restore the deleted row???? 

    I've looked in BOL and it's kinda confusing to me.  I thought it would be something like:

    Select * into orig_table from temp_table where VCRNMBR= '12345678'

    but that returns an error:

    There is already an object named 'existing_table' in the database.

    I'm doing this on a test machine (obviously) -----  WHAT AM I MISSING HERE!

  • April 13, 2005 at 7:19 am

    #552404

    Try

    insert into orig_table Select * from temp_table where VCRNMBR= '12345678'

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 13, 2005 at 7:26 am

    #552405

    Thank you much!

  • April 13, 2005 at 7:41 am

    #552408

    but I get this when I try and insert back into the orig_table?

    ERROR: an explicit value for the identity column in table 'orig_table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Identity row.. Primary Key.. Clustered Index..   AAHHHHHHH!!!!!

    so how do I restore the deleted row in this case?

  • April 13, 2005 at 7:45 am

    #552409

    SET IDENTITY_INSERT orig_table ON

    insert into orig_table

    (col1,col2,col3....)

    Select col1,col2,col3....

    from temp_table where VCRNMBR= '12345678'

    SET IDENTITY_INSERT orig_table OFF

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 13, 2005 at 7:48 am

    #552411

    Thank you David!!!!!!!!!!!!!!!!!!!

  • April 13, 2005 at 7:58 am

    #552412

    Perhaps you should also consider devoting some time to read about select, insert, update and delete statements in BOL (Books On Line).

    It's well spent effort.

    /Kenneth

  • April 13, 2005 at 8:32 am

    #552433

    I would also suggest that you start using the best practice of naming the columns in the insert statement :

    Insert into dbo.MyTable (col1, col2, coln) select col1, col2, coln from dbo.MyOtherTable

    It'll save you a lot of troubles later on.

  • April 14, 2005 at 1:19 pm

    #552806

    Thank you for all the suggestions!!!!!!!!!!!   I definitely have alot of reading to do!!!!!!

    What I ended up doing was exporting the 2 rows using DTS and a query to a flat file.  I successfully testing importing the 2 rows back into the table from the flat file using DTS.   seemed to work fine.

    thank you for all your help!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply