MATCHED DATA INSERTED INTO ANOTHER TABLE BY USING LOOP

CREATE PROC USP_LOOP

 AS

BEGIN

DECLARE @TBL_A TABLE(ID INT,NAME VARCHAR(10)) 

INSERT INTO @TBL_A VALUES(1,'A'),(2,'B'),(3,'C')

DECLARE @I INT,@X INT=1,@Y INT=1

SELECT @I=MAX(ID) FROM @TBL_A

DECLARE @TBL_B TABLE(ID INT,NAME VARCHAR(10))

INSERT INTO @TBL_B VALUES(1,'A'),(2,'B'),(3,'C')

DECLARE @J INT

SELECT @J=MAX(ID) FROM @TBL_B

DECLARE @TBL_C TABLE(ID INT,NAME VARCHAR(10))

DECLARE @NAME VARCHAR(10), @NAME1 VARCHAR(10)

WHILE(@X<=@I)

BEGIN

SELECT @NAME=NAME FROM @TBL_A WHERE ID=@X 

PRINT @NAME

WHILE(@Y<=@J)

BEGIN

SELECT @NAME1=NAME FROM @TBL_B WHERE ID=@Y 

    IF(@NAME=@NAME1)

BEGIN

INSERT INTO @TBL_C(ID,NAME) SELECT ID,NAME FROM @TBL_A WHERE ID=@X

BREAK

END

SET @Y=@Y+1

END

SET @Y=1

SET @X=@X+1

END

SELECT * FROM @TBL_A

SELECT * FROM @TBL_B

SELECT * FROM @TBL_C

END 

EXEC USP_LOOP

OTHER WAYS:

CREATE TABLE TBL_1(ID INT,NAME VARCHAR(30))

INSERT INTO TBL_1 VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')

SELECT * FROM TBL_1

CREATE TABLE TBL_2(ID INT,NAME VARCHAR(30))

INSERT INTO TBL_2 VALUES(1,'AAA'),(2,'B'),(3,'CC'),(4,'D')

CREATE TABLE TBL_MATCH(ID INT,NAME VARCHAR(30))

-MATCHED DATA BY USING CTE

;WITH X AS(SELECT A.ID,B.NAME FROM TBL_1 A INNER JOIN TBL_2 B ON A.NAME=B.NAME AND A.ID=B.ID )

INSERT INTO TBL_MATCH SELECT * FROM X 

UNMATCHED DATA

SELECT V.ID,V.NAME FROM TBL_1  A INNER JOIN TBL_2  B ON A.NAME<>B.NAME

CREATE TABLE TBL_UNMATCH(ID INT,NAME VARCHAR(30))

MATCHED AND UNMATCHED DATA BY USING PROCEDURE

CREATE PROC USP_MAT

AS 

BEGIN

INSERT INTO TBL_MATCH   SELECT * FROM TBL_1

   INTERSECT

   SELECT * FROM TBL_2

   

   INSERT INTO TBL_UNMATCH   SELECT * FROM TBL_1

   EXCEPT

   SELECT * FROM TBL_2

   

   

   END

   EXEC USP_MAT