April 13, 2005 at 7:12 am
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
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
Thank you much!
April 13, 2005 at 7:41 am
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
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
Thank you David!!!!!!!!!!!!!!!!!!!
April 13, 2005 at 7:58 am
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
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
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy