SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
Mvs2k11
Mvs2k11
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 93
 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



Thanks for you help in advance..
Van Heghe Eddy
Van Heghe Eddy
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2142 Visits: 924
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113274 Visits: 18297
Please take a few minutes and read the first link in my signature about best practices. We need ddl, sample data and desired output. Your design does indeed look a bit suspect but we can't really help much without more details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search