March 9, 2013 at 9:20 pm
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..
March 10, 2013 at 4:22 am
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
March 11, 2013 at 8:20 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply