hi Mvs2k11,

I don't think there is any generic query available for what you want to do.

Besides there are duplicate studentnames in the different tables.

My best guess would be to create a cte to select the distinct names from the different tables, move them to your table A and aftwrerwards delete them from the source tables.

Example setup below

But i'm still curious, why do you have such a setup, this seems bad database design to me.

You would be far bether of rethinking your table design and act to the new scheme afterwards.

code to help you on your way

;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

Hope this helps you on your way.

Wkr,

Van Heghe Eddy