Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    99

    Unanswered: copy table data back and forth

    This is probably a dumb question.. but here goes.

    I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.

    What I 'thought' i could to was this:

    select * into mytable_temp from mytable

    and then to roll back..

    truncate table mytable

    Select * into mytable from mytable_temp

    When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this????

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by jpotucek
    This is probably a dumb question.. but here goes.

    I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.

    What I 'thought' i could to was this:

    select * into mytable_temp from mytable

    and then to roll back..

    truncate table mytable

    Select * into mytable from mytable_temp

    When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this????
    You can do it by -
    select * into mytable_temp from mytable

    and then to roll back..

    truncate table mytable

    insert into mytable
    select * from mytable_temp
    go
    drop table mytable_temp
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Mar 2004
    Posts
    99
    Thanks so much! that will work for me.. just couldn't get the syntax right!!!!

    one more question though.. how do I roill back when one column is an identity column? I get this error:

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

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by jpotucek
    Thanks so much! that will work for me.. just couldn't get the syntax right!!!!

    one more question though.. how do I roill back when one column is an identity column? I get this error:

    An explicit value for the identity column in table 'pcb00100' can only be specified when a column list is used and IDENTITY_INSERT is ON.
    Just do this -
    select * into mytable_temp from mytable

    and then to roll back..

    truncate table mytable
    go
    set identity_insert mytable on
    go
    insert into mytable(id,name)
    select id,name from mytable_temp
    go
    set identity_insert mytable off
    go
    drop table mytable_temp
    That will work for you.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Mar 2004
    Posts
    99
    AWESOME thank you so much!

  6. #6
    Join Date
    Mar 2004
    Posts
    99
    When I do this.. I'm still getting the below error....
    (not sure what you mean by id,name in your code.. here's mine.. )

    truncate table mytable
    go
    set identity_insert mytable on

    go
    insert into mytable
    select * from mytable_temp
    go
    set identity_insert mytable off
    go

    Server: Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table 'mytable' can only be specified when a column list is used and IDENTITY_INSERT is ON.
    Last edited by jpotucek; 03-17-06 at 10:21.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jpotucek
    Server: Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table 'PCB00100' can only be specified when a column list is used and IDENTITY_INSERT is ON.
    Code:
    insert into PCB00100 (column_list)
    select * from PCB00100_temp
    go
    -PatP

  8. #8
    Join Date
    Mar 2004
    Posts
    99
    Thanks Pat!

    Is there anyway to generate the column list so I can paste it into my code? There are ALOT of columns : )

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    You are a deviant soul... I knew that there was something I liked about you from the git-go!

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    You are a deviant soul... I knew that there was something I liked about you from the git-go!
    Lol - thanks (I think)

    BTW - ignore the bit where jhermiz says he would rather type out every column name for a table (twice) than use my script. He's just fooling around - we go back a long way and actually I am very highly respected at SQL Team...ahem
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    I am very highly respected at SQL Team...ahem
    that and 35 cents will get you a washington post.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    that and 35 cents will get you a washington post.
    I have not the faintest idea what that means - perhaps the meaning fades as it crosses the Atlantic or perhaps I am just being dim
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2006
    Location
    Los Angeles, CA
    Posts
    63
    Quote Originally Posted by pootle flump
    I have not the faintest idea what that means - perhaps the meaning fades as it crosses the Atlantic or perhaps I am just being dim
    My guess is that Thrasy is expressing how he doesn't like SQL Team ) .. I remember back (a while ago) he posted something on SQL Team that started a war of words. I was shocked at how edgy some of the folks at SQL Team were. To his defense (not that he needs it ) Thrasy didn't do anything that seemed offensive but the reaction from some SQL Team members was very immature.

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    In all fairness I did make a little joke to start it off and the ringleader Tara did back off. On a rare occassion I still post over there but the place does leave a bad taste in my mouth.

    If 35 cents gets you a washington post newspaper. And respect at SQLTeam + 35 cents gets you a washington post. Then what does respect at SQLTeam equal.

    X + Y = Z
    X = Z
    Y = 0
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •