Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

623 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
52326 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

623 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000