scover22
asked on
SELECT INTO #temp table Then add field
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.
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
The code is:
SELECT WorkSites_ContactsImport.E
WorkSites_ContactsImport.L
INTO #CNTCS_temp FROM WorkSites_ContactsImport LEFT JOIN WorkSiteContacts
ON (WorkSites_ContactsImport.
AND (WorkSites_ContactsImport.
WHERE (((WorkSiteContacts.ExtOrg
--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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER