Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Saturday, March 09, 2013 9:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 10:02 PM
Points: 60, Visits: 79
 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..
Post #1428954
Posted Sunday, March 10, 2013 4:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 4:56 AM
Points: 592, Visits: 753
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
Post #1428968
Posted Monday, March 11, 2013 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1429294
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse