Inserting multiple rows into a currency table

Transact-SQL
1

Hi

I have a requirement where I need to insert multiple rows to my currency table which consists of three columns (date, currency, ex-rate) and three months of data.

NOW I need to add six more months of data to the table where the currency and ex-rate columns are constant and only change is date column. I have to maintain 150 currencies on each date.

Your help will be appreciated.

2

Are you absolutely sure you want to add aprox. 27000 records to your table?
I would first investigate if it is possible to change your existing queries, handling the currency table as it is (possibly creating a function/procedure to look up the exchange rate at a given date).

3

Hi bitsmed

Yes, I have to insert 6 months data to my currency table where the only change will date.
I am looking for the best possible way to implement this whether to use stored proc or to use any variable.

see example of table.

Date Cur Ex-rate
01-01-2015 NZ 66
02-01-2015 US 100
03-01-2015 EUR 15
04-01-2015 AED 21
,
,
,
,
01-02-2015 NZ 66
02-02-2015 US 100
03-02-2015 EUR 15
04-02-2015 AED 21
,
,
,
31-03-2015 AED 21
01-04-2015 NZ 66
02-01-2015 US 100
03-01-2015 EUR 15
04-01-2015 AED 21
,
,
,
,
,
,
,
31-09-2015 AED 21

The table contains 3 months of data i.e from Jan 2015 to Mar 2015 with 3 columns Date Currency and Ex-rate.
Now i need to add 6 months of data i.e from April to Sep where the currency and ex-rate remains constant and the date varies.

Hope you have understood my query. Can I you help me on this.

Regards
ASk

4

Try this:

declare @fromdate date='20150401'
       ,@todate date='20150930'
       ;

with cte(thedate)
  as (select @fromdate as thedate
      union all
      select dateadd(dd,1,thedate) as thedate
        from cte
       where thedate<@todate
     )
insert into curency_rate([date],cur,[ex-rate])
 select b.thedate
       ,a.cur
       ,a.[ex-rate]
   from (select *
               ,row_number() over(partition by cur order by [date] desc) as rn
           from curency_rate
        ) as a
        cross join cte as b
  where a.rn=1
 option(maxrecursion 0)
;
1 Like
5

Hi

Thank you very much for the help. It worked for me. :grinning: