To update several tables in one execution

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 2005 Forums

Transact-SQL (2005)

To update several tables in one execution

Author

Topic

svgeorge
Starting Member

23 Posts

Posted - 2007-09-26 : 09:58:56

How do I update several tables at one single execution

below is my each update command and I saved this as sql file and if I open this file and exucute will it update all tables or syntax needs to be changed please

suggest.

UPDATE dbo.Payment_Enrollment_AIMS
SET dbo.Payment_Enrollment_AIMS.[PROJ END DT]= dbo.View_1.DTE_PROJ_END
FROM dbo.Payment_Enrollment_AIMS JOIN dbo.View_1
ON dbo.Payment_Enrollment_AIMS.Enrollment_ID=dbo.View_1.Enrollment_ID
where dbo.Payment_Enrollment_AIMS.JC_ID = dbo.View_1.JC_ID
and dbo.Payment_Enrollment_AIMS.[PROJ START DT] =dbo.View_1.[PROJ START DT]
and dbo.Payment_Enrollment_AIMS.[Grant]=dbo.View_1.[Grant]
and dbo.Payment_Enrollment_AIMS.TERM= dbo.View_1.TERM

GO

UPDATE dbo.Payment_Placement_AIMS
SET dbo.Payment_Placement_AIMS.[EMP END DT]= dbo.View_2.DTE_END_EMPLR
FROM dbo.Payment_Placement_AIMS JOIN dbo.View_2
ON dbo.Payment_Placement_AIMS.JC_ID = dbo.View_2.JC_ID
where dbo.Payment_Placement_AIMS.[EMP START DT] =dbo.View_2.[EMP START DT]
and dbo.Payment_Placement_AIMS.[Grant]=dbo.View_2.CDE_PROJ
and dbo.Payment_Placement_AIMS.HOURS= dbo.View_2.HOURS
and dbo.Payment_Placement_AIMS.WAGE=dbo.View_2.WAGE

GO

UPDATE dbo.Payment_Activity_AIMS
SET dbo.Payment_Activity_AIMS.[ACTV END DT]= dbo.View_3.DTE_END_ACTV
FROM dbo.Payment_Activity_AIMS JOIN dbo.View_3
ON dbo.Payment_Activity_AIMS.ACTIVITY_ID =dbo.View_3.ACTIVITY_ID
where dbo.Payment_Activity_AIMS.JC_ID = dbo.View_3.JC_ID
and dbo.Payment_Activity_AIMS.[ACTV START DT] = dbo.View_3.[ACTV START DT]
and dbo.Payment_Activity_AIMS.[ACTIVITY CODE]= dbo.View_3.[ACTIVITY CODE]
and dbo.Payment_Activity_AIMS.PROGRAM= dbo.View_3.PROGRAM

GO

UPDATE dbo.Payment_Activity_AIMS
SET dbo.Payment_Activity_AIMS.[ACTV END DT]= dbo.View_3.DTE_END_ACTV
FROM dbo.Payment_Activity_AIMS JOIN dbo.View_3
ON dbo.Payment_Activity_AIMS.ACTIVITY_ID =dbo.View_3.ACTIVITY_ID
where dbo.Payment_Activity_AIMS.JC_ID = dbo.View_3.JC_ID
and dbo.Payment_Activity_AIMS.[ACTV START DT] = dbo.View_3.[ACTV START DT]
and dbo.Payment_Activity_AIMS.[ACTIVITY CODE]= dbo.View_3.[ACTIVITY CODE]
and dbo.Payment_Activity_AIMS.PROGRAM= dbo.View_3.PROGRAM

GO

UPDATE dbo.Payment_Subproject_Retention_AIMS
SET dbo.Payment_Subproject_Retention_AIMS.[SUBPROJ END DT]= dbo.View_4.DTE_END_PROJ_SUB,
dbo.Payment_Subproject_Retention_AIMS.PROJ_END_DATE= dbo.View_4.DTE_PROJ_END
FROM dbo.Payment_Subproject_Retention_AIMS JOIN dbo.View_4
ON dbo.Payment_Subproject_Retention_AIMS.Subproject_Retention_ID =dbo.View_4.Subproject_Retention_ID
where dbo.Payment_Subproject_Retention_AIMS.JC_ID = dbo.View_4.JC_ID
and dbo.Payment_Subproject_Retention_AIMS.[SUBPROJ START DT] = dbo.View_4.[SUBPROJ START DT]
and dbo.Payment_Subproject_Retention_AIMS.[SUB PROJECT]= dbo.View_4.[SUB PROJECT]
and dbo.Payment_Subproject_Retention_AIMS.PROJ_START_DATE= dbo.View_4.DTE_PROJ_BEGIN

GO

UPDATE dbo.Payment_Placement_Retention_AIMS
SET dbo.Payment_Placement_Retention_AIMS.[EMP END DT]= dbo.View_5.DTE_END_EMPLR
FROM dbo.Payment_Placement_Retention_AIMS JOIN dbo.View_5
ON dbo.Payment_Placement_Retention_AIMS.Placement_Retention_ID =dbo.View_5.Placement_Retention_ID
where dbo.Payment_Placement_Retention_AIMS.JC_ID = dbo.View_5.JC_ID
and dbo.Payment_Placement_Retention_AIMS.[Grant] = dbo.View_5.[Grant]
and dbo.Payment_Placement_Retention_AIMS.[EMP START DT]= dbo.View_5.[EMP START DT]

GO

UPDATE dbo.Payment_Placement_Retention_AIMS
SET dbo.Payment_Placement_Retention_AIMS.PROJ_END_DATE = dbo.View_6.DTE_PROJ_END
FROM dbo.Payment_Placement_Retention_AIMS JOIN dbo.View_6
ON dbo.Payment_Placement_Retention_AIMS.Placement_Retention_ID =dbo.View_6.Placement_Retention_ID
where dbo.Payment_Placement_Retention_AIMS.JC_ID = dbo.View_6.JC_ID
and dbo.Payment_Placement_Retention_AIMS.[Grant] = dbo.View_6.[Grant]
and dbo.Payment_Placement_Retention_AIMS.PROJ_START_DATE= dbo.View_6.PROJ_START_DATE
and dbo.Payment_Placement_Retention_AIMS.CDE_PROJ_TERM= dbo.View_6.CDE_PROJ_TERM

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 10:27:46

Yes it will update several tables, but you probably should surround the statements by a

BEGIN TRANSACTION

and then

COMMIT (if no errors)
or
ROLLBACK (if errors)

and take out the GO's between each statement (because they may prevent a rollback if there is an earlier error)

Kristen

svgeorge
Starting Member

23 Posts

Posted - 2007-09-26 : 13:21:07

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources