how to insert same value in auto generated column in sql server 2008

Question

1.00/5 (1 vote)

See more:

how to insert same value in auto generated column in sql server 2008

my table script is:

CREATE TABLE [dbo].[rr](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [add] [varchar](50) NULL
)

i want my table value like that

id      name    address

1	aa      noida
2	ss	delhi
2	dd      punjab
3	ee	okhla

Posted 27-Jun-12 22:01pm

srishti_

Add a Solution

Comments

Herman<T>.Instance 28-Jun-12 3:03am

why not use an update on that field Add with value from Name ?

Your question has lack of enough information to exactly understand your problem.

4 solutions

Add a Solution

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

OriginalGriff · Answer 1 · 2012-06-27T22:11:00

Bad idea.

The whole idea of a autogenerated column is that it holds unique values, not unique-except-when-I-feel-like-it values.
The whole purpose of an "ID" field is to uniquely identify a row, so that it can be handled correctly. With your scheme, an attempt to delete the second row could well delete the third as well - which is probably not what you want.

Leave IDs alone, make them unique and reconsider whatever you are doing that you think needs duplicate row ids.

What are you trying to achieve that you think this would be a good idea?

[edit]Minor typos - OriginalGriff[/edit]

_Maxxx_ · Answer 2 · 2012-06-27T22:07:00

Solution 1

YOu need to create the table without the column being specified as an identity, then insert the values you want, and then change teh column to be an identity, with the seed starting not at 1, but at 5 (in your example)

Permalink

Posted 27-Jun-12 22:07pm

_Maxxx_

Comments

srishti_ 28-Jun-12 3:29am

i need to have a column identity i can't remove that. and want to insert same value in that column

Kiran Susarla · Answer 3 · 2012-06-27T22:07:00

Solution 2

You can use

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

statement to insert a value into identity column.

Detailed information is available at http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx[^]

Permalink

Posted 27-Jun-12 22:07pm

Kiran Susarla

Mohamed Mitwalli · Answer 4 · 2012-06-27T22:12:00

Solution 4

Hi ,
You can't do that as long you set IDENTITY on, Solution for this remove the IDENTITY and make your own function which make IDENTITY and be careful
if your column is PK you will not be allowed to duplicate data because PK have to be unique .
Best Regards
M.Mitwalli

Permalink

Posted 27-Jun-12 22:12pm

Mohamed Mitwalli

Comments

srishti_ 28-Jun-12 3:25am

its possible through identity column but how i don't know

how to insert same value in auto generated column in sql server 2008

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0

how to insert same value in auto generated column in sql server 2008

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us