CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
Romli Eko
Romli Eko

Reputation: 55

Insert to table then Conversion failed when converting the varchar value to data type int

I am trying to INSERT a row when the row does not exist. I have 2 tables, and I check 1 by 1, if the record does not exist in the second table, then do INSERT.

DECLARE @Counter int;
DECLARE @jumlah int;
DECLARE @namamerchant varchar(200);
DECLARE @namapemilik varchar(200);
DECLARE @alamat varchar(200);
SET @Counter = 1;

SELECT @jumlah = COUNT(*)
FROM ##TempMerchant_upload_id tmui;
SELECT @jumlah;
WHILE (@Counter <= @jumlah)
BEGIN

    SELECT @namamerchant = tmui.Nama_Merchant
    FROM ##TempMerchant_upload_id tmui
    WHERE tmui.id = @Counter;
    SELECT @alamat = tmui.Alamat_Pemilik
    FROM ##TempMerchant_upload_id tmui
    WHERE tmui.id = @Counter;
    SELECT @namapemilik = Nama_Pemilik
    FROM ##TempMerchant_upload_id
    WHERE ##TempMerchant_upload_id.id = @Counter;

    IF (SELECT COUNT(*)
        FROM merchant_negative_db_copy mndc
        WHERE mndc.Nama_Merchant = @namamerchant
          AND mndc.Alamat_Merchant = @alamat
          AND mndc.Nama_Pemilik = @namapemilik) = 0
    BEGIN
        SET @sql = 'INSERT INTO merchant_negative_db_copy SELECT *,tes, GETDATE(),0 FROM ##TempMerchant_upload_id where id = ''' + @Counter + '''';

        EXEC (@sql);
    END;

    SET @Counter = @Counter + 1;
END;

It's showing error like this when I run it:

Conversion failed when converting the varchar value 'INSERT INTO merchant_negative_db_copy SELECT *,tes, GETDATE(),0 FROM ##TempMerchant_upload_id where id = '' to data type int.

Any suggestion?

Upvotes: 0

Views: 387

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131374

I'm posting an answer because the accepted one is a bad answer. Normally, I'd wait for a description of the real problem, so a single query can be written without the loop, or the temporary table.

There's no reason to use dynamic SQL in the first place. @Counter can be used in a SQL query directly :

INSERT INTO merchant_negative_db_copy (ColA,ColB,...)
SELECT a,b,....,tes, GETDATE(),0 
FROM ##TempMerchant_upload_id 
WHERE id = @Counter

There's no reason to loop to check if a record already exists. One can use WHERE NOT EXISTS ( select 1 from target where ....) for that. Once that's done, the Counter variable isn't needed any more. Another option is to use a LEFT JOIN between source and target, and only insert records that have no match.

INSERT INTO merchant_negative_db_copy (ColA,ColB,...)
SELECT a,b,....,tes, GETDATE(),0 
FROM ##TempMerchant_upload_id source
WHERE NOT EXISTS 
(   SELECT 1
    FROM merchant_negative_db_copy mndc
    WHERE mndc.Nama_Merchant = source.Nama_Merchant
          AND mndc.Alamat_Merchant = source.Alamat_Merchant
          AND mndc.Nama_Pemilik = source.Nama_Pemilik
)

This will be at least N times faster than the loop, especially if the join fields Nama_Merchant, Alamat_Merchant and Nama_Pemilik fields are indexed in both tables.

Upvotes: 2

Related Questions