March 4, 2019 at 8:38 am
I want to collect a value from the source table of a SELECT statement used in an INSERT statement, that is NOT inserted into the target table
I think the following code explains what I'm trying to do: Just cut and paste into SSMS
DECLARE @CrossRef TABLE (
MyTable_ID INT,
C_C VARCHAR(10)
);
DECLARE @MyData TABLE (
A VARCHAR(10),
B VARCHAR(10),
C VARCHAR(10) );
INSERT INTO @MyData (A, B, C)
VALUES ('A1', 'B1', 'C1'), ('A2', 'B2', 'C2'),('A3', 'B3', 'C3');
DECLARE @MyTable TABLE (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
A VARCHAR(10),
B VARCHAR(10) );
INSERT INTO @MyTable (A, B)
OUTPUT INSERTED.Id, MyData.C
INTO @CrossRef (MyTable_ID, C_C)
SELECT A, B
FROM @MyData AS MyData
-- Error: The multi-part identifier "MyData.C" could not be bound.
-- DESIRED OUTPUT
SELECT * FROM @MyTable
/*
ID A B
----------
1 A1 B1
2 A2 B2
3 A3 B3
*/
SELECT * FROM @CrossRef
/*
MyTable_ID C_C
---------------
1 C1
2 C2
3 C3
*/
Note - The example has been highly simplified to make the issue as clear as possible
It may seem trivial to get the desired output by other means, but like anything in production the real situation is much more complex
March 4, 2019 at 8:56 am
The problem is you are trying to OUTPUT a column from table @MyTable that does not exist on @MyTable(MyData.C).
March 4, 2019 at 9:29 am
The help on the OUTPUT clause seems to suggest that data not deleted can be accessed in the DELETED table
I want to find out if something similar is possible for INSERTED
March 4, 2019 at 9:48 am
I've figured it out - using the MERGE statement instead of INSERT
DECLARE @CrossRef TABLE (
MyTable_ID INT,
C_C VARCHAR(10)
);
DECLARE @MyData TABLE (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
A VARCHAR(10),
B VARCHAR(10),
C VARCHAR(10) );
INSERT INTO @MyData (A, B, C)
VALUES ('A1', 'B1', 'C1'), ('A2', 'B2', 'C2'),('A3', 'B3', 'C3');
DECLARE @MyTable TABLE (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
A VARCHAR(10),
B VARCHAR(10) );
-- MERGE statement does UPDATE where join condition exists and INSERT where it does not
MERGE @MyTable
USING (SELECT A, B, C FROM @MyData) AS [Source]
ON (1=0) -- join never true so everything inserted, nothing updated
WHEN NOT MATCHED THEN
INSERT (A, B)
VALUES ([Source].A, [Source].B)
OUTPUT INSERTED.Id, [Source].C
INTO @CrossRef (MyTable_ID, C_C);
SELECT * FROM @MyData
SELECT * FROM @MyTable
SELECT * FROM @CrossRef
March 4, 2019 at 9:53 am
tom 69406 - Monday, March 4, 2019 9:29 AMThe help on the OUTPUT clause seems to suggest that data not deleted can be accessed in the DELETED tableI want to find out if something similar is possible for INSERTED
Yes, you can access anything with inserted/deleted that's in the table in question. But MyData.C is in a different table.
deleted is the value before the statement execution (if it's an insert then it's null)
inserted is the value after the statement execution (if it's a delete then it's null)
March 6, 2019 at 9:17 am
The basic problem here is that you were trying to use a column that was not participating in the INSERT. Can't do that without using MERGE, and that does appear to be a creative use of it to expose that additional data. Very interesting, and wondering about whether or not there might be performance implications at high data volumes.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply