Hi All,
I want to understand MERGE behavior. In below example, I am using it for INS-UPD functionality.
However, I want to see the values which are getting inserted or updated in destination table.
Please help if anyone knows.
--src table
CREATE TABLE [dbo].[Supplier](
[SupplierCode] CHAR(8) PRIMARY KEY,
[SupplierName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Supplier] ([SupplierCode], [SupplierName], [Address])
VALUES
('S0000001', 'ABC Company', 'USA'),
('S0000002', 'XYZ Corporation', 'USA')
GO
SELECT * FROM [dbo].[Supplier]
--dest tbl
CREATE TABLE [dbo].[DimSupplier](
[SupplierId] [int] IDENTITY(1,1) NOT NULL,
[SupplierCode] CHAR(8),
[SupplierName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[EffectiveDate] [date] NULL,
[ExpirationDate] [date] NULL,
[CurrentFlag] [char](1) NULL,
CONSTRAINT [PK_DimSupplier] PRIMARY KEY CLUSTERED ([SupplierId] ASC)
) ON [PRIMARY]
GO
INSERT INTO [dbo].[DimSupplier] ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
SELECT [SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag]
FROM
(
MERGE [dbo].[DimSupplier] TT
USING [dbo].[Supplier] ST
ON (TT.[SupplierCode] = ST.[SupplierCode])
-- This inserts new records in the dimension table
WHEN NOT MATCHED THEN
INSERT ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
VALUES ([SupplierCode], [SupplierName], [Address], '01/01/1900', NULL, 'Y')
-- This marks the older record to be outdated for SCD Type 2
WHEN MATCHED AND TT.[CurrentFlag] = 'Y' AND (ISNULL(TT.[SupplierName], '') != ISNULL(ST.[SupplierName], '')) THEN
UPDATE SET TT.[CurrentFlag] = 'N', TT.[ExpirationDate] = GETDATE() - 1
OUTPUT $Action Action_Taken, ST.[SupplierCode], ST.[SupplierName],
ST.[Address], GETDATE() AS [EffectiveDate], NULL AS [ExpirationDate], 'Y' AS [CurrentFlag]
)
AS MERGE_OUT
WHERE MERGE_OUT.Action_Taken = 'UPDATE';
GO
select * from [DimSupplier]
go
---2 rows
Till here everything is fine....
Thing is that, I want to output/print what values are getting insert/updated so that i can see what values is getting inserted or updated.
I am using a SELECT statement , it is giving me an error.
SELECT [SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag]
FROM
(
MERGE [dbo].[DimSupplier] TT
USING [dbo].[Supplier] ST
ON (TT.[SupplierCode] = ST.[SupplierCode])
-- This inserts new records in the dimension table
WHEN NOT MATCHED THEN
INSERT ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
VALUES ([SupplierCode], [SupplierName], [Address], '01/01/1900', NULL, 'Y')
-- This marks the older record to be outdated for SCD Type 2
WHEN MATCHED AND TT.[CurrentFlag] = 'Y' AND (ISNULL(TT.[SupplierName], '') != ISNULL(ST.[SupplierName], '')) THEN
UPDATE SET TT.[CurrentFlag] = 'N', TT.[ExpirationDate] = GETDATE() - 1
OUTPUT $Action Action_Taken, ST.[SupplierCode], ST.[SupplierName],
ST.[Address], GETDATE() AS [EffectiveDate], NULL AS [ExpirationDate], 'Y' AS [CurrentFlag]
)
AS MERGE_OUT;
/*
Error message:
Msg 10729, Level 15, State 1, Line 40
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.
*/
I want to see/output data which is getting inserted or updated ? how can I do that?