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
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
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
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.
12 people are following this question.