SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 stored procedure help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/09/2013 :  23:07:47  Show Profile  Reply with Quote

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

Thanks for you help in advance..

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/10/2013 :  04:13:05  Show Profile  Reply with Quote

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'


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

Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/10/2013 :  10:29:39  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/11/2013 :  01:40:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000