CODEWITHSUNDEEP
c#asp.netsql-server
user606521
user606521

Reputation: 15424

Inserting results from one table to another with consecutive dates for each inserted row

In my asp.net App I have SqlDataSource with following insert query:

INSERT INTO Invitations (PatientId, PlanId)
SELECT TOP 10 Patients.Id, @PlanId
FROM Patients

So basically it gets 10 rows from Patients table and inserts them (certain values) to Invitations table. @PlanId is additional parameter passed to SqlDataSource.

What I need is to insert value into third column called Time which is of type time(0), not null. But I would like this value to be generated somehow like this:

for(i = 0; i < 10; ++i) Time = @InitialTime + i * Interval

So for example:

row 1: PatientId = 0; PlanId = 555; Time = 12:00
row 2: PatientId = 1; PlanId = 555; Time = 12:05
row 3: PatientId = 2; PlanId = 555; Time = 12:10

So I pass @InitialTime param and Time field is generated based on it with some interval...

Is it possible in MSSQL?

Upvotes: 0

Views: 403

Answers (2)

jpw
jpw

Reputation: 44871

Try this, it should work:

INSERT INTO Invitations (PatientId, PlanId, [Time])
SELECT TOP 10 
  Patients.Id, 
  @PlanId, 
  DATEADD(MINUTE,(ROW_NUMBER() OVER (ORDER BY Patients.Id) -1) * 5 ,@InitialTime ) AS [Time]
FROM Patients

Upvotes: 1

Parimal Raj
Parimal Raj

Reputation: 20575

I am not MS-SQL user at all,

This pseudo code may help

CREATE PROCEDURE InsertFromOtherTable
AS

DECLARE my_cur CURSOR FOR SELECT TOP 10 Patients.Id, @PlanId FROM Patients

DECLARE @startTime DATETIME, @interval INT

OPEN my_cur

while (read_one_row_from_cursor)
loop 
   -- insert the data into other table
   -- increment the timer
   -- continue loop to next row
end loop;

CLOSE my_cur

Upvotes: 0

Related Questions