Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATE table2 with value from table1 on INSERT into table1 Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
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
Post #777427
Posted Wednesday, August 26, 2009 6:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:12 AM
Points: 1,207, Visits: 1,259
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.
Post #777429
Posted Wednesday, August 26, 2009 7:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:41 AM
Points: 870, Visits: 894
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.


Karl
source control for SQL Server
Post #777471
Posted Wednesday, August 26, 2009 9:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
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#');

Post #777627
Posted Wednesday, August 26, 2009 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
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)#');

Post #777641
Posted Wednesday, August 26, 2009 12:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:41 AM
Points: 870, Visits: 894
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#' ;




Karl
source control for SQL Server
Post #777723
Posted Wednesday, August 26, 2009 12:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
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.
Post #777738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse