March 16, 2014 at 8:21 pm
Hi
I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. Any idead what's wrong with the code below? The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 10
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE)
VALUES(10,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1)
Regards
Vik
March 16, 2014 at 9:34 pm
Please bare in mind that LGI_GROUPID is a primary key in the target table and a foreign key in the source table.
March 16, 2014 at 10:32 pm
This might be because there is no record into the Source.
IF Object_id('LOAN_GROUP_INFO') IS NULL
CREATE TABLE LOAN_GROUP_INFO
(
LGI_GROUPLVR INT ,LGI_GROUPEXPOSURE INT ,LGI_DATECREATED DATETIMEOFFSET(3),LGI_CREATEDBY INT,LGI_DATEUPDATED DATETIMEOFFSET(3) ,LGI_UPDATEDBY INT ,LGI_ISACTIVE BIT,
LGI_GROUPID INT
)
GO
IF Object_id('LOAN_GROUPING') IS NULL
CREATE TABLE LOAN_GROUPING
(
LGI_GROUPLVR INT ,LGI_GROUPEXPOSURE INT ,LGI_DATECREATED DATETIMEOFFSET(3),LGI_CREATEDBY INT,LGI_DATEUPDATED DATETIMEOFFSET(3) ,LGI_UPDATEDBY INT ,LG_ISACTIVE BIT,
LG_LOANID INT,LGI_GROUPID INT
)
DElete from LOAN_GROUP_INFO
delete from LOAN_GROUPING
GO
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT * FROM LOAN_GROUPING
)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
AND SOURCE.LG_LOANID = 10
AND SOURCE.LG_ISACTIVE = 1
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 10
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE,LGI_GROUPID)
VALUES(Source.LGI_GROUPID,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1,Source.LGI_GROUPID);
GO
select * from LOAN_GROUP_INFO
INSERT INTO LOAN_GROUPING (LG_LOANID,LG_ISACTIVE,LGI_GROUPID)
SELECT 10,1,10 where NOT EXISTS (SELECT TOP 1 NULL FROM LOAN_GROUPING where LGI_GROUPID = 10)
GO
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT * FROM LOAN_GROUPING
)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
AND SOURCE.LG_LOANID = 10
AND SOURCE.LG_ISACTIVE = 1
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 10
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE,LGI_GROUPID)
VALUES(Source.LGI_GROUPID,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1,Source.LGI_GROUPID);
GO
select * from LOAN_GROUP_INFO
Regards,
Mitesh OSwal
+918698619998
March 16, 2014 at 10:53 pm
In this case you can use the Union in source query so the data can be validated
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1
UNION SELECT 0 AS LGI_GROUPID
)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 10
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE)
VALUES(10,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1);
Regards,
Mitesh OSwal
+918698619998
March 16, 2014 at 11:01 pm
Thanks Mitesh
In my case, the LGI_GROUPID is a primary key with identity increment and therefore the code you supplied may not work. I modified my code as follows and am still not getting any inserts. In the statement below, LG_LOANID of value 22711 does not exist in the source or target table. I think I am doing this incorrect as for the MERGE to work, you need to have a record in Source or Target. I think what I need to use is IF EXISTS....ELSE.
Modified code is;
SET IDENTITY_INSERT LOAN_GROUP_INFO ON
DECLARE @NEXT_ID AS INT = (SELECT IDENT_CURRENT('LOAN_GROUP_INFO') + 1)
MERGE INTO LOAN_GROUP_INFO WITH (HOLDLOCK) AS TARGET
USING (SELECT *
FROM LOAN_GROUPING
WHERE LG_LOANID = 22711
AND LG_ISACTIVE = 1)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 12
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPID,LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE)
VALUES(@NEXT_ID,NULL,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1);
SET IDENTITY_INSERT LOAN_GROUP_INFO OFF
March 16, 2014 at 11:24 pm
IF Object_id('LOAN_GROUP_INFO') IS NULL
CREATE TABLE LOAN_GROUP_INFO
(
LGI_GROUPLVR INT ,LGI_GROUPEXPOSURE INT ,LGI_DATECREATED DATETIMEOFFSET(3),LGI_CREATEDBY INT,LGI_DATEUPDATED DATETIMEOFFSET(3) ,LGI_UPDATEDBY INT ,LGI_ISACTIVE BIT,
LGI_GROUPID INT IDENTITY (1,1)
)
GO
IF Object_id('LOAN_GROUPING') IS NULL
CREATE TABLE LOAN_GROUPING
(
LGI_GROUPLVR INT ,LGI_GROUPEXPOSURE INT ,LGI_DATECREATED DATETIMEOFFSET(3),LGI_CREATEDBY INT,LGI_DATEUPDATED DATETIMEOFFSET(3) ,LGI_UPDATEDBY INT ,LG_ISACTIVE BIT,
LG_LOANID INT,LGI_GROUPID INT
)
GO
SET IDENTITY_INSERT LOAN_GROUP_INFO ON
DECLARE @NEXT_ID AS INT = (SELECT IDENT_CURRENT('LOAN_GROUP_INFO') + 1)
MERGE INTO LOAN_GROUP_INFO WITH (HOLDLOCK) AS TARGET
USING (SELECT LGI_GROUPID
FROM LOAN_GROUPING
WHERE LG_LOANID = 22711
AND LG_ISACTIVE = 1
UNION
SELECT -1 AS LGI_GROUPID
)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 12
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPID,LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE)
VALUES(@NEXT_ID,NULL,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1);
SET IDENTITY_INSERT LOAN_GROUP_INFO OFF
Regards,
Mitesh OSwal
+918698619998
March 17, 2014 at 8:45 pm
Hi Mitesh
There is still an issue. The script is not correct as it will perform and insert every time it is executed, along with an update if found.
Any other ideas?
Vik
March 18, 2014 at 10:06 pm
This is because there is no record into the Source table, try to verify with the some records into the source record.
SET IDENTITY_INSERT LOAN_GROUP_INFO ON
DECLARE @NEXT_ID AS INT = (SELECT IDENT_CURRENT('LOAN_GROUP_INFO') + 1)
MERGE INTO LOAN_GROUP_INFO WITH (HOLDLOCK) AS TARGET
USING (SELECT LGI_GROUPID
FROM LOAN_GROUPING
WHERE LG_LOANID = 22711
AND LG_ISACTIVE = 1
UNION
SELECT @NEXT_ID AS LGI_GROUPID
where NOT exists(select top 1 null from LOAN_GROUP_INFO)
)
AS SOURCE
ON TARGET.LGI_GROUPID = SOURCE.LGI_GROUPID
WHEN MATCHED THEN
UPDATE SET LGI_GROUPLVR = 12
WHEN NOT MATCHED BY TARGET THEN
INSERT (LGI_GROUPID,LGI_GROUPLVR,LGI_GROUPEXPOSURE,LGI_DATECREATED,LGI_CREATEDBY,LGI_DATEUPDATED,LGI_UPDATEDBY,LGI_ISACTIVE)
VALUES(@NEXT_ID,NULL,NULL,CURRENT_TIMESTAMP,3,NULL,NULL,1);
SET IDENTITY_INSERT LOAN_GROUP_INFO OFF
Regards,
Mitesh OSwal
+918698619998
March 18, 2014 at 10:52 pm
That's the whole point. There might not be a record at all in the source server. I think in my case, the answer is IF EXIST...THEN....ELSE, rather than Merge
Merge would be more suitable for orphaned relationships and/or missing records
Vik
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply