Insert statement logic - Any Help

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)

Insert statement logic - Any Help

Author

Topic

DeepakNewton
Starting Member

39 Posts

Posted - 2008-07-07 : 05:07:16

I have 2 tables below is my table structure where pkCId is Identity element and also the Relation between two tables , my problems is i have data from the Temp Table ( i have given sample data) which has the both table data, Like i need to Insert 1 row in the tblCcond table and get the pkCId and insert into the second for n number based upon the fkLId value, Please Provide any Logic

tblCcond

pkCId int 4
fkHId int 4
fkLId int 4

tblPValue

pkPValueId int 4
fkPaId int 4
PValue varchar100
fkCId int 4

TMP TABLE
SNO fkHid fkPaId PValue fkLId
1 10124 8 R 8
2 10124 9 BR 8
3 10124 10 CR 8
4 10124 11 DR 8
5 10124 12 AB 8
6 10124 13 BA 9
7 10124 14 ASB 9
8 10124 15 CBC 9
9 10124 16 DCB 9
10 10124 17 8000 10
11 10124 18 80000 10

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-07 : 05:23:23

Insert data into tblCcond

after the insert statement, use SCOPE_IDENTITY() to get the Identity just inserted, then use this to insert the rest of the data into tblPValue.

DeepakNewton
Starting Member

39 Posts

Posted - 2008-07-07 : 05:56:39

Hi RickD,
Thanks for ur reply, My concern here is like there should be one row inserted to the tblCcond
Depends upon the fkLId value i need to insert n numbers in the tblPValue

For Example
tblCcond
pkCId fkHId fkLId
1 10124 8

tblPValue
pkPValueId fkPaId PValue fkCId
1 8 R 1
2 9 BR 1
3 10 CR 1
4 11 DR 1
Thanks
deePak
5 10124 12 AB 8

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-07 : 05:59:48

It doesn't matter how many you need to insert into the second table, as long as they all depend on the value of the Id in the first table and you have that value stored in a variable for the second insert.

DeepakNewton
Starting Member

39 Posts

Posted - 2008-07-07 : 07:47:19

Below is the code I have used
But its generated unique record in the second table

SELECT @intParametersCount = COUNT(*) FROM #t_P

SET @intCount=1

WHILE (@intCount <= @intParametersCount)
BEGIN
INSERT tblCcond
SELECT
fkIndemnityHistoryId
,fkLId
FROM #t_P
WHERE SNO = @intCount

SELECT @DetId = SCOPE_IDENTITY()
INSERT tblPValue
SELECT
fkPId
,PValue
,@DetId
FROM
#t_P
WHERE
SNO = @intCount

SET @intCount = @intCount +1

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 13:44:30

it sounds as if you need a trigger for this. You can have an insert trigger which on insertion of records to table will cause insertion of records with values same as the id value of created records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources