Problem in Login
-
Hi,
I had 5 user on a test database .Now one of the user had dropped the table and other user had executed the select statement.
Now the user who had executed the select statement had got the result with all the rows (5,000000)+
But as the query finished the other user had dropped the table.
now can anyone tell me that i want to recover all those records.
i dont have any backup.
I know the data is in temporary table. how can i put the data in a table
pls help
from
killer
-
How did you create the temp table in the first place??
If you hav eno backups, the only chance ou have left is that the user still has the temp table opened, or the recordset openened in a vba application where he could reenter all the data in a big while loop.
-
HI,
The query was executed on SQA and the temp table is still opened.
Now how can i enter that data in a table..
thanx for help
-
Have the user who selected the data into the temp table run this statement:
SELECT temp.*
INTO newtable
FROM temp
newtable is the name of the table that was dropped.
Greg
Greg
-
hi,
No the user has just executed select * from tablename.
but the data is still there in QA grid .
hope u all can help me
from
killer
-
If it's only in the QA grid, you're screwed.
-
if it's a test database, you should be able to get another copy from production, right? you do have access to backups, right?
if not, your only hope is that you can copy and paste the results from query analyzer to a text file; i don't know that QA will have all 5 million rows;
back that file up a couple of times, cause it's a long process to write a macro to change the data into insert statements.
append the "insert into sometable(field1,field2) values(" stuff to thefront of each line;if you have the grid view in QA then you can replace every tab chanracter with ',' and finally add a closing parenthesis to the end '). use a powerful text editor like Editplus (EditPlus.com. it allows you to replace a CrLf with item+CrLf so you can just do a find and replace instead of hand editing 5 million lines.
test the inserts, see what screws up, fix it and test yet again.
good luck, and start backing up your data from now on so you don't have to spend hours editing and testing things like this.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Nice... never thaught of that one
. -
You might be able to use the data in QA. Do a select all in the results pane (grid) and then copy, paste the data into either excel or notepad. It might even paste into a table result from EM, but I've never tried that.
Excel has a problem with some pasted data, usually when it tries to interpret data formats. (It might take character fields that just happen to appear numeric and strip off leading `0' values for example)
Notepad is the safest. Then you can save the file and import it using DTS or the import wizard in EM.
Good Luck!
JG
-
I didn't RTFP correctly. Excel won't work in your case because you have too many rows.
-
if the results are in grid mode then you can right click on the top left corner of the results (left most empty box on the column header) and select save as to save it to a CSV file and continues as others suggested to import it back to a table using DTS.
-
Hi,
I am working on the test.
Ok so that is only the way.
Thanx.
From
Killer
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply