I need to know the below mentioned task can be don or not in MSSQL 2008 R2?

Question

0.00/5 (No votes)

See more:

I need to know the below mentioned task can be don or not in MSSQL 2008 R2?

In Database, there is a table "books" and currently the key is file number and when ever any book uploaded, it assign the file number with 1 increment.

Now the task which I required is that, there are lots of files number (from 1 to 485) are missing, its because while testing at the development time. I want to add these in the table manually, so can we perform this without disturbing any thing locally or on the server...

Awaiting for your kindness....

Posted 21-Jan-12 23:49pm

genious Developer

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2012-01-22T00:29:00

Solution 1

No, not easily.

If you have declared the field as autoincrement, then SQL server takes it over completely, and will not allow you to specify the number at all - if you try to set the field on an INSERT or UPDATE command you will get an error. The main reason for this is that people insist on using these as an ID and there may be data in other tables that refers to the row by that value. If you could re-number them, then that data would be associated with the wrong record. If this was an invoicing system, then suddenly your customers get invoiced for items they didn't order, and get very annoyed! :laugh:

The only way I know of is to create a new table, copy the data over so it is assigned a new number, then delete the original table and rename. Nasty, but if you will use autonumber... :)

Personally, I use GUIDs instead, and refer to an ordinal number if I must. Autoincrements I only use when I just want an unique ID that I will never refer to: a log file or similar.

Posted 22-Jan-12 0:29am

OriginalGriff

Comments

genious Developer 22-Jan-12 7:09am

ok dear thanks for the info.

in my table records from 1 to 484 is empty and files start from 485.
i want the files which start from 485 remain same not start from 1. i want to enter record in first 484 also without disturbing the rest of the records.

is it poosible i create a new table and i add mannully first 484 records which i want to add at start then copy data from old table and paste their as same start from 485 and rest of the records.

is it fine?? i can do that???

OriginalGriff 22-Jan-12 7:26am

Yes, except for the paste part.
Create your new table.
Insert your new records.
Insert your existing records.
Delete original table
Rename new table.

Note that the records will go in with an ascending number, so if you only insert 20 new records first, the old records will be renumbered starting at 21. Don't try to copy the number field :laugh:

genious Developer 22-Jan-12 7:50am

thanks man.

but last question is in old table i have a lot of records starts from 485.enter first 484 records manually then can i copy and paste record start from 485 from the old table.or need to enter them manually

Heino Zunzer 23-Jan-12 7:09am

with
SET IDENTITY_INSERT books ON
you can insert any number into an autoincrement (identity) field. after you have done your inserts
SET IDENTITY_INSERT books OFF
will switch the autoincrement on again.

Using identity columns is always best practise. Using GUIDS worst practise. GUIDS are basically char columns and joining and searching on GUIDs takes much longer than on integer columns. also GUIDs are random and not in ascending order, which a primary key, espescially a clustered one, always should be.

and don't even get me started on the idea with creating a new table. just set identity_insert on and insert your values. as easy as that.