IDENTITY PROZBLEM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IDENTITY PROZBLEM

Author

Topic

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-28 : 04:22:15

select tmpID=identity(int,100001,1) into #tmp
from table1

tmpID
-----
100001
100002
100003
100004
100005
till end record

insert into table1(UID)
select ID from #tmp

UID
-----
100001
100001
100001
100001
100001
till end record

what is the problem?why table1 UID same 100001 record?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 04:29:03

Can i ask what are trying to achieve here?

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2008-02-28 : 04:39:32

What is your objective form this T-SQL script?

In your script, identity(int,100001,1) will generate data with a sequence for number of records you have in table1.

---------------------
http://dineshasanka.spaces.live.com/

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-28 : 04:40:59

quote:
Originally posted by visakh16

Can i ask what are trying to achieve here?

i select column tmpID and insert into table 1 column uid, but after inserted, all the records in table 1 UID became only '100001'.my expected result is

UID
---
100001
100002
and etc

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-28 : 04:43:57

Hi

change ID to tempid and check the results

insert into table1(UID)
select tempID from #tmp

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-28 : 04:46:47

quote:
Originally posted by PeterNeo

Hi

change ID to tempid and check the results

insert into table1(UID)
select tempID from #tmp

invalid column name...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 04:49:23

create table table1(uid int)

insert into table1
select 100001
union all
select 100001

select tmpID=identity(int,100001,1) into #tmp
from table1

insert into table1(uid)
select tmpId from #tmp

select uid from table1

drop table table1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 04:51:43

select tmpID from #tmp

E 12°55'05.25"
N 56°04'39.16"

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-28 : 05:00:24

quote:
Originally posted by madhivanan

create table table1(uid int)

insert into table1
select 100001
union all
select 100001

select tmpID=identity(int,100001,1) into #tmp
from table1

insert into table1(uid)
select tmpId from #tmp

select uid from table1

drop table table1

Madhivanan

Failing to plan is Planning to fail

thx.

insert into table1(uid)
select tmpId from #tmp

insert can work succesfully.
if When I take this insert function instead with update function, the result return all the same 10001.
how har?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 06:14:15

quote:
Originally posted by ooi_happiness

quote:
Originally posted by madhivanan

create table table1(uid int)

insert into table1
select 100001
union all
select 100001

select tmpID=identity(int,100001,1) into #tmp
from table1

insert into table1(uid)
select tmpId from #tmp

select uid from table1

drop table table1

Madhivanan

Failing to plan is Planning to fail

thx.

insert into table1(uid)
select tmpId from #tmp

insert can work succesfully.
if When I take this insert function instead with update function, the result return all the same 10001.
how har?

Can you post your update query?

Madhivanan

Failing to plan is Planning to fail

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-28 : 20:36:50

quote:
Originally posted by madhivanan
Can you post your update query?

Madhivanan

Failing to plan is Planning to fail

select UID=identity(int,100001,1) into #tmp
from TABLE_ooi

update TABLE_ooi
set uid = #tmp.uid
from #tmp

select UID from TABLE_ooi

Unexpected result:

UID
----
100001
100001
100001
100001
100001
and so on..

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-28 : 23:42:36

try this

select UID=identity(int,100001,1), tmpId -- (any primary key col from TABLE_ooi)
into #tmp
from TABLE_ooi

update t
set uid = t1.uid
from TABLE_ooi t
inner join #tmp t1 on t1.tmpid = t.tmpid

select UID from TABLE_ooi

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-29 : 00:56:16

quote:
Originally posted by PeterNeo

try this

select UID=identity(int,100001,1), tmpId -- (any primary key col from TABLE_ooi)
into #tmp
from TABLE_ooi

update t
set uid = t1.uid
from TABLE_ooi t
inner join #tmp t1 on t1.tmpid = t.tmpid

select UID from TABLE_ooi

in table_ooi, i got no data in primary key coll , so now im using this IDENTITY method to generate 1 wic start from 100001,100002 and so on.finally i got wrong result if im using update method to pull identity data to table_ooi primary key coll.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 01:10:12

[code]Alter Table dbo.TABLE_ooi
Drop Column uid
GO

Alter Table dbo.TABLE_ooi
add uid int identity(100001,1)
GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-29 : 01:27:55

quote:
Originally posted by harsh_athalye

Alter Table dbo.TABLE_ooi
Drop Column uid
GO

Alter Table dbo.TABLE_ooi
add uid int identity(100001,1)
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

after tis statement done successfully, then i try to insert others new data into UID column, but ERROR MSG:
"Cannot insert explicit value for identity column in table 'test_bak' when IDENTITY_INSERT is set to OFF."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 01:31:03

You can't insert values explicitly in an Identity column directly. Why do you want to do it anyway?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-29 : 01:34:24

quote:
Originally posted by harsh_athalye

You can't insert values explicitly in an Identity column directly. Why do you want to do it anyway?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

oic. Because after finish insert this identity record to column UID, then i need to insert others primary data into same UID column, izit able to do so?

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-29 : 02:35:41

PROBLEM clear successfully.
Thank you for all ur guidnce!!appreciate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources