CODEWITHSUNDEEP
sql-server
forme hasan
forme hasan

Reputation: 41

How can I Insert each row With getdate() + 20 second

How can I Insert row With getdate() + 20 second Insert each row + 20 seconds

Example

insert into Student (id, Name, Mar,cTime)  
       select  id , Name , Mar , getdate() from sende

Expected Output row + 20 seconds

| id| Name| Mar |   cTime             |
---------------------------------------
| 1 | ha1 | 100 | 2018-05-07 22:49:00 |
| 2 | ha2 | 200 | 2018-05-07 22:49:20 |
| 3 | ha3 | 300 | 2018-05-07 22:49:40 |
| 4 | ha4 | 400 | 2018-05-07 22:50:00 |
| 5 | ha5 | 500 | 2018-05-07 22:50:20 |

Upvotes: 0

Views: 440

Answers (5)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Similar to Sean answer, but instead of update just calculate the date before insert.

SQL DEMO

CREATE TABLE sende
    ([id] int, [Name] varchar(3), [Mar] int)
;

INSERT INTO sende
    ([id], [Name], [Mar])
VALUES
    (1, 'ha1', 100),
    (2, 'ha2', 200),
    (3, 'ha3', 300),
    (4, 'ha4', 400),
    (5, 'ha5', 500)
;

With cte as (
    SELECT *, row_number() over ( ORDER BY [id]) -1 as rn
    FROM sende
)    
SELECT *, DATEADD(ss, rn * 20, GETDATE()) as cTime
FROM cte

Upvotes: 1

S3S
S3S

Reputation: 25132

It doesn't really make sense to insert data this way, and you aren't going to be inserting it that way from an application. Also, you are wanting 20 seconds sequentially for each row. When you do this for thousands of rows, it'd get messy. So, why not handle it on the SELECT?

declare @Student table  
(  
   id int ,  
   Name varchar(50),  
   Mar int ,
   cTime datetime 
)  

insert into @Student (id, Name, Mar)  
values
(1,'ha1',100),  
(2,'ha2',200),  
(3,'ha3',300),  
(4,'ha4',400),
(5,'ha5',500) 


select
    id
    ,name
    ,Mar
    ,cTime = dateadd(second,id * 20,getdate())
from @Student

Or, update the table after you have inserted everything, if there are going to be more than the sample 5 rows.

update @Student set cTime = dateadd(second,id * 20,getdate())


select * from @Student

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

There are a number of ways to do this. Possibly using a tally table or some other logic during the insert. But here is another way. Just insert the data and then use ROW_NUMBER to update it. It will be dynamic in the number of rows and requires no hardcoding of values other than the original insert statement.

declare @Student table  
(  
   id int ,  
   Name varchar(50),  
   Mar int ,
   cTime datetime 
)  

;
insert into @Student (id, Name, Mar,cTime)  
    values(1, 'ha1', 100, getdate()),  
    (2, 'ha2', 200, getdate()),  
    (3, 'ha3', 300, getdate()),  
    (4, 'ha4', 400, getdate()),
    (5, 'ha5', 500, getdate())  

select * from @Student; --so you can see the original values

with MyCTE as
(
    select *
        , RowNum = ROW_NUMBER() over (order by id) - 1
    from @Student
)

update @Student
set cTime = dateadd(second, 20 * RowNum, c.cTime)
from MyCTE c;

select * from @Student; --every row is now 20 seconds greater than the row "before it"

Upvotes: 1

JuanR
JuanR

Reputation: 7803

You can do it the manual way:

SQL DEMO

DECLARE @date DATETIME
SET @date = GETDATE()

declare @Student table  
(  
   id int ,  
   Name varchar(50),  
   Mar int ,
   cTime datetime 
)  

insert into @Student (id, Name, Mar,cTime) values(1,'ha1',100, @date)
SET @date = DATEADD(ss, 20, @date)
insert into @Student (id, Name, Mar,cTime) values(2,'ha2',200, @date)
SET @date = DATEADD(ss, 20, @date)
insert into @Student (id, Name, Mar,cTime) values(3,'ha3',300, @date)
SET @date = DATEADD(ss, 20, @date)
insert into @Student (id, Name, Mar,cTime) values(4,'ha4',400, @date)
SET @date = DATEADD(ss, 20, @date)
insert into @Student (id, Name, Mar,cTime) values(5,'ha5',500, @date)
SET @date = DATEADD(ss, 20, @date)

SELECT * FROM @student

Upvotes: 1

benjamin moskovits
benjamin moskovits

Reputation: 5458

Just use the dateadd function:

declare @Student table  
(  
   id int ,  
   Name varchar(50),  
   Mar int ,
   cTime datetime 
)  

insert into @Student (id, Name, Mar,cTime)  
values(1,'ha1',100,getdate()),  
(2,'ha2',200, dateadd(s,20,getdate())),
(3,'ha3',300,dateadd(s,40,getdate())),  
(4,'ha4',400,dateadd(s,60,getdate())),
(5,'ha5',500,dateadd(s,80,getdate()))

select * from @Student

Upvotes: 1

Related Questions