Solved

How to insert multiple records from a select query result?

Posted on 2009-05-12
2
183 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:techques
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 50 total points
ID: 24371552
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24371739
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question