CODEWITHSUNDEEP
sqlsql-servert-sql
jorame
jorame

Reputation: 2207

How can I make the code inside a WHILE LOOP in SQL execute only once per LOOP?

Question, how can I make the insert execute only 9 time? I say 9 because that's the value of @CN_COUNT. Instead this is executing 9*9. Any help will be really appreciate it. 

DECLARE @CN_COUNT INT
, @DATE VARCHAR(10) = CONVERT(VARCHAR(10), GETDATE(),101)
, @TIME VARCHAR(10) = CONVERT(VARCHAR(8), GETDATE(),114)


SELECT @CN_COUNT = COUNT(*) FROM CARTON_HEADER

DECLARE @CN INT
SET @CN = 1
WHILE (@CN  <= @CN_COUNT)
BEGIN
SET @CN = @CN + 1

INSERT INTO TM_CARTON(
    CN_NUMBER, CN_PICKTICKET, CN_LOAD_NUMBER, CN_SHIPMENT_NUMBER,         CN_PACKED_QTY, CN_TRACKING_NUMBER, 
    CN_ROUTE, CN_BOL, CN_MBOL, CN_PARCEL_NUMBER, CN_TRAILER_NUMBER, CN_CREATED_DATE, CN_CREATED_TIME)
SELECT 
    CN_NUMBER, CN_PICKTICKET, CN_LOAD_NUMBER, CN_SHIPMENT_NUMBER, CN_PACKED_QTY, CN_TRACKING_NUMBER, 
    CN_ROUTE, CN_BOL, CN_MBOL, CN_PARCEL_NUMBER, CN_TRAILER_NUMBER, @DATE, @TIME 
    FROM CARTON_HEADER WHERE CN_LOAD_NUMBER = '1000000002'

END
GO

Here is the example data:

  CN_NUMBER      CN_STATUS  CN_LOAD_NUMBER
  1001333311111 85  1000000002
  1001333311112 85  1000000002
  1001333311114 85  1000000002
  1001333311113 85  1000000002
  1001333311115 85  1000000002
  1001333311116 85  1000000002
  1001333311117 85  1000000002
  1001333311118 85  1000000002
  1001333311119 85  1000000002

The above data is on table A, I would like to get this copied to table B where CN_LOAD_NUMBER is something I declare in the statement

Sorry guys, I made this more complicated than what it was the solution is just a simple INSERT statement with NO loop. 

INSERT INTO TM_CARTON(
CN_NUMBER, CN_PICKTICKET, CN_LOAD_NUMBER, CN_SHIPMENT_NUMBER,         CN_PACKED_QTY, CN_TRACKING_NUMBER, 
CN_ROUTE, CN_BOL, CN_MBOL, CN_PARCEL_NUMBER, CN_TRAILER_NUMBER, CN_CREATED_DATE, CN_CREATED_TIME)
SELECT 
CN_NUMBER, CN_PICKTICKET, CN_LOAD_NUMBER, CN_SHIPMENT_NUMBER, CN_PACKED_QTY, CN_TRACKING_NUMBER, 
CN_ROUTE, CN_BOL, CN_MBOL, CN_PARCEL_NUMBER, CN_TRAILER_NUMBER, @DATE, @TIME 
FROM CARTON_HEADER WHERE CN_LOAD_NUMBER = '1000000002'

Upvotes: 0

Views: 1818

Answers (1)

dodexahedron
dodexahedron

Reputation: 4657

The code you posted will only execute 9 times. If you are getting more inserts than you think you should be, it is because that select statement is returning more than one row.

After the first loop, you will have duplicated the row. Thus, every successive iteration of the loop will return n+n rows, where n is the number of rows the previous iteration returned.

What, exactly, are you trying to do?

Upvotes: 4

Related Questions