August 26, 2009 at 6:39 am
Good morning all,
And thank i advance. I need to "UPDATE table2.ID with value from table1.ID on INSERT into table1"
Table1.ID is Identity Insert but table2.ID is Nullable. I want when I insert values into table1 that table2 get the same ID from table for future queries.
UPDATE tbl_Assembly_Delay1.ID
SET tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID
FROM tbl_Assembly_Production
WHERE (tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID)
Thanks again,
DJ Khalif
August 26, 2009 at 6:42 am
It sounds like you need an insert trigger on table 1. I can't rattle off the syntax for this off the top of my head but you can look up "triggers" in books online and see if that will do what you are needing.
August 26, 2009 at 7:35 am
kabaari (8/26/2009)
Good morning all,And thank i advance. I need to "UPDATE table2.ID with value from table1.ID on INSERT into table1"
Table1.ID is Identity Insert but table2.ID is Nullable. I want when I insert values into table1 that table2 get the same ID from table for future queries.
UPDATE tbl_Assembly_Delay1.ID
SET tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID
FROM tbl_Assembly_Production
WHERE (tbl_Assembly_Production.ID = tbl_Assembly_Delay1.ID)
Thanks again,
DJ Khalif
Something doesn't quite seem right here.....
If the ID value in table2 is nullable how will you ever be able to link up table2 with table1?
Let's follow this through the logical sequence....
1. You insert a record into table1.
2. You can use scope_identity() to get the identity value of the record you just inserted.
3. So now you want to update table2's ID column with this ID you just inserted. But which row are you going to update? In other words, what conditions are you using to determine which row you want to update in table2?
Hope that makes sense.
August 26, 2009 at 9:43 am
Thanks in advance:
I realized that logically I was incorrect. Below is where I attempted to correct my mistake. Now I get: "Incorrect syntax near 'Code'".
INSERT into tbl_Assembly_Delay(ID, Code, DelayTime)
( '#Trim(FORM.Code)#',
'#Trim(FORM.DelayTime)#', SELECT ID
FROM tbl_Assembly_Production
WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#');
August 26, 2009 at 9:58 am
I made another change and I get this: Incorrect syntax near ','.
INSERT into tbl_Assembly_Delay(ID, Code, DelayTime)
SELECT ID
FROM tbl_Assembly_Production
WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#' AND ('#Trim(FORM.Code)#' ,
'#Trim(FORM.DelayTime)#');
August 26, 2009 at 12:01 pm
Try this:
INSERT into tbl_Assembly_Delay(ID, Code, DelayTime)
SELECT ID, '#Trim(FORM.Code)#', '#Trim(FORM.DelayTime)#'
FROM tbl_Assembly_Production
WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#' ;
August 26, 2009 at 12:13 pm
Thanks. That was the same thing I came up with:
INSERT INTO tbl_Assembly_Delay1(ID, Code, DelayTime)
SELECT ID , '#Trim(FORM.Code)#', '#Trim(FORM.DelayTime)#'
FROM tbl_Assembly_Production
WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#'
I also ran into some other issues but figured those too. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply