Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Inserting Results from SPROC into table Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:55 AM
Points: 31, Visits: 53
I was able to finally develop this SPROC that runs fine and returns the results but i would like to add an INSERT INTO clause that will load the values into a table that i could use for additional querying and joins.

Thoughts?

DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''


SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))

DECLARE
@sqlQuery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''

SET @sqlQuery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +

'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'

-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)

SET @finalQuery = N'SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @SQLQuery + ''')'

EXEC (@finalQuery)
Post #1468556
Posted Friday, June 28, 2013 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
as long as you declare a temp table before the EXEC command, any other queries would have that table in scope, and can insert into it (same for permanent tables)
so i think it's as easy as my edits below:

tstagliano (6/28/2013)
I was able to finally develop this SPROC that runs fine and returns the results but i would like to add an INSERT INTO clause that will load the values into a table that i could use for additional querying and joins.

Thoughts?

DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''


SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))

DECLARE
@sqlQuery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''

SET @sqlQuery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +

'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'

-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
CREATE TABLE #Results(ResultsId int identity(1,1) NOT NULL PRIMARY KEY,Part varchar(50),ActualCost money )
SET @finalQuery = N'INSERT INTO #Results (Part,ActualCost) SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @SQLQuery + ''')'

EXEC (@finalQuery)


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1468560
Posted Friday, June 28, 2013 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
BE VERY CAREFUL HERE, from what you posted I suspect that @Start_Date DATETIME and @Part_Type_MP are inbound parameters to your stored proc. You have created a huge hole here that is ripe for sql injection. You allow the user to pass you a string and then you execute it. This is the classic scenario for sql injection. Consider what would happen if somebody passed "'); drop table Purchasing_v_Line_Item_e;--". It would throw a syntax error for the first statement, then drop your purchasing table.

You should parameterize your query and use sp_executesql instead of a simple exec.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1468562
Posted Friday, June 28, 2013 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:55 AM
Points: 31, Visits: 53
Thanks for the information but i dont what the table to be a temporary table in the tempdb database. I need it to appear in the ConstarOLAP_PROPHIX_FactDB where the SPROC is running
Post #1468563
Posted Friday, June 28, 2013 8:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
tstagliano (6/28/2013)
Thanks for the information but i dont what the table to be a temporary table in the tempdb database. I need it to appear in the ConstarOLAP_PROPHIX_FactDB where the SPROC is running


The same logic works. Just create a permanent table and insert into it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1468568
Posted Friday, June 28, 2013 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
tstagliano (6/28/2013)
Thanks for the information but i dont what the table to be a temporary table in the tempdb database. I need it to appear in the ConstarOLAP_PROPHIX_FactDB where the SPROC is running


I specifically mentioned you could use permanent tables, but it might not have been clear enough.

Which target able you use is up to you, whether it's a temporary table a permanent table that already exists, my snippet was only to demonstrate how to insert the results of the openquery data into a table so it can be used in other queries.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1468569
Posted Friday, June 28, 2013 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:55 AM
Points: 31, Visits: 53
worked perfect. thank you
Post #1468574
Posted Monday, July 8, 2013 11:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:55 AM
Points: 31, Visits: 53
One last piece of advice i need. i am able to run this for the first time, but i need to delete the dbo.tblPlexActualPrice table everytime the SPROC runs so i can be re-created. I tried to add a DELETE FROM and INSERT INTO statement but it error out on the syntax where is creates the table.

USE [ConstarOLAP_PROPHIX_FactDb]
GO
/****** Object: StoredProcedure [dbo].[ActualPurchasePriceExport] Script Date: 07/08/2013 13:16:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================
-- Author: Tom Stagliano, Constar
-- Create date: June 25, 2013
-- Description: Prophix Actual Purchase Price Export SPROC
-- =======================================================

ALTER PROCEDURE [dbo].[ActualPurchasePriceExport]

AS
BEGIN

DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''


SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))

DECLARE
@sqlQuery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''

SET @sqlQuery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +

'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'

-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
CREATE TABLE dbo.tblPlexActualPrice(ResultsID int identity(1,1) NOT NULL PRIMARY KEY, Part varchar(50), ActualCost money)

SET @finalQuery = N'INSERT INTO dbo.tblPlexActualPrice (Part,ActualCost) SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @SQLQuery + ''')'

EXEC (@finalQuery)
END
Post #1471296
Posted Monday, July 8, 2013 11:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
tstagliano (7/8/2013)
One last piece of advice i need. i am able to run this for the first time, but i need to delete the dbo.tblPlexActualPrice table everytime the SPROC runs so i can be re-created. I tried to add a DELETE FROM and INSERT INTO statement but it error out on the syntax where is creates the table.


Now you are confusing me; if the table gets data inserted into it, and then you want to destroy the table, what is the procedure supposed to actually do then?
if it creates and destroys the data, it's really doing nothing; maybe you left out some other code? chances are the table needs to exist BEFORE the procedure runs, and so the procedure should not be creating anything...just inserting into your table; i think you might just have vestiges of a copy/paste from my first examples?


if you really need to destroy the table, use a temp table instead.
Create and insert into #tblPlexActualPrice in this case;

the usage of a temp table, instead of a permanent table, allows for full concurrency, meaning two or hundreds of people can call the same procedure at the same time, without one of them pausing or crashing.
a temp table, created in a procedure, is automatically destroyed when it goes out of scope(the procedure ends). a temp table with that name is unique to the session, so 100 people can create their own version of that table, with the same name, even with different schemas and data, than any of the other sessions.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471300
Posted Monday, July 8, 2013 1:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22, Visits: 147
I recommend taking a step back and trying to analyze your solution a little more in depth.

From what I can tell, based on your DB name, ConstarOLAP_PROPHIX_FactDb, you are creating some type of data warehouse. My hunch is this is a proc you want to run nightly and refresh a table in this DB. Presumably some reports down the line use this table for some calculations and it changes everyday.

To answer your specific question, the proc is attempting to create the table every time its run, you can't have multiple objects with the same name.

Syntactically, to achieve your solution, you would have to execute a drop table command prior to the create table command in your proc.

drop table tblPlexActualPrice

or you can create the table outside the proc and issue a truncate table command prior to your insert.

truncate table tblPlexActualPrice

I do not advocate either of the above, but it would work. A better solution would be to create and populate your tblPlexActualPrice table with pricing data constrained by a date value, to give you what actual pricing was on any given day. Your procedure should then only run inserts of daily changes to this table to keep it current. Any reports or queries will need to be constrained by the date value.

It would help us as well if you could tell us what you are ultimately trying to accomplish. Often times we get lost in the search for how to make something work vs. what is a better way...
Post #1471346
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse