0

Unable to perform Merge with Insert statement (for an accounting process)

Table1 contains GRList for write off (based on date on Table3)

Table2 Contains All GR details (all information from 1-jan-2010 to till date)

Table3 contains Oldest Claim date (eg: 1-Apr-2018)

so from the above scenario Oldest Claim date (eg: 1-Apr-2018) gets picked up from Table 3 and then it searches in Table 2 for the GRs which is before the extracted date ( <= 1-apr-2018) and populates the record (from 1-jan-2010 to 31-mar-2018) in Table 1

Code Tried in SQL

MERGE Table1 As Target
Using (select column1, column2 From Table2 AS tbl2 INNER JOIN Table3 as tbl3
ON tbl2.column1 = tbl3.column1
WHERE
tbl2.column1 = tbl3.column1 AND tbl2.column2 = tbl3.column2) AS SOURCE

ON
(Target.Column1 = Source.Column1 AND Target.Column2 = Source.Column2  AND Target.Column5 <= Source.Column5 )

WHEN MATCHED AND 
Target.Column1 = Source.Column1 AND Target.Column2 = Source.Column2

THEN UPDATE SET Target.Column4='Updated'

WHEN NOT MATCHED BY TARGET 
THEN INSERT 
(Column1, Column2, Column3)
VALUES
(Source.Column1, Source.Column2, Source.Column3)

ERROR

Msg 248, Level 16, State 1, Line 23 The conversion of the nvarchar value '3000143371 ' overflowed an int column. The statement has been terminated.

Avinesh
  • 21
  • 5

1 Answers1

0

One of your Target table column data type is INT. You are trying to insert a value that is to big for data type INT Try changing your target column data type from INT to BIGINT.

user3298593
  • 115
  • 1
  • 13
  • I completely agree with you, but I tried updating only one column which is of nvarchar datatype in both the target and source tables and still getting the same error. – Avinesh Jul 03 '19 at 16:26