How to optimise multiple inserts with identity

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to optimise multiple inserts with identity

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-04 : 18:18:35

Hi All,

In following code i am inserting names from table #t into #emp and i am capturing newly generated emp_no into table #t1 for further processing.

Please help me in optimising this code.


CREATE TABLE #emp(emp_no int IDENTITY (1, 1) NOT NULL,name varchar(50))
insert into #emp values('abc')
insert into #emp values('xyz')

CREATE TABLE #t(ident int IDENTITY (1, 1) NOT NULL,name varchar(50))
insert into #t values('peter')
insert into #t values('tom')
insert into #t values('ken')

CREATE TABLE #t1(new_emp_no int)

DECLARE  @curr_id int,@highest_id int,@next_id int
DECLARE @N int

SELECT  @curr_id=0
SELECT  @highest_id=MAX(Ident) FROM #t

WHILE @curr_id <@highest_id
BEGIN
	SELECT TOP 1 @next_id=Ident
	FROM #t
	WHERE Ident>@curr_id
	ORDER BY Ident

		INSERT INTO #emp
		SELECT name
		FROM #t
		WHERE #t.Ident=@next_id
		
		SELECT @N=SCOPE_IDENTITY()
	
	insert into #t1 values(@N)
	set @curr_id=@next_id
END 

select * from #emp
select * from #t1
DROP table #emp,#t,#t1

Thanks

mk_garg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-05 : 01:18:16

[code]-- prepare sample data
create table #emp(emp_no int identity (1, 1) not null,name varchar(50))

insert into #emp values('abc')
insert into #emp values('xyz')

create table #t(ident int identity (1, 1) not null,name varchar(50))

insert into #t values('peter')
insert into #t values('tom')
insert into #t values('ken')

create table #t1(new_emp_no int)

declare @emp_no int

SELECT @emp_no = ident_current('#emp')

INSERT #emp
SELECT name
FROM #t

insert #t1
select emp_no
from #emp
where emp_no > @emp_no

select * from #emp
select * from #t1

drop table #emp,#t,#t1[/code]

Peter Larsson
Helsingborg, Sweden

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-05 : 05:13:10

Thanks for that.

If other users are adding records in #emp table at that time.
How i can make sure #t1 got only ids generated by my code?

Regards

mk_garg

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-05 : 05:18:43

Local temporary tables are accessible only within the connection context in which they are created. So other users can't access your #emp table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 05:18:59

temporary table only exists or local to per connection. If other users is connecting to the database using a different connection, each connection will have it's own temp table.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-05 : 17:23:31

My apologies for confusing you guys.

I created this example just to get help. In reality #emp and #t1 will not be temp tables.
They will be permanent tables.

So again smae question, How i can make sure it return only emp_no generated by my code.

Regards

mk_garg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 01:29:41

Start a transaction.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources