I want query to move the "IN" state records from tableB,TableC,TableD to TableA which has studid as identity column.
After moving the records to TableA delete the records with state ="IN" from tableB,TableC,TableD.

Below is my sceniario but i have more nearly 15 tables which the records to be moved and deleted.

Below is the Ex:

TableA
-------
Studid   Stuname     State
------   -------    -------
1          SaM	     IN
2          Mat	     IN


TableB
-----
Studid   Stuname     State
------   -------    -------
1          VIn	     IN
2          Jon	     IN
3          Pat       NY

TableC
-------
Studid   Stuname     State
------   -------    -------
1          Kim	     IN
2          Jim	     WA
3          Pat       NY

TableD
-------
Studid   Stuname     State
------   -------    -------
1          Rog	     IN
2          Ant	     IN
3          Put       NY

Expected output

TableA
-------
Studid   Stuname     State
------   -------    -------
1          SaM	     IN
2          Mat	     IN
3          VIn	     IN
4          Jon	     IN
5          Kim	     IN
6          Rog	     IN
7          Ant	     IN


TableB
-----
Studid   Stuname     State
------   -------    -------
3          Pat       NY

TableC
-------
Studid   Stuname     State
------   -------    -------
2          Jim	     WA
3          Pat       NY

TableD
-------
Studid   Stuname     State
------   -------    -------
3          Put       NY

INSERT INTO TableA (Stuname,State)
SELECT Stuname,State
FROM TableB
WHERE State='IN'
UNION ALL
SELECT Stuname,State
FROM TableC
WHERE State='IN'
UNION ALL
SELECT Stuname,State
FROM TableD
WHERE State='IN'

DELETE
FROM TableB 
WHERE State = 'IN'

DELETE
FROM TableC 
WHERE State = 'IN'

DELETE
FROM TableD 
WHERE State = 'IN'