Inserting PK value to table

Question

Inserting PK value to table

vime

SSC-Addicted

Points: 496
More actions
February 22, 2005 at 5:42 am

#85104

I have 4 tables
Tab A          Tab B          Tab c
site_id         Time_id          Site_name
site_name     Year             Min
What I would like is to get
Tab D
site_id
Time_id
min
so when site_name is added to Tab C it compares the name
with site_name in Tab A and adds the ID in Tab D
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 1

There are multiple ways of accomplishing this. One would be to create a trigger after insert of Tab C which could compare the vaules of Tab A then insert the ID into Tab D.

vime SSC-Addicted Points: 496 More actions · Answer 2

How would I write the trigger? what would be the T-SQL

Thanks

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 3

Something like

CREATE TRIGGER trigger_name

AFTER INSERT

ON TABLE

[ FOR EACH ROW ]

DECLARE

-- variable declarations

BEGIN

-- trigger code

EXCEPTION

WHEN ...

-- exception handling

END;

Hope this helps

Marty

vime SSC-Addicted Points: 496 More actions · Answer 4

Marty, thanks for your help, but what I need is the actual T-SQL code, so what I declare, what the code is. I need help with the T-SQL.

Thanks

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 5

Something like this could be written

Create Trigger tabcheck

on Tab C

For Insert

AS

Select TabA.Site_Name,TabC.Site_Name from TabA,TabC

If TabA.Site_Name=TabC.Site_Name then

Insert into TabD (site_name) Values (TabA.Site_Name)

Print'TabD has been Updated'

vime SSC-Addicted Points: 496 More actions · Answer 6

but I will I the insert the data from the othe tables.

What I am after is a way to insert all the data ie. get the site_id,time_id and min value and place it into TabD

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 7

Insert into TabD (site_name,site_id,time_id,min value ) Values (TabA.Site_Name,??,??,??)

rhunt SSCertifiable Points: 5868 More actions · Answer 8

Vime, I think what you are saying is that you need both a one time update - and a trigger for future updates...(I may be misundertanding).

See Martin's examples for the trigger. For the first update, you could do something like

INSERT INTO TAB_D

SELECT a.site_id, b.Time_id, c.min

FROM TAB_A a INNER JOIN TAB_B b on ?.?=?.?

INNER JOIN TAB_C c on a.site_name=c.site_name

Notice the ?.?=?.? on line three. I don't see an obviouse PK-->FK relationship for TAB_B. You will need to identify the correct relationship for the join.