insert values into both table at the same time using sql server 2005

hi all,

In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.

table 1:

tid--> int,identity,primary key

tname-->varchar(200)

table 2:

sid-->int,identity,primary key

tid-->fk (this tid is set as foreign key for the tid in table1)

now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..

pls help me..

thanks

swapna

Go For Stored Procedure for Insert...

SP Flow should be-

1. Start SQL Transaction

2. Insert into Table1

3. Get the Inserted INDENTITY value.

4.Insert into table 2

5. Commit Transaction Or Rollback transaction depending on the Error .

|||

You can create a stored procedure. There use will insert the record in the table 1 first and fetch the latest generated id value in table 1 using scope_identity and store it in a variable. Then you will insert the corresponding record in table 2 using the variable value.

For help on how to call a stored procedure from code visit http://forums.asp.net/t/1165758.aspx.

Feel free to ask for more help on this issue.

|||

Or you can create a trigger to insert the row into the second table.

It if must always happen the same way, the trigger would be a safer bet.

check out create trigger in the documentation.

sql

Insert Trigger Question

Can someone help me with this scenario:
I have 2 tables one detail and one summary. The key fields for betwen the the detail and summary tables are the date field na the part # field.
I need to create a trigger that will insert records when they do not exist in the summary table and also only update the records that are needing to be modified. Any Ideas?

ThanksReal time warehousing?

Can't you run a scheduled process instead?|||Originally posted by Brett Kaiser
Real time warehousing?

Can't you run a scheduled process instead?

I need to have this summary infomration available at any time the daily reports need to be run. A scheduled process would be easier but I need to keep this table up to date as transcations are processed in the detail table.|||Use this as an example:

create table item(id int identity,item# varchar(10))
create table itemsummary(id int identity,item# varchar(10),quantity int)
go
create trigger iu_item on item
for insert,update
as
insert itemsummary(item#)
select item# from inserted i
where not exists(select 1 from itemsummary where item#=i.item#)
update itemsummary set quantity=(select count(*) from item i where i.item#=itemsummary.item#)
go
insert item(item#) values('#1')
insert item(item#) values('#1')
insert item(item#) values('#2')

select * from item
select * from itemsummary|||He'll need to update..the count? for the part#

So you need 2 sections...

Check

-- An Update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)

--An INSERT
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)

Then do your apporpriate action...

What's the transaction level?sql