question

San-5300 avatar image
0 Votes"
San-5300 asked MelissaMa-msft answered

Stroed Proc which take table backup of each week data



Hi Experts,
I am struggling to created Stored Procedure which take table backup of each week data and insert into new table with YYYY_MM_WW. Please let me know if any blog I can refer to or how it can be done.

Thanks in advance

Cheers
San

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @San-5300,

Could you please provide CREATE TABLE statements for your table together with INSERT statements with sample data? How about the expected data in your new table?

Is there any parameter in your procedure?

Will the new table named as YYYY_MM_WW or column in the new table named as YYYY_MM_WW ?

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @San-5300,

It is recommended to provide CREATE TABLE statements for your table together with INSERT statements with sample data.We also need to see the expected result of the sample after executing the procedure.

You could also refer below simple example and check whether it is a little helpful.

Step One: create one table with some sample data and create one backup table.

 create table mytable 
 (
 updatevalue varchar(10),
 updatedate datetime)
    
 insert into mytable values
 ('aaa','2021-07-10'),
 ('bbb','2021-08-01'),
 ('ccc','2021-08-10'),
 ('ddd','2021-08-22'),
 ('eee','2021-08-23'),
 ('fff','2021-08-25')
    
 create table mytablebackup
 (
 updatevalue varchar(10),
 updatedate datetime,
 YYYY_MM_WW varchar(20))

Step Two: create one procedure.

 create procedure usp_tablebackup(@date date)
 as
 begin
    
 insert into mytablebackup
 select *,replace(convert(varchar(7),updatedate,120),'-','_')+'_0'+
 cast(DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, updatedate), 0), updatedate) +1  as char(1)) 'YYYY_MM_WW'
 from mytable
 where updatedate between DATEADD(DAY, 1- DATEPART(DW, @date), @date) and DATEADD(DAY, 7- DATEPART(DW, @date), @date)
    
 end

Step Three: execute this procedure.

 exec usp_tablebackup '2021-08-25'

Step Four: validate the result of backup table.

 select * from mytablebackup

Output:

 updatevalue    updatedate    YYYY_MM_WW
 ddd    2021-08-22 00:00:00.000    2021_08_04
 eee    2021-08-23 00:00:00.000    2021_08_04
 fff    2021-08-25 00:00:00.000    2021_08_04

If you would like to insert the data into backup data at the same time, you could refer below statement.

 insert into mytablebackup
 select *,replace(convert(varchar(7),updatedate,120),'-','_')+'_0'+
 cast(DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, updatedate), 0), updatedate) +1  as char(1)) 'YYYY_MM_WW'
 from mytable

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.