How to get autonumber and insert into other table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to get autonumber and insert into other table?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-27 : 11:49:01

Hi

I have 3 table (tbl1, tbl2, tbl3).

tbl1 colums ---Pid(autonNumber),Pcode(unique values),PGuid

tbl2 columns---Pid, CatId,Fid

tbl3 Colums----Pid,BundleId

The user will upload 1 excel(CSV) sheet 500 rows. I managed to bulkupload to tbl1. But how do I get the all Pid generated (after the uplaod) from tbl1 and insert into Pid of tbl2 and tbl3. Any ideas or code example will be more appreceatable. I am using asp.net with sql 2000, but expecting some kind of lead to get started.

Is it possible to do using sqlprocedure ? View ? etc

Advance thanks

karthik.shanmugavelu
Starting Member

3 Posts

Posted - 2007-11-28 : 00:54:12

hi
its a suggestion ,u can try it out ..
get the values of pid(autonnumber) in a variable and insert into the second table column.

karthik.shanmugavelu
Starting Member

3 Posts

Posted - 2007-11-28 : 00:56:15

hi
its a suggestion ,u can try it out ..
get the values of pid(autonnumber) in a variable and insert into the second table column.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-28 : 01:15:18

Hi,

take the max(Pid) from tbl1 before insert and after insert.
After getting those values by using a numbers table we can get the Pids generated between those two ranges.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-28 : 01:38:04

Hi!

Use after trigger

Here is a sample code. I hope that it will help u

create trigger t1 on tbl1
after insert
as
begin
declare @id int
select @id=pid from inserted
insert into tbl2(pid) values(@id)
insert into tbl3(pid) values(@id)
end

here inserted is an imaginary table. If you try to select from inserted statement without trigger it wont work

kiruthika!
http://www.ictned.eu

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-28 : 05:13:53

Kiruthika it was fantastic help. Thank you very much, thanks a lot

quote:
Originally posted by kiruthika

Hi!

Use after trigger

Here is a sample code. I hope that it will help u

create trigger t1 on tbl1
after insert
as
begin
declare @id int
select @id=pid from inserted
insert into tbl2(pid) values(@id)
insert into tbl3(pid) values(@id)
end

here inserted is an imaginary table. If you try to select from inserted statement without trigger it wont work

kiruthika!
http://www.ictned.eu

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-28 : 09:32:09

Hi

Is it possible to give some sample code for my below thread which is related to this thread ? Please have look at my thread

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93347[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources