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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 stored procedure help

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 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
[/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,State
FROM TableB
WHERE State='IN'
UNION ALL
SELECT Stuname,State
FROM TableC
WHERE State='IN'
UNION ALL
SELECT Stuname,State
FROM TableD
WHERE State='IN'

DELETE
FROM TableB
WHERE State = 'IN'

DELETE
FROM TableC
WHERE State = 'IN'

DELETE
FROM TableD
WHERE State = 'IN'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-03-10 : 10:29:39
Thanks visakh

Is 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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -