If (City Id) does not exist in “CustomersInfo” table then create a new

Hamza Shah

I'm working on a project where I've made a Store procedure to load Bulk of customers data in CustomersInfo table. There is customer's City Id in a table. So i want when there is new city of customer then new city will added to City table. I'm not getting the logic to do that. can anyone please help me.

Here's my Store Procedure

alter procedure sp_AddCustomerBulk
@CompanyId int,
@jsonstring nvarchar(max)
as begin
select * from OPENJSON(@jsonstring)
WITH (MemberId int,CompanyId int,ShopId int, MemberName nvarchar(500), CityName nvarchar(50), CityAreaName nvarchar(50), Email nvarchar(50), PhoneResident nvarchar(25), PhoneOffice nvarchar(25), PhoneMobile nvarchar(25), Comments nvarchar(500) , MemberDOB datetime , MemberAnniversary datetime , CNIC nvarchar(20) , Gender nvarchar(10), CreditLimit numeric(18,4) , AllowCredit bit , MemberAddress nvarchar(500) , MemberTypeName nvarchar(50) , OpeningBalance money)
end

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
you can use Merge with City table and a temperory table to hold city id and city name
CREATE TABLE #tempCity
(
    Id int,
    CityName varchar(100),


)

insert into #tempCity values (select CityId,CityName from OpenJson(@Jsonstring))

MERGE City AS TARGET
USING #tempCity AS SOURCE
ON (TARGET.CityId = SOURCE.Id)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.CityName <> SOURCE.CityName
THEN UPDATE SET TARGET.CityName = SOURCE.CityName
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (Id, CityName) VALUES (SOURCE.CityId, SOURCE.CityName)
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE
THEN DELETE
Jignesh Kumar5y ago
Hi,

Please use this script,
BEGIN
   IF NOT EXISTS (SELECT CityName FROM City
                   WHERE CityId = @CityId

   BEGIN
      /// Insert Logic for city Table
       INSERT INTO City(CityName, CityAreaName)
       VALUES (@CityName, @CityAreaName)
   END
END