December 1, 2016 at 7:55 am
On this update statement, I want to get the output from the original source value (since it's not in the table itself):
UPDATE company SET
active =
CASE
WHEN [F51] < GETDATE() THEN 0
WHEN [F51] > GETDATE() THEN 1
END,
dateModified = GETDATE()
OUTPUT inserted.company, [myexcel]...['Company'].[F8] INTO #updated
FROM
[myexcel]...['Company']
WHERE [F8] in (SELECT company FROM mastercompany )
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "myexcel...'Company'.F8" could not be bound.
I guess output does not allow you to do that?
December 1, 2016 at 8:02 am
If you're looking for the new value, use the Inserted virtual table, as you have done. For the old value, use the Deleted virtual table.
John
December 1, 2016 at 8:47 am
UPDATE does allow you to use a named table as long as it's not the one being updated. If you use the name of the table that's being updated, it can't tell whether you want the original value or the new value, which is why you need to use the INSERTED and DELETED virtual tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 1, 2016 at 8:52 am
[myexcel]...['Company'] does not look like a valid object identifier to me.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2016 at 1:16 pm
I have updated to add table alias and changed table alias on OUTPUT and gone with a real table instead of temp table:
UPDATE company SET
active =
CASE
WHEN [F51] < GETDATE() THEN 0
WHEN [F51] > GETDATE() THEN 1
END,
dateModified = GETDATE()
OUTPUT inserted.company, myexcelfile.[F8] INTO updated
FROM
[myexcel]...['Company'] myexcelfile
WHERE [F8] in (SELECT company FROM mastercompany )
Seems to at least pass syntax check now.
December 1, 2016 at 1:17 pm
That's a linked server to an Excel Spreadsheet.
December 2, 2016 at 1:46 am
I'm not sure that's a legal operation. From Books Online (emphasis mine):
The OUTPUT clause is not supported in the following statements:
* DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
* INSERT statements that contain an EXECUTE statement.
* Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
* The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
But let's assume it were indeed legal. It's slightly unusual, because you don't include any of the updated columns in your OUTPUT clause. So even though you're not updating the F8 column, you still have to refer to it as Inserted or Deleted (since you didn't update it, it doesn't matter which). Something like this may work, or you may have to tweak it a little:UPDATE c
SET
c.active =CASE
WHEN F51 < GETDATE() THEN 0
WHEN F51 > GETDATE() THEN 1
END
,c.dateModified = GETDATE()
FROM myexcel...Company c
JOIN mastercompany m ON c.F8 = m.company
OUTPUT
m.company
,inserted.F8
INTO #updated
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply