Problem adding records to a table using data from another table 1

[jbradley]

Although I am not a database programmer I have ended up with the task of reverting the value in the status field of records in the master table on a SQL 2005 based application we use to its previous value. I am collecting the data I need into a temporary table then joininng it to the master table on the customer field to update the master.status field from the temp.oldstatus field. Then I need to create a new record in a notes table so we have an audit trail of the status change.
If I were writing an external program to do this I would load the temp table into a recordset in memory and use a Do loop to INSERT INTO the notes table one record at a time. What I'm trying to do here is write a stored procedure using t-sql to do everything and I don't know how to do this final step. Here is what I have so far:

USE Collect2000training
GO

DROP TABLE dbo.StatusChangeTemp
GO

SELECT dbo.StatusHistory.AccountID, 
	 MAX(dbo.StatusHistory.DateChanged) AS DateChanged, 
	 dbo.StatusHistory.OldStatus, 
	 dbo.StatusHistory.NewStatus, 
	 GETDATE() AS RECORDDATE, 
	 'Reverted status from ' + dbo.StatusHistory.NewStatus + ' to ' + dbo.StatusHistory.OldStatus AS COMMENT
INTO dbo.StatusChangeTemp 
FROM dbo.StatusHistory
WHERE (dbo.StatusHistory.OldStatus <> dbo.StatusHistory.NewStatus) 
	AND (dbo.StatusHistory.OldStatus IN ('CBD', 'CYC', 'DCC', 'FRD')
		OR dbo.StatusHistory.NewStatus = 'NSF')
GROUP BY dbo.StatusHistory.AccountID, 
		 dbo.StatusHistory.OldStatus, 
		 dbo.StatusHistory.NewStatus
		 
SELECT * FROM dbo.StatusChangeTemp
ORDER BY dbo.StatusChangeTemp.AccountID DESC
GO

UPDATE dbo.[master] INNER JOIN StatusChangeTemp ON dbo.[master].[number] = StatusChangeTemp.AccountID ]
SET dbo.[master].[status] = [StatusChangeTemp].[OldStatus]
GO

At this point I need to do something like ...

INSERT INTO notes 
FIELDS([number], created, comment, UtcCreated) 
VALUES(dbo.StatusChangeTemp.AccountID, dbo.StatusChangeTemp.RECORDDATE, dbo.StatusChangeTemp.COMMENT, dbo.StatusChangeTemp.RECORDDATE)

... and let all the other fields in the notes table default to NULL. How can I do an INSERT like this from one table to another?

 

[PWise]

insert into tablename (Field1,field2.....)
Select xxxxx,yyyy,zzzz........
from sometable
inner join someothertable
on sometable.Somefiel =someothertable.someothefield
where athirdfield = somevalue

 

[jbradley]

Thanks. I should be able to figure out from that.