Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MERGE Insert not working Expand / Collapse
Author
Message
Posted Sunday, March 16, 2014 8:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 10:50 PM
Points: 9, Visits: 21
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
Post #1551626
Posted Sunday, March 16, 2014 9:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 10:50 PM
Points: 9, Visits: 21
Please bare in mind that LGI_GROUPID is a primary key in the target table and a foreign key in the source table.
Post #1551630
Posted Sunday, March 16, 2014 10:32 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:32 AM
Points: 784, Visits: 632
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
Post #1551633
Posted Sunday, March 16, 2014 10:53 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:32 AM
Points: 784, Visits: 632
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
Post #1551636
Posted Sunday, March 16, 2014 11:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 10:50 PM
Points: 9, Visits: 21
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
Post #1551638
Posted Sunday, March 16, 2014 11:24 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:32 AM
Points: 784, Visits: 632
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
Post #1551641
Posted Monday, March 17, 2014 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 10:50 PM
Points: 9, Visits: 21
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
Post #1551979
Posted Tuesday, March 18, 2014 10:06 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:32 AM
Points: 784, Visits: 632
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
Post #1552451
Posted Tuesday, March 18, 2014 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 10:50 PM
Points: 9, Visits: 21
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
Post #1552455
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse