Based on parameter value remove record from one Table and insert into another Table in SQL Server

Last Reply 6 days ago By AnandM

Posted 11 days ago

Hi! I have four table and separate product by each seller. When seller bought product then delete it’s for this seller and insert in other table. Input result for all tables:

ProductTable

Id           ProductName

1             Apple

2             Oil

3             Orange

4             Cucumber

5             Water

6             Bread

SellerTable

Id           Name

1             Firdavs

2             Farhod

SellerProductTable

Id           IdSeller IdProduct

1             1             1,2,3

2             2             4,5,6

CustomerProductTable is empty.

Id           IdSeller IdProduct

 

DECLARE @IdSeller INT, @IdProduct INT
SET @IdSeller = 1
SET @IdProduct = 2

DECLARE @ProductTable AS TABLE(Id INT IDENTITY(1,1),ProductName VARCHAR(50))
   
INSERT INTO @ProductTable VALUES('Apple')
INSERT INTO @ProductTable VALUES('Oil')
INSERT INTO @ProductTable VALUES('Orange')
INSERT INTO @ProductTable VALUES('Cucumber')
INSERT INTO @ProductTable VALUES('Water')
INSERT INTO @ProductTable VALUES('Bread')

DECLARE @SellerTable AS TABLE(Id INT IDENTITY(1,1),Name VARCHAR(50))
   
INSERT INTO @SellerTable VALUES('Firdavs')
INSERT INTO @SellerTable VALUES('Farhod')

DECLARE @SellerProductTable AS TABLE(Id INT IDENTITY(1,1),IdSeller INT,IdProduct VARCHAR(50))
   
INSERT INTO @SellerProductTable VALUES(1,'1,2,3')
INSERT INTO @SellerProductTable VALUES(2,'4,5,6')

DECLARE @CustomerProductTable AS TABLE(Id INT IDENTITY(1,1),IdSeller INT,IdProduct VARCHAR(50))

       IF EXISTS (SELECT IdSeller FROM @CustomerProductTable WHERE IdSeller = @IdSeller)
            BEGIN
              UPDATE @CustomerProductTable
               SET IdProduct = IdProduct + ',' + CONVERT(VARCHAR,@IdProduct)
              WHERE IdSeller = @IdSeller
            END
        ELSE
            BEGIN
               INSERT INTO @CustomerProductTable VALUES(@IdSeller,CONVERT(VARCHAR,@IdProduct))
            END
            
select * from @ProductTable
select * from @SellerTable
select * from @SellerProductTable
select * from @CustomerProductTable

After run query I want below result because seller bought oil:

SellerProductTable

Id           IdSeller IdProduct

1             1             1,3

2             2             4,5,6

CustomerProductTable

Id           IdSeller IdProduct

1             1             2

It’s output result.

Posted 6 days ago

Hi RPA,

You need to make use of Split Function for spliting the values by comma into table so that you can delete productId from the SellerProduct table

For Split Function refer below article.

Split and convert Comma Separated (Delimited) String to Table in SQL Server

Refer below sample query.

SQL

DECLARE @SellerProductTable AS TABLE (Id INT IDENTITY(1,1),IdSeller INT,IdProduct VARCHAR(50))    
INSERT INTO @SellerProductTable VALUES(1,'1,2,3')
INSERT INTO @SellerProductTable VALUES(2,'4,5,6')
 
DECLARE @CustomerProductTable AS TABLE(Id INT IDENTITY(1,1),IdSeller INT,IdProduct VARCHAR(50))

DECLARE @IdSeller INT, @IdProduct INT,@SellerIdProduct VARCHAR(50)
SET @IdSeller = 1
SET @IdProduct = 2

CREATE TABLE #Temp(Item VARCHAR(50))
INSERT INTO #Temp
SELECT Item FROM dbo.SplitString((SELECT IdProduct FROM @SellerProductTable WHERE IdSeller = @IdSeller ),',')
 
IF EXISTS (SELECT IdSeller FROM @CustomerProductTable WHERE IdSeller = @IdSeller)       
	BEGIN
		UPDATE @CustomerProductTable
        SET IdProduct = IdProduct + ',' + CONVERT(VARCHAR,@IdProduct)
		WHERE IdSeller = @IdSeller			
			
		DELETE FROM #Temp
		WHERE Item = @IdProduct
			
		UPDATE @SellerProductTable
		SET IdProduct = (SELECT STUFF((SELECT ', ' + CAST(Item AS VARCHAR(10)) [text()]
						 FROM #Temp 										 
					     FOR XML PATH(''), TYPE)
		     	         .value('.','NVARCHAR(MAX)'),1,2,' '))
		WHERE IdSeller = @IdSeller		         										
    END
ELSE
    BEGIN
       INSERT INTO @CustomerProductTable VALUES(@IdSeller,CONVERT(VARCHAR,@IdProduct))
       DELETE FROM #Temp
	   WHERE Item = @IdProduct

       UPDATE @SellerProductTable
	   SET IdProduct = (SELECT STUFF((SELECT ', ' + CAST(Item AS VARCHAR(10)) [text()]
	                    FROM #Temp 										 
					    FOR XML PATH(''), TYPE)
					    .value('.','NVARCHAR(MAX)'),1,2,' '))
	   WHERE IdSeller = @IdSeller		         		
    END
    
SELECT * FROM @SellerProductTable
SELECT * FROM @CustomerProductTable
DROP TABLE #Temp  

 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html