CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Pooveshin
Pooveshin

Reputation: 213

Error with select in Stored Procedure SQL Server

I am writing a stored procedure to be called when a button (Copy) is clicked on my front end application, I seem to be having a problem with the select statement after the #TestData table. Can some please help by pointing out the error.

Create Procedure CopyRates
@Period_id INT
AS
BEGIN

CREATE TABLE #TestData (Period_id int, Evo_StockLink int, Evo_itemCode    varchar(50), Evo_Description varchar(50), UnitRate float, MinRate float, RateBeforeSevenDays float, RateAfterSevenDays float)

INSERT INTO #TestData (Period_id, Evo_StockLink, Evo_itemCode ,Evo_Description, UnitRate, MinRate, RateBeforeSevenDays, RateAfterSevenDays)
VALUES
SELECT  Period_id, Evo_StockLink, Evo_itemCode ,Evo_Description, UnitRate, MinRate, RateBeforeSevenDays, RateAfterSevenDays

From [ZS_CS_EVO_Integration].[dbo].[rates]

Where Period_id = @Period_id

INSERT INTO rates(Period_id, Evo_StockLink, Evo_itemCode ,Evo_Description, UnitRate, MinRate, RateBeforeSevenDays, RateAfterSevenDays)
   SELECT Period_id +1, Evo_StockLink, Evo_itemCode ,Evo_Description, UnitRate, MinRate, RateBeforeSevenDays, RateAfterSevenDays
   FROM #TestData
END

Upvotes: 3

Views: 260

Answers (2)

JNR_Prog123
JNR_Prog123

Reputation: 133

You can try this:

SELECT 
* 
INTO temp_Rates
FROM rates 
WHERE Period_Id = (SELECT TOP 1 Period_Id FROM rates ORDER BY Period_Id DESC)

INSERT INTO rates 
SELECT Period_id +1, 
Evo_StockLink, 
Evo_itemCode,
Evo_Description, 
UnitRate, 
MinRate, 
RateBeforeSevenDays, 
RateAfterSevenDays
FROM temp_Rates

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

INSERT INTO #TestData (Period_id, Evo_StockLink, Evo_itemCode ,Evo_Description, UnitRate, MinRate, RateBeforeSevenDays, RateAfterSevenDays)
-- remove this**VALUES** 
SELECT  Period_id, Evo_StockLink, Evo_itemCode ,Evo_Description, UnitRate, MinRate, RateBeforeSevenDays, RateAfterSevenDays

From [ZS_CS_EVO_Integration].[dbo].[rates]

actually you don't need the #TestData temp table, you can insert direclty into rates table from [ZS_CS_EVO_Integration].[dbo].[rates]

Upvotes: 3

Related Questions