How to insert multiple records from a select query result?

There are 30 exchanges reference rates each day needs to carry down to next day. I use the following SQL to select the yesterday's result and hope to insert them to the same table but with different date.

However, it just insert the last record of previous day.

How should I write the SP?


ALTER PROCEDURE [dbo].[SP_Update_Exchanges] 
@fordate datetime
AS
BEGIN
BEGIN TRAN TRAN_SP_Ex
Declare @currencyid int
Declare @currency nvarchar(50)
Declare @referencerate decimal(18,6)
Declare @buy decimal(18,6)
Declare @sell decimal(18,6)
 
SET NOCOUNT ON;
select * from exchanges where systemdate = @fordate
if @@rowcount <> 1
      begin
            SELECT @currencyid=currencyid, @currency=currency, 
            @referencerate=referencerate, @buy=buy, @sell=sell from exchanges 
            where systemdate = datediff(day,1,@fordate)
 
            insert into exchanges (currencyid,currency,referencerate,buy,sell,systemdate) 
            values (@currencyid,@currency,@referencerate,@buy,@sell,@fordate)
      end
IF @@ERROR <> 0
	ROLLBACK TRAN TRAN_SP_Ex
ELSE
	COMMIT TRAN TRAN_SP_Ex
END

Open in new window

techquesAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
Try this
ALTER PROCEDURE [dbo].[SP_Update_Exchanges] 
@fordate datetime
AS
BEGIN
BEGIN TRAN TRAN_SP_Ex
 
 
SET NOCOUNT ON;
                 insert into exchanges (currencyid,currency,referencerate,buy,sell,systemdate) 
             SELECT currencyid, currency, 
            referencerate, buy, sell,@fordate from exchanges 
            where systemdate = datediff(day,1,@fordate)
    
IF @@ERROR <> 0
	ROLLBACK TRAN TRAN_SP_Ex
ELSE
	COMMIT TRAN TRAN_SP_Ex
END

Open in new window

0
 
RiteshShahCommented:
this should work for you...



ALTER PROCEDURE [dbo].[SP_Update_Exchanges] 
@fordate datetime
AS
BEGIN
BEGIN TRAN TRAN_SP_Ex
 
 
SET NOCOUNT ON;
                 insert into exchanges (currencyid,currency,referencerate,buy,sell,systemdate) 
             SELECT currencyid, currency, 
            referencerate, buy, sell,@fordate from exchanges 
            where systemdate = dateadd(dd,-1,@fordate)
    
IF @@ERROR <> 0
        ROLLBACK TRAN TRAN_SP_Ex
ELSE
        COMMIT TRAN TRAN_SP_Ex
END

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.