How I can reset my table records to default

Question

1.00/5 (2 votes)

See more:

Hello, dear friends
I am using SQL Server 2008 when I changed ID column (float) data type to (nvarchar) I lost the correct 5000 records, now on just one ID there is more than 2 or three records available, so after change data type and remove primary key with save button a message appeared
(Your table data will be lost)
Please help me how I can reset my records

Thanks so much friends

What I have tried:

I tried last backup but just 2000 records were there but no one a professional there to take a backup

Posted 17-Apr-18 5:11am

Muhammad Qasim Ludin

Updated 17-Apr-18 14:19pm

Add a Solution

Comments

RedDk 17-Apr-18 14:51pm

You had an ID column that was formatted as (float) and in changing the type to (nvarchar) you lost the column of indices but you still retained the rest of the row for each record ... and at last count you still had 5000 records?

If this is the case I can't think of any way to recover that ID ... but my thought is that by sorting (using ORDER BY) on some other retained column value which will group distinct rows again ... a complete reindexing using "[ID][int] IDENTITY(1,1) NOT NULL" then INSERT from this unindexed table should suffice to make clustered records "closer" to the indexed and primary-keyed data to which you used to refer before the mistake.

Look up IDENTITY in the BOL for starters

an0ther1 17-Apr-18 18:06pm

If you have a backup, restore a copy to another location & then work out how to re-insert your data.
Any other method will take longer to resolve and will likely be unsuccessful.

As RedDK has suggested, look at IDENTITY in SQL Books on line & look at the IDENTITY_INSERT option - this allows you to re-insert data including the Identity value.
You will likely need to turn on IGNORE_DUP_KEY when re-inserting the data

Good Luck

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2018-04-17T12:42:00

0) Using a float for an ID column is not the best idea. If you think your ids are going to be numerous, use a bigint.

1) After adding data, changing the type of a column will result in the loss of all data in the table.

2) If it were me, I would create a whole new table with the same schema, change the type of the desired columns, and insert records from the old table into the new one, remembering to cast.convert the appropriate columns data into the new schema's type.

SQL

-- duplicate a table's schema

SELECT TOP 0 * INTO NewTable FROM OldTable;

-- after you do that, use the table designer to alter the necessary columns' type

-- and then do a query something like this

INSERT INTO NewTable
SELECT col1,
       Convert(newtype, col2) AS col2,
       ...
FROM OldTable;

EDIT =============================

Ya know, it thrills me to no end when someone 1–votes an answer without providing a reason why. Remember, the quality of the question directly affects the quality of the answer.

Patrice T · Answer 2 · 2018-04-17T14:20:00

You have just learned the hard way the reason why professional admins are loosing their time doing/organizing useless backups. It is because one day, the useless backup become useful and invaluable, but one never know when it will happen.

Quote:
I changed ID column (float) data type to (nvarchar) I lost the correct 5000 records

I also guess that now, you understand the reason why experimenting on live data is a bad idea.

Your solution: Take actions to prevent ever being in this situation. For your actual problem, it is too late.
- Backups, backups backups
- never experiment on live data, always make copies, when something go wrong, it is only a copy.

How I can reset my table records to default

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0