I'm creating a temp table in a stored procedure by selecting 4 fields from an existing table. Then I add a new field to the table into which I want to store the current date and time. I'm getting an error on the update statement. The error is "invalid column DateAdded".
The code is:
SELECT WorkSites_ContactsImport.E
xtOrgID, WorkSites_ContactsImport.C
ontactNum,
WorkSites_ContactsImport.L
ocation, WorkSites_ContactsImport.C
ontactName
INTO #CNTCS_temp FROM WorkSites_ContactsImport LEFT JOIN WorkSiteContacts
ON (WorkSites_ContactsImport.
ContactNum
= WorkSiteContacts.ContactNu
m)
AND (WorkSites_ContactsImport.
ExtOrgID = WorkSiteContacts.ExtOrgID)
WHERE (((WorkSiteContacts.ExtOrg
ID) Is Null));
--SELECT * FROM #CNTCS_temp
ALTER TABLE #CNTCS_temp ADD DateAdded datetime
SELECT * FROM #CNTCS_temp
UPDATE #CNTCS_temp SET DateAdded = convert(datetime, convert(char(10), getdate(), 110))
The SELECT statement before the update gives this result, so I know the field is added.
![results of SELECT]()
What can I do to put the DateAdded into the temp table which will then be put into the production table using this statement.
INSERT INTO WorkSiteContacts (ExtOrgID, ContactNum, LocNum, ContactName, DateAdded) SELECT * FROM #CNTCS_temp
Thanks, Susan