I want a generic 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

;with cteData as (
      select StuName from TableB where State ='IN'
      union
      select StuName from TableC where State ='IN'
      union
      ....
      -- Extra select statement here, i used Union (without ALL) so only distinct values will be in the output list
)
    Insert into TableA (Stuname,State)
    Select StuName,'IN' from cteData

   delete from TableB where State = 'IN'
   Delete from TableC where State ='IN'
    .....
    -- extra delete statement here for the other tables