Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-03-09 : 23:07:47
|
[code]I want 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 IN2 Mat INTableB-----Studid Stuname State------ ------- -------1 VIn IN2 Jon IN3 Pat NYTableC-------Studid Stuname State------ ------- -------1 Kim IN2 Jim WA3 Pat NYTableD-------Studid Stuname State------ ------- -------1 Rog IN2 Ant IN3 Put NYExpected outputTableA-------Studid Stuname State------ ------- -------1 SaM IN2 Mat IN3 VIn IN4 Jon IN5 Kim IN6 Rog IN7 Ant INTableB-----Studid Stuname State------ ------- -------3 Pat NYTableC-------Studid Stuname State------ ------- -------2 Jim WA3 Pat NYTableD-------Studid Stuname State------ ------- -------3 Put NY[/code]Thanks for you help in advance.. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-10 : 04:13:05
|
[code]INSERT INTO TableA (Stuname,State)SELECT Stuname,StateFROM TableBWHERE State='IN'UNION ALLSELECT Stuname,StateFROM TableCWHERE State='IN'UNION ALLSELECT Stuname,StateFROM TableDWHERE State='IN'DELETEFROM TableB WHERE State = 'IN'DELETEFROM TableC WHERE State = 'IN'DELETEFROM TableD WHERE State = 'IN'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-03-10 : 10:29:39
|
Thanks visakhIs it possible to have generic storedprocedure where i can pass only the tablenames and state parameters values which should do the insert and delete task..I have like 15 to 20 tables..Thanks for yu help in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 01:40:36
|
that would require use of dynamic sql. can i ask why you need to make it generic? why are your tables not fixed?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|