SQL Server 2008 Insert with WHILE LOOP
Assuming that ID is an identity column:
INSERT INTO TheTable(HospitalID, Email, Description)
SELECT 32, Email, Description FROM TheTable
WHERE HospitalID <> 32
Try to avoid loops with SQL. Try to think in terms of sets instead.
While loop in SQL Server 2008 iterating through a date-range and then INSERT
SQL is a set based language and loops should be a last resort. So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops:
- Generate a set or sequence without loops – part 1
- Generate a set or sequence without loops – part 2
- Generate a set or sequence without loops – part 3
Part 3 is specifically relevant as it deals with dates.
Assuming you don't have a Calendar table you can use the stacked CTE method to generate a list of dates between your start and end dates.
DECLARE @StartDate DATE = '2015-01-01',
@EndDate DATE = GETDATE();
WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3;
I have skipped some detail on how this works as it is covered in the linked article, in essence it starts with a hard coded table of 10 rows, then joins this table with itself to get 100 rows (10 x 10) then joins this table of 100 rows to itself to get 10,000 rows (I stopped at this point but if you require further rows you can add further joins).
At each step the output is a single column called N with a value of 1 (to keep things simple). At the same time as defining how to generate 10,000 rows, I actually tell SQL Server to only generate the number needed by using TOP and the difference between your start and end date - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1). This avoids unnecessary work. I had to add 1 to the difference to ensure both dates were included.
Using the ranking function ROW_NUMBER() I add an incremental number to each of the rows generated, then I add this incremental number to your start date to get the list of dates. Since ROW_NUMBER() begins at 1, I need to deduct 1 from this to ensure the start date is included.
Then it would just be a case of excluding dates that already exist using NOT EXISTS. I have enclosed the results of the above query in their own CTE called dates:
DECLARE @StartDate DATE = '2015-01-01',
@EndDate DATE = GETDATE();
WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
( SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3
)
INSERT INTO MyTable ([TimeStamp])
SELECT Date
FROM Dates AS d
WHERE NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE d.Date = t.[TimeStamp])
Example on SQL Fiddle
If you were to create a calendar table (as described in the linked articles) then it may not be necessary to insert these extra rows, you could just generate your result set on the fly, something like:
SELECT [Timestamp] = c.Date,
t.[FruitType],
t.[NumOffered],
t.[NumTaken],
t.[NumAbandoned],
t.[NumSpoiled]
FROM dbo.Calendar AS c
LEFT JOIN dbo.MyTable AS t
ON t.[Timestamp] = c.[Date]
WHERE c.Date >= @StartDate
AND c.Date < @EndDate;
ADDENDUM
To answer your actual question your loop would be written as follows:
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate
WHILE (@CurrentDate < @EndDate)
BEGIN
IF NOT EXISTS (SELECT 1 FROM myTable WHERE myTable.Timestamp = @CurrentDate)
BEGIN
INSERT INTO MyTable ([Timestamp])
VALUES (@CurrentDate);
END
SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/
END
Example on SQL Fiddle
I do not advocate this approach, just because something is only being done once does not mean that I should not demonstrate the correct way of doing it.
FURTHER EXPLANATION
Since the stacked CTE method may have over complicated the set based approach I will simplify it by using the undocumented system table master..spt_values. If you run:
SELECT Number
FROM master..spt_values
WHERE Type = 'P';
You will see that you get all the numbers from 0 -2047.
Now if you run:
DECLARE @StartDate DATE = '2015-01-01',
@EndDate DATE = GETDATE();
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P';
You get all the dates from your start date to 2047 days in the future. If you add a further where clause you can limit this to dates before your end date:
DECLARE @StartDate DATE = '2015-01-01',
@EndDate DATE = GETDATE();
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate;
Now you have all the dates you need in a single set based query you can eliminate the rows that already exist in your table using NOT EXISTS
DECLARE @StartDate DATE = '2015-01-01',
@EndDate DATE = GETDATE();
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));
Finally you can insert these dates into your table using INSERT
DECLARE @StartDate DATE = '2015-01-01',
@EndDate DATE = GETDATE();
INSERT YourTable ([Timestamp])
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));
Hopefully this goes some way to showing that the set based approach is not only much more efficient it is simpler too.
Table INSERT using While loop
Quick and dirty (Change 1000 to the highest seat number possible. A Persistent Numbers table would help)
;WITH N(Number) AS (SELECT 1 AS Number UNION ALL SELECT Number + 1 FROM N WHERE Number < 1000)
SELECT TheTable.*, N.Number AS Seat
FROM TheTable
INNER JOIN N
ON ToSeat >= N.Number AND FromSeat <= N.Number
ORDER BY N.Number
OPTION(MAXRECURSION 1000)
SQL Stored Procedure with While loop and results inserted into temp table
there are a few things you need to check in the query:
- Is
@ENDPeriodan input variable because you have harcoded it's
values asSET @ENDPeriod = @CurrentPeriod-5make sure it's the
correct logic. CostCodeBalance' + @ORGCODE + '.FinPeriodNrif this an integer value then you can compare it asCostCodeBalance' + @ORGCODE + '.FinPeriodNr BETWEEN @CurrentPeriod AND @ENDPeriod.
so re-write your sproc as:
CREATE PROCEDURE [dbo].[SP_NominalRecord]
@ORGCODE VARCHAR(6),
@CurrentPeriod INT,
@ENDPeriod INT,
@Nominal INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX),
@END INT;
SET @ENDPeriod = @CurrentPeriod-5 -- Please verify if this logic is actually required
IF OBJECT_ID('tempdb..#TempResults')IS NOT NULL DROP TABLE #TempResults
--WHILE (@CurrentPeriod<> @ENDPeriod)--loop through until endperiod is reached
-- No need of while loop so commented
--BEGIN
CREATE TABLE #TempResults
(ID bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
CostCode INT,
CostDescr VARCHAR(50),
BankACType CHAR(2),
FinPeriodNr SMALLINT,
BalJan DECIMAL(12, 2),
BalFeb DECIMAL(12, 2),
BalMar DECIMAL(12, 2),
BalApr DECIMAL(12, 2),
BalMay DECIMAL(12, 2),
BalJun DECIMAL(12, 2),
BalJul DECIMAL(12, 2),
BalAug DECIMAL(12, 2),
BalSep DECIMAL(12, 2),
BalOct DECIMAL(12, 2),
BalNov DECIMAL(12, 2),
BalDec DECIMAL(12, 2),
Balance DECIMAL(12, 2))
SET @SQL = N'
SELECT CostCodes.CostCode,
CostCodes.CostDescr,
CostCodes.BankACType,
CostCodeBalance' + @ORGCODE + '.FinPeriodNr,
CostCodeBalance' + @ORGCODE + '.BalJan,
CostCodeBalance' + @ORGCODE + '.BalFeb,
CostCodeBalance' + @ORGCODE + '.BalMar,
CostCodeBalance' + @ORGCODE + '.BalApr,
CostCodeBalance' + @ORGCODE + '.BalMay,
CostCodeBalance' + @ORGCODE + '.BalJun,
CostCodeBalance' + @ORGCODE + '.BalJul,
CostCodeBalance' + @ORGCODE + '.BalAug,
CostCodeBalance' + @ORGCODE + '.BalSep,
CostCodeBalance' + @ORGCODE + '.BalOct,
CostCodeBalance' + @ORGCODE + '.BalNov,
CostCodeBalance' + @ORGCODE + '.BalDec,
Sum(CostCodeBalance' + @ORGCODE + '.BalJan
+ CostCodeBalance' + @ORGCODE + '.BalFeb +
CostCodeBalance' + @ORGCODE + '.BalMar +
CostCodeBalance' + @ORGCODE + '.BalApr +
CostCodeBalance' + @ORGCODE + '.BalMay +
CostCodeBalance' + @ORGCODE + '.BalJun +
CostCodeBalance' + @ORGCODE + '.BalJul +
CostCodeBalance' + @ORGCODE + '.BalAug +
CostCodeBalance' + @ORGCODE + '.BalSep +
CostCodeBalance' + @ORGCODE + '.BalOct +
CostCodeBalance' + @ORGCODE + '.BalNov +
CostCodeBalance' + @ORGCODE + '.BalDec) AS Balance
FROM CostCodeBalance' + @ORGCODE + '
INNER JOIN CostCodes ON CostCodeBalance' + @ORGCODE + '.CostCode
= CostCodes.CostCode
WHERE Costcodes.Costcode = @Nominal
AND CostCodeBalance' + @ORGCODE + '.FinPeriodNr
BETWEEN @CurrentPeriod AND @ENDPeriod
GROUP BY CostCodes.CostCode,
CostCodes.CostDescr,
CostCodes.BankACType,
CostCodeBalance' + @ORGCODE + '.FinPeriodNr,
CostCodeBalance' + @ORGCODE + '.BalJan,
CostCodeBalance' + @ORGCODE + '.BalFeb,
CostCodeBalance' + @ORGCODE + '.BalMar,
CostCodeBalance' + @ORGCODE + '.BalApr,
CostCodeBalance' + @ORGCODE + '.BalMay,
CostCodeBalance' + @ORGCODE + '.BalJun,
CostCodeBalance' + @ORGCODE + '.BalJul,
CostCodeBalance' + @ORGCODE + '.BalAug,
CostCodeBalance' + @ORGCODE + '.BalSep,
CostCodeBalance' + @ORGCODE + '.BalOct,
CostCodeBalance' + @ORGCODE + '.BalNov,
CostCodeBalance' + @ORGCODE + '.BalDec;';
--SET @CurrentPeriod = @CurrentPeriod -1; --subtrace current period by 1
--END
EXEC sp_executesql @sql, N'@ORGCODE VARCHAR(6),@CurrentPeriod INT, @ENDPeriod INT
, @NOMINAL INT', @ORGCODE,@CurrentPeriod, @ENDPeriod, @NOMINAL;
SELECT * FROM #TempResults --to select the results of the code above
END
SQL Server 2008 Using while loop to populate missing values
Bethony,
I've build the following based off your provided data and tested to success.
SQL Code:
USE SO_Tests
GO
--CREATE TABLE Patient_Table
-- (
-- id INT
-- ,status VARCHAR (200)
-- ,times VARCHAR(200)
-- )
TRUNCATE TABLE Patient_Table
INSERT INTO
Patient_Table
(ID, status, times)
VALUES
(123666,'Patient Arrive', '20180606 10:52')
,(123666,'Patient Arrive', '20180606 11:21')
,(123666,'Patient Arrive', '20180606 11:45')
,(123666,'Patient Prepped', '20180606 10:52')
,(123666,'Patient Prepped', '20180606 11:45')
,(123666,'Patient Leave', '20180606 10:52')
,(123666,'Patient Return', '20180606 10:55')
,(123666,'Patient Return', '20180606 12:30')
,(123666,'Patient Ready', '20180606 12:45')
,(123666,'Patient Discharged', '20180606 12:45')
SELECT * FROM Patient_Table
DECLARE @Count_Arrive INT = (SELECT COUNT(*) FROM Patient_Table WHERE status = 'Patient Arrive')
DECLARE @Count_Leave INT = (SELECT COUNT(*) FROM Patient_Table WHERE status = 'Patient Leave')
DECLARE @Count_Prepped INT = (SELECT COUNT(*) FROM Patient_Table WHERE status = 'Patient Prepped')
DECLARE @Count_Return INT = (SELECT COUNT(*) FROM Patient_Table WHERE status = 'Patient Return')
DECLARE @Count_Discharged INT = (SELECT COUNT(*) FROM Patient_Table WHERE status = 'Patient Discharged')
DECLARE @Count_Ready INT = (SELECT COUNT(*) FROM Patient_Table WHERE status = 'Patient Ready')
WHILE(@Count_Arrive <> @Count_Leave OR @Count_Arrive <> @Count_Prepped OR @Count_Arrive <> @Count_Return OR @Count_Arrive <> @Count_Discharged OR @Count_Arrive <> @Count_Ready)
BEGIN
IF (@Count_Arrive <> @Count_Leave)
BEGIN
INSERT INTO
Patient_Table
SELECT
123456,
'Patient Leave',
'No Data Provided'
SET @Count_Leave = @Count_Leave + 1
END
IF (@Count_Arrive <> @Count_Prepped)
BEGIN
INSERT INTO
Patient_Table
SELECT
123456,
'Patient Prepped',
'No Data Provided'
SET @Count_Prepped = @Count_Prepped + 1
END
IF (@Count_Arrive <> @Count_Return)
BEGIN
INSERT INTO
Patient_Table
SELECT
123456,
'Patient Return',
'No Data Provided'
SET @Count_Return = @Count_Return + 1
END
IF (@Count_Arrive <> @Count_Discharged)
BEGIN
INSERT INTO
Patient_Table
SELECT
123456,
'Patient Discharged',
'No Data Provided'
SET @Count_Discharged = @Count_Discharged + 1
END
IF (@Count_Arrive <> @Count_Ready)
BEGIN
INSERT INTO
Patient_Table
SELECT
123456,
'Patient Ready',
'No Data Provided'
SET @Count_Ready = @Count_Ready + 1
END
END
SELECT * FROM Patient_Table ORDER BY STATUS
Results: 
This covers the additional statuses where needed and applies dummy rows to the count of the arrival rows
UPDATE:
Added in the cursor to progress through the Patient ID's and added some additional handling to prevent adding lines that were never originally recorded and avoiding infinite loop if another status has more row than Patient_Arrive has per id
USE SO_Tests
GO
DECLARE @Patient_Table TABLE
(
id INT
,status VARCHAR (200)
,times VARCHAR(200)
)
--TRUNCATE TABLE Patient_Table
--INSERT INTO
-- Patient_Table
-- (ID, status, times)
--VALUES
-- (123666,'Patient Arrive', '20180606 10:52')
-- ,(123666,'Patient Arrive', '20180606 11:21')
-- ,(123666,'Patient Arrive', '20180606 11:45')
-- ,(123666,'Patient Prepped', '20180606 10:52')
-- ,(123666,'Patient Prepped', '20180606 11:45')
-- ,(123666,'Patient Leave', '20180606 10:52')
-- ,(123666,'Patient Return', '20180606 10:55')
-- ,(123666,'Patient Return', '20180606 12:30')
-- ,(123666,'Patient Ready', '20180606 12:45')
-- ,(123666,'Patient Discharged', '20180606 12:45')
INSERT INTO @Patient_Table SELECT * FROM Patient_Table
DECLARE @id INT
DECLARE Patient_Cursor CURSOR FOR
SELECT
id
FROM
@Patient_Table
OPEN Patient_Cursor
FETCH NEXT FROM Patient_Cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count_Arrive INT = (SELECT COUNT(*) FROM @Patient_Table WHERE status = 'Patient Arrive' AND id = @id)
DECLARE @Count_Leave INT = (SELECT COUNT(*) FROM @Patient_Table WHERE status = 'Patient Leave' AND id = @id)
DECLARE @Count_Prepped INT = (SELECT COUNT(*) FROM @Patient_Table WHERE status = 'Patient Prepped' AND id = @id)
DECLARE @Count_Return INT = (SELECT COUNT(*) FROM @Patient_Table WHERE status = 'Patient Return' AND id = @id)
DECLARE @Count_Discharged INT = (SELECT COUNT(*) FROM @Patient_Table WHERE status = 'Patient Discharged' AND id = @id)
DECLARE @Count_Ready INT = (SELECT COUNT(*) FROM @Patient_Table WHERE status = 'Patient Ready' AND id = @id)
WHILE((@Count_Arrive > @Count_Leave and @Count_Leave > 0) OR (@Count_Arrive > @Count_Prepped and @Count_Prepped > 0) OR (@Count_Arrive > @Count_Return and @Count_Return = 0) OR (@Count_Arrive > @Count_Discharged and @Count_Discharged > 0) OR (@Count_Arrive > @Count_Ready and @Count_Ready > 0))
BEGIN
print 'recursing'
print @count_arrive
print @count_leave
print @count_prepped
print @count_return
print @count_discharged
print @count_ready
IF (@Count_Arrive > @Count_Leave and @Count_Leave > 0)
BEGIN
INSERT INTO
@Patient_Table
SELECT
@id,
'Patient Leave',
'No Data Provided'
SET @Count_Leave = @Count_Leave + 1
END
IF (@Count_Arrive > @Count_Prepped and @Count_Prepped > 0)
BEGIN
INSERT INTO
@Patient_Table
SELECT
@id,
'Patient Prepped',
'No Data Provided'
SET @Count_Prepped = @Count_Prepped + 1
END
IF (@Count_Arrive > @Count_Return and @Count_Return > 0)
BEGIN
INSERT INTO
@Patient_Table
SELECT
@id,
'Patient Return',
'No Data Provided'
SET @Count_Return = @Count_Return + 1
END
IF (@Count_Arrive > @Count_Discharged and @Count_Discharged > 0)
BEGIN
INSERT INTO
@Patient_Table
SELECT
@id,
'Patient Discharged',
'No Data Provided'
SET @Count_Discharged = @Count_Discharged + 1
END
IF (@Count_Arrive > @Count_Ready and @Count_Ready > 0)
BEGIN
INSERT INTO
@Patient_Table
SELECT
@id,
'Patient Ready',
'No Data Provided'
SET @Count_Ready = @Count_Ready + 1
END
END
FETCH NEXT FROM Patient_Cursor INTO @id
END
SELECT * FROM Patient_Table
SELECT * FROM @Patient_Table
How to use WHILE LOOP to add value to list with condition, SQL Server 2008
You can also loop through your priorities and make sure the orderqty is updated accordingly. I'd do something like this:
DECLARE @allocatedqty int = 0
DECLARE @allocateqty int = 1
DECLARE @runningstock int = (SELECT stockqty FROM @stock)
WHILE @runningstock>=0
BEGIN
DECLARE @priority int
SELECT TOP 1 @priority = priority FROM @order ORDER BY priority ASC
WHILE @priority <= (SELECT MAX(priority) FROM @order)
BEGIN
DECLARE @orderqty int
SELECT @orderqty = orderqty - @allocatedqty FROM @order WHERE priority = @priority
SELECT @allocateqty = CASE WHEN @runningstock > 0 AND @orderqty > 0 THEN 1 ELSE 0 END
INSERT INTO @RESULT(priority,partcode,orderqty,runningstock,allocateqty)
SELECT @priority,
partcode,
CASE WHEN @orderqty >= 0 THEN @orderqty ELSE 0 END AS orderqty,
@runningstock,
@allocateqty
FROM @order
WHERE priority = @priority
SET @priority += 1
SET @runningstock = @runningstock - @allocateqty
END
SET @allocatedqty += 1
IF (@runningstock <= 0) BREAK
END
SELECT * FROM @Result
GO
How can INSERT INTO a table 300 times within a loop in SQL?
You may try it like this:
DECLARE @i int = 0
WHILE @i < 300
BEGIN
SET @i = @i + 1
/* your code*/
END
SQL Server 2008 insert into table using loop
I want to use the IDs I get from this query to insert in another table
Member which use ContaId as a foreign key.
You can use INSERT INTO .. SELECT instead of cursors and while loops like so:
INSERT INTO Member(ContaId)
SELECT TOP 1000 c.ContaId
FROM FastGroupe fg
INNER JOIN FastParticipant fp
ON fg.FastGroupeId = fp.FastGroupeId
INNER JOIN Participant p
ON fp.ParticipantId = p.ParticipantId
INNER JOIN Contact c
ON p.ContaId = c.ContaId
WHERE FastGroupeName like '%Group%'
Update: Try this:
INSERT INTO Member(ContaId, PromoId)
SELECT TOP 1000 c.ContaId, 91 AS PromoId
FROM FastGroupe fg
...
This will insert the same value 91 for the PromoId for all the 1000 records. And since the MemberId is set to be automatic, just ignore it in the columns' list and it will get an automatic value.
SQL Server 2008 - Alternatives to While Loop
You can use an ad-hoc Tally Table in concert with a CROSS APPLY
Declare @Date1 date = '20170101'
Declare @Date2 date = '20170110'
-- Insert Into tblCombinedData
Select B.*
From (Select Top (DateDiff(DD,@Date1,@Date2)+1) D=DateAdd(DD,-1+Row_Number() Over (Order By Number),@Date1) From master..spt_values) DT
Cross Apply (
SELECT a.SomeString
,b.SomeNumber
,dbo.fnDoSomeStuff(a.AKey,DT.D,DT.D) --<< Notice DT.D
,dbo.fnDoSomeMoreStuff(b.AKey,DT.D,DT.D) --<< Notice DT.D
FROM tblATable a
INNER JOIN tblAnotherTable b ON a.ID = b.ID
) B
If it helps with the visualization, the ad-hoc tally table looks like this
D
2017-01-01
2017-01-02
2017-01-03
2017-01-04
2017-01-05
2017-01-06
2017-01-07
2017-01-08
2017-01-09
2017-01-10
Related Topics
Autoincrement Fields on Databases Without Autoincrement Field
Calculate Time Difference (Only Working Hours) in Minutes Between Two Dates
Split a String into Rows Using Pure SQLite
How to Find Out Whether a Table Has Some Unique Columns
How to Specify 'Default' as a SQL Parameter Value in Ado.Net
How to Get Count() and Rows from One SQL Query in SQL Server
How to Convert the System Date Format to Dd/Mm/Yy in SQL Server 2008 R2
What Does "&" Means in This SQL Where Clause
How to List Active Connections on Postgresql
SQL Server Clustered Index - Order of Index Question
How to Label "Transitive Groups" with SQL
Best to Use * When Calling a Lot of Fields in MySQL
For Autoincrement Fields: Max(Id) VS Top 1 Id Order by Id Desc
How to to Read a Xml from a Url Using T-Sql