Check and Insert if range between two numbers is not available in SQL Server

nabilabolo
 
on May 07, 2021 03:17 AM
737 Views

Hi, 

I have a two data range which from 101-200, 201-300.

My issue is when i want to add the date range 0 - 100 because currently i set the stored procedure to check the max record in endRange only.

How can i insert data 0-100 and also want to check if the range that i inserted is in between the data that has been added in databae?

I want to do this in stored procedure in SQL Server

declare @last int;
 
if exists(select @last = max(to) from temptable)
   /// do update
else
  //// do insert

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 10, 2021 01:52 AM

Hi nabilabolo,

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

SQL

DECLARE @first INT,@last INT
SET @first = 1
SET @last = 5

DECLARE @Counter INT
SET @Counter = @first

WHILE (@Counter <= @last)
BEGIN
	IF EXISTS(SELECT CustomerId FROM Customers WHERE CustomerId = @Counter)
	   -- Update 
	   UPDATE Customers SET Country = '' WHERE CustomerId = @Counter
	ELSE
	  -- Insert 
	  INSERT INTO Customers VALUES ('test','test')

	SET @Counter = @Counter + 1
	CONTINUE;
END

SELECT * FROM Customers

Output

CustomerId Name Country
1 John Hammond  
2 Mudassar Khan  
3 Suzanne Mathews  
4 Robert Schidner  
5 test test