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_N o 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_N o = 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
-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_N o 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_N o = 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
Comment