August 26, 2009 at 2:10 pm
I have a [dbo].[DimOrders] table which has a OrderID column(autoID), trying to insert and update that table using Merge.
I am not sure if my query is correct, please advice. Thx.
But getting the below error:
Cannot insert the value NULL into column 'OrderID', table '[dbo].[DimOrders]'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Here is the below sql code:
CREATE TABLE [dbo].[DimOrders]
(
[OrderID] [int] IDENTITY(1, 1) NOT NULL,
[OrderName] [varchar](100) NOT NULL,
[OrderType] [varchar](12) NOT NULL,
[OrderNumber] [uniqueidentifier] NOT NULL
)
CREATE TABLE #MyTempTable
(
[ExistingOrderID] [int],
[ExistingOrderName] [varchar](100),
[ExistingOrderType] [varchar](12),
[ExistingOrderNumber] [uniqueidentifier],
ActionTaken NVARCHAR(10),
[NewOrderID] [int],
[NewOrderName] [varchar](100),
[NewOrderType] [varchar](12),
[NewOrderNumber] [uniqueidentifier]
)
DECLARE @OrderID INT,
@orderName [varchar](100),
@orderType [varchar](12),
@orderNumber [int]
BEGIN
SET NOCOUNT ON ;
MERGE [dbo].[DimOrders] AS target USING ( SELECT --@OrderID,
@orderName,
@orderType,
@orderNumber
) AS source
(
SELECT ordername, ordertype, ordernumber
FROM TABLEA a
INNER JOIN TABLEb b ON a.ID = b.ID
)
ON ( target.ordernumber = source.ordernumber )
WHEN MATCHED THEN UPDATE
SET
[orderName] = source.[orderName],
[orderType] = source.[orderType]
WHEN NOT MATCHED THEN INSERT ( [orderName],
[orderType],
[orderNumber] )
VALUES
(
--source.ResourceNumber,
source.[orderName],
source.[orderType],
source.[orderNumber]
)
OUTPUT
deleted.*,
$action,
inserted.*
INTO #MyTempTable ;
END ;
GO
SELECT *
FROM #MyTempTable ;
August 26, 2009 at 5:49 pm
No data to test with from you, so I don't even know if your code will run, but let me ask why is this in your code?
(
SELECT ordername, ordertype, ordernumber
FROM TABLEA a
INNER JOIN TABLEb b ON a.ID = b.ID
)
I go straight from "AS Source" to "On" in the following example and it works just fine.
declare @target table (id int identity(1,1), filler varchar(20))
insert into @target
select 'Blah' union all
select 'BlahBlah'
select * from @target
declare @source table (id int, filler varchar(20))
insert into @source
select 1,'NewBlah' union all
select null, 'BlahBlahBlah' union all
select null, 'Blah4'
select * from @source
merge @target as target using @source as source
on (target.id = source.id)
when Matched then Update
set
target.filler = source.filler
when not matched then Insert (filler)
values(source.filler);
select * from @target
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 27, 2009 at 4:45 am
Actually this code is for the source:
(
SELECT ordername, ordertype, ordernumber
FROM TABLEA a
INNER JOIN TABLEb b ON a.ID = b.ID
)
Per your example I shall insert this code's data into a @source table var.
ANd the target table's data will be inserted into @target table var.
Where will I mention the columns to compare?
If I want to compare col1, col2, col3, shall I write like this:
Merge @target(col1, col2, col3) using @source(col1, col2, col3) as source
on (target.id = source.id)
Please advice. Thanks much!!
August 27, 2009 at 9:50 am
Mh (8/27/2009)
Actually this code is for the source:
No, this is where you defined the source:
USING ( SELECT --@OrderID,
@orderName,
@orderType,
@orderNumber
) AS source
Per your example I shall insert this code's data into a @source table var.
You don't have to put the data into a table variable. Using a SELECT query for the source is perfectly acceptable, but it is located between USING and AS. Looking at your code, your source consists of a single row populated with the values from @orderName, @orderType, and @orderNumber. You are defining a match when the target.orderNumber column is equal to source.orderNumber.
If I want to compare col1, col2, col3, shall I write like this:
Merge @target(col1, col2, col3) using @source(col1, col2, col3) as source
on (target.id = source.id)
If your intent is to compare those three columns and call it a match only if they are all equal, then you would use (just like a JOIN or WHERE clause) the following:
ON (target.col1=source.col1 and target.col2=source.col2 and target.col3=source.col3)
Is your intent to merge the data from the following query into your target table? If so, what is the purpose of the select clause with @orderName, @orderType, and @orderNumber?
(
SELECT ordername, ordertype, ordernumber
FROM TABLEA a
INNER JOIN TABLEb b ON a.ID = b.ID
)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply