Inserting concatenated data with while construct

[GabeM]

I am trying to insert records from one table to another using the WHILE construct, I have managed to move all of the data with the exception of one concatenated field. I can't figure out how to concatenate 'GSA' and the @N into GSAOrderNo column in the TempWebOrders table. The @N is incrementing. If anyone can help, please assist. See code below:

-TIA

-Gabe

Use SMS
Declare @N as nvarchar(10)


SELECT @N = MAX (RIGHT(Order_no, 4))
FROM [Order]

USE SC
--Columns that will be used to loop through
--the rows of this table
DECLARE @NumberRecords int, @RowCount int
DECLARE @Order_No int, @System varchar(3), @clerk varchar(3), @custname nvarchar (152), @camp nvarchar(50), @bldg_no nvarchar(50), @phone_no nvarchar(50), @Online_Order_No float, @Webstatus varchar(4), @Orderway varchar(6), @Order_Date datetime, @Order_time datetime, @organization nvarchar(50), @apoc nvarchar(137), @custacct_no nvarchar(75), @card_exp_date nvarchar(50), @GSAOrderNo nvarchar(50)

--Insert the resultset we wan to loop through
--into the TempWebOrders table
INSERT INTO TempWebOrders (Order_No, [System], clerk, custname, camp, bldg_no, phone_no, Online_Order_No, Webstatus, Orderway, Order_Date, Order_time, organization, apoc, custacct_no, card_exp_date, GSAOrderNo)
SELECT Order_No, [System], clerk, custname, camp, bldg_no, phone_no, Online_Order_No, Webstatus, Orderway, Order_Date, Order_time, organization, apoc, custacct_no, card_exp_date, GSAOrderNo = @N
FROM WebOrders


--Get the number of records in the TempWebOrders table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

--loop through all records in the TempWebOrders table
--using the WHILE loop construct
WHILE @RowCount <= @NumberRecords

BEGIN

SET @N = @N + 1
SELECT @Order_No = Order_No, @System = [System], @clerk = clerk, @custname = custname, @camp = camp, @bldg_no = bldg_no, @phone_no = phone_no, @Online_Order_No = Online_Order_No, @Webstatus = Webstatus, @Orderway = Orderway, @Order_Date = Order_Date, @Order_time = Order_time, @organization = organization, @apoc = apoc, @custacct_no = custacct_no, @card_exp_date = card_exp_date
FROM TempWebOrders
WHERE RowID = @RowCount
SET @RowCount = @RowCount + 1
END

[ck9663]

Try using IDENTITY instead.

--- CK

[GabeM]

I don't understand by what you mean by Identity. What does the Identity have to do with the concatenated string?

[ck9663]

What will happen if this stored proc ran simultaneously? You can not always be sure with your @n + 1 value.

To address your concatenation, can't you convert the numeric to string and concatenate the two strings?

--- CK

[GabeM]

I will try to convert it, but I don't think it will work. Thanks for replying.

-Gabe