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 |
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2013-01-23 : 09:34:26
|
I am trying to do two queries and have the data end up in one table. Here is what I have so far.USE [MT]GO/****** Object: StoredProcedure [dbo].[PMPartsDue] Script Date: 01/23/2013 08:10:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[AllPartsDue](@zWODue Integer)ASBEGINSET NOCOUNT ONSELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, sparts.ComponentID, pmaint.TaskNum1, pmaint.TaskNum, pmaint.EquipmentID, pmparts.QtyNeededINTO partsreorderFROM pmparts INNER JOIN pmaint INNER JOIN pmtask ON pmaint.TaskNum = pmtask.TaskNum ON pmparts.TaskNum = pmaint.TaskNum INNER JOIN spartven INNER JOIN sparts ON spartven.ComponentID = sparts.ComponentID ON pmparts.ComponentNum = sparts.ComponentIDWHERE (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND (spartven.PrimaryVendor = 'True')UNIONSELECT sparts.ComponentID, sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.VendorID, spartven.Price, spartven.ComponentFROM sparts INNER JOIN spartven ON sparts.ComponentID = spartven.ComponentIDWHERE (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)SELECT * FROM partsreorderEND My problem is in one table I am returning more fields that in the other but all the fields are the same except for in the second query the extra fields (that are not being returned in this query) would just be blank. Is there a way to do that?I hope that makes sense.Thanks,Stacy |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-23 : 09:48:08
|
Create the partsreorder table first, and then insert into it. Also, when you do a union query, the top and bottom queries have to have the same number of fields (as you know!) and theyneed to be in the same order. Just use nulls or some default value where the columns differINSERT INTO table (a,b,c,d,e)Select a,b,c,d,e from table1unionselect a,null,null,d,e from table2JimEveryday I learn something that somebody else already knew |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2013-01-23 : 14:11:55
|
quote: Originally posted by jimf Create the partsreorder table first, and then insert into it. Also, when you do a union query, the top and bottom queries have to have the same number of fields (as you know!) and theyneed to be in the same order. Just use nulls or some default value where the columns differINSERT INTO table (a,b,c,d,e)Select a,b,c,d,e from table1unionselect a,null,null,d,e from table2JimEveryday I learn something that somebody else already knew
Thanks Jim for your reply. This is what I ended up with.USE [MT]GO/****** Object: StoredProcedure [dbo].[PMPartsDue] Script Date: 01/23/2013 08:10:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[AllPartsDue](@zWODue Integer)ASBEGINSET NOCOUNT ONINSERT INTO partsreorder (QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, ComponentID, TaskNum1, TaskNum, EquipmentID, QtyNeeded)SELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, sparts.ComponentID, pmaint.TaskNum1, pmaint.TaskNum, pmaint.EquipmentID, pmparts.QtyNeededFROM pmparts INNER JOIN pmaint INNER JOIN pmtask ON pmaint.TaskNum = pmtask.TaskNum ON pmparts.TaskNum = pmaint.TaskNum INNER JOIN spartven INNER JOIN sparts ON spartven.ComponentID = sparts.ComponentID ON pmparts.ComponentNum = sparts.ComponentIDWHERE (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND (spartven.PrimaryVendor = 'True')UNIONSELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, spartven.ComponentID, NULL, NULL, NULL, NULLFROM sparts INNER JOIN spartven ON sparts.ComponentID = spartven.ComponentIDWHERE (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)SELECT * FROM partsreorderEND It seems to be working fine except that if both queries are returning the same component# then I am getting both in the table which causes a constraint. Not sure exactly how to handle this. Is there a way when I do the 2nd query to check if any of the comp#'s are already in that partsreorder table?Thanks for you help.Stacy |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-24 : 07:20:42
|
You can't check the 2 part to see if it's already in the table as part of a union query. You'd have to separate it out and make it it's own insert -- this shouldn't affect performance at all. INSERT INTO partsreorderSELECT <STUFF>FROM <tables>WHERE NOT EXISTS (select * from partreorder po where po.Component = <tables>.componentJimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 01:29:30
|
for that isnt it enough to add NOT EXISTS conditions based on pmparts and partsreorder table to look for components if you want to do it inline in same UNION query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|