quote:

---

Originally posted by digitalslavery

Hi everyone thanks for taking the time to look at my issue.

I have a stored proc that retrieves the values from 2 tables and joins them together to output a single dataset, not too uncommon. However this is where it gets interesting, I need to take the results from that stored proc and then use them as values to insert into a new table. I was hoping to use INSERT SELECT, however since I am using UNION to join up the results to get my single dataset I am not sure how to do this, also the table I want to put the results into does not have the same column names, since I need to add some additional data to this new table.

here is what I have so far.

CREATE PROCEDURE CE_Snapshots_Create
(
@tidmi_id int
)
AS
-- Take the data from the two deficiencies tables and insert the data into this snapshot table.
INSERT INTO CE_Snapshot
(
shortDesc
, deficiency
, partForService
, qty
, manHours
, pricing_milsys
, GETDATE() -- specify actual column names here
, @tidmi_id
)SELECT Pricing_Master.shortDescription, TI_Deficiencies.deficiency, Parts_Master.shortDescription, Parts_Master.partNumber, TI_DEF_END_ITEMS.qty,
Parts_Master.manHours, Parts_Master.pricing_milsys
FROM TI_DEF_END_ITEMS INNER JOIN
Pricing_Master ON TI_DEF_END_ITEMS.price_id = Pricing_Master.price_id INNER JOIN
TI_Deficiencies ON TI_DEF_END_ITEMS.ti_def_id = TI_Deficiencies.ti_def_id LEFT OUTER JOIN
Parts_Master ON TI_DEF_END_ITEMS.partMaster_id = Parts_Master.partMaster_id
WHERE (TI_DEF_END_ITEMS.tidmi_id = @tidmi_id)

/* SET NOCOUNT ON */
UNION

SELECT cItem, cDef, cPart, cPartNum, cQty, cManHours, cPrice
FROM TI_DEF_CUS_END_ITEMS
WHERE tidmi_id=@tidmi_id
/* SET NOCOUNT ON */
RETURN
SELECT * FROM CE_Snapshot where tidmi_id=@tidmi_id
RETURN

However when I try and save the sp I get an error saying
Invalid syntax near '('.

yAy

---

USE [table]
GO
/****** Object:  StoredProcedure [c2gear].[CE_Snapshots_Create]    Script Date: 04/18/2007 21:54:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [table].[CE_Snapshots_Create]
(@tidmi_id int)
AS
INSERT INTO CE_History
(shortDesc, deficiency, partForService, partNum, qty, manHours, pricing_01, createdDate, tidmi_id)
Select
	shortDescription, deficiency, longDescription, partNumber, qty, manHours, pricing_01, getdate(), @tidmi_id
From
	(SELECT 
		Pricing_Master.shortDescription, TI_Deficiencies.deficiency, Parts_Master.longDescription, Parts_Master.partNumber, TI_DEF_END_ITEMS.qty, 
		Parts_Master.manHours, Parts_Master.pricing_milsys
	FROM TI_DEF_END_ITEMS INNER JOIN
	Pricing_Master ON TI_DEF_END_ITEMS.price_id = Pricing_Master.price_id INNER JOIN
	TI_Deficiencies ON TI_DEF_END_ITEMS.ti_def_id = TI_Deficiencies.ti_def_id LEFT OUTER JOIN
	Parts_Master ON TI_DEF_END_ITEMS.partMaster_id = Parts_Master.partMaster_id
	WHERE (TI_DEF_END_ITEMS.tidmi_id = @tidmi_id)
	
	/* SET NOCOUNT ON */ 
	UNION
	
	SELECT cItem, cDef, cPart, cPartNum, cQty, cManHours, cPrice
	FROM TI_DEF_CUS_END_ITEMS
	WHERE tidmi_id=@tidmi_id
	/* SET NOCOUNT ON */ 
	) as Temp