Insert into another table while updating

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

Transact-SQL (2008)

Insert into another table while updating

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2015-03-06 : 07:01:48

Hi There,

I have a record that I update but after updating this record I need to set all the records that are related to the same groupid field the same record details. I have managed to do this with an update statement.
I am now struggling with the next part.
For all the updated records with the same groupid I need to add a record to another table. I am not sure how I do this as I have just used Update and set, not a for loop to get the individual unique Id and then use that in the insert statement.
Can anyone point me in the right direction?

I hope that all makes sense.

Thanks for any help.

Best Regards,

Always Learning.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2015-03-06 : 07:16:36

[CODE]
-- create table variable to hold the updated records
declare @UpdatedTeams table (TeamID int, TeamName varchar(100), CaptainID int)
--Do your UPDATE here
UPDATE Team
SET TEAM_NAME = REPLACE( TEAM_NAME , ' New', '')
OUTPUT inserted.* INTO @UpdatedTeams
WHERE TeamID=1

--Now the table variable @UpdatedTeams holds the updated records

--Then use that table variable to insert into another table as per your requirement

INSERT INTO AnotherTable
SELECT * FROM @UpdatedTeams
[/CODE]

--
Chandu

Steve2106
Posting Yak Master

183 Posts

Posted - 2015-03-07 : 18:32:33

Hi Chandu,

Thanks for the reply.
I cannot get it to work though and I think I should give you my table structure.

Tbl_WorkSheet
TaskId int
SetNum int
GroupNum int
DateCompleted datetime
Approver nvarchar(50)

My update statement is:
UPDATE Tbl_WorkSheet SET GroupNum = 7, DateCompleted = '07-03-2015', Approver = 'Dave Newey'
WHERE Tbl_WorkSheet.SetNum = 1

Taking this into account how do I create a table variable and fill that table with all the records that were updated including the taskid of each record updated.

Thanks for your help.

Best Regards,

Always Learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources