sql loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql loop

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-09-25 : 04:48:30

Hi i have a sql loop query which i have working in asp fine, i have altered it to try and get it working as a stored procedure.

but i am not sure what the syntax is.

can someone help please.

many thanks



DECLARE
	CURSOR GetWebOrder_cur IS
	SELECT O_R_ID, O_Name, O_Add_1, O_DB_Code, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_Instore, O_STC_Code, O_ID 
	FROM [newserver].dbo.X_TBL_ORDER 
	WHERE NewOrder = 0

BEGIN
   FOR GetWebOrder_rec IN GetWebOrder_cur LOOP
     
		-- SET ALL FIELDS
		set R_ID34 = GetWebOrder_cur("O_R_ID")
		set R_Name = GetWebOrder_cur("O_Name")
		set R_Contact = GetWebOrder_cur("O_Add_1")
		set R_Code = GetWebOrder_cur("O_DB_Code")
		set R_Add_1 = GetWebOrder_cur("O_Add_2")
		set R_Add_2 = GetWebOrder_cur("O_Add_3")
		set R_Add_3 = GetWebOrder_cur("O_Add_4")
		set R_Add_4 = GetWebOrder_cur("O_Add_5")
		set R_Add_5  = GetWebOrder_cur("O_Add_6")
		set R_Add_6  = GetWebOrder_cur("O_Add_6")
		set R_PostCode = GetWebOrder_cur("O_PostCode")
		set R_CostCode = GetWebOrder_cur("O_CCode")
		set R_Delivery = GetWebOrder_cur("O_Service")
		set R_Instore = GetWebOrder_cur("O_Instore")
		set R_STCODES = GetWebOrder_cur("O_STC_Code")
		set WebOrderID = GetWebOrder_cur("O_ID")

		-- GET MAX ID
		SELECT Max(O_ID) AS MAXOID FROM dbo.X_TBL_ORDER

		-- INSERT ORDER INTO F4 ORDERS
		INSERT INTO dbo.X_TBL_ORDER
		(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
		VALUES
		(getdate(), R_ID34, R_Name, R_Code, R_Contact, R_Add_1, R_Add_2, R_Add_3, R_Add_4, R_Add_5, R_PostCode, R_CostCode, R_Delivery, R_Contact, R_Instore, R_STCODES)

		-- UPDATE VIRTUAL SERVER SET NewOrder = 1
		UPDATE [newserver].dbo.X_TBL_ORDER
		SET NewOrder = 1
		WHERE O_ID = WebOrderID

		-- SET CURSOR FOR ORDERLINES
		CURSOR orderlines_cur IS
		SELECT * 
		FROM [newserver].dbo.X_TBL_ORDER_LINE 
		WHERE OL_O_ID = @WebOrderID
		-- OPEN LOOP THROUGH ORDERLINES
		FOR orderlines_rec in orderlines_cur LOOP

		-- SET ORDERLINE FIELDS
		set B_St_Code = orderlines_cur("OL_St_Code")
		set B_Description = orderlines_cur("OL_Desc")
		set B_Qty = orderlines_cur("OL_Qty") 
		set B_dbcode = orderlines_cur("OL_DB_Code")

        -- INSERT INTO F4 ORDERLINES
		INSERT INTO dbo.X_TBL_ORDER_LINE
		(OL_O_ID, OL_St_Code, OL_Desc, OL_Qty, OL_Allocated, OL_Despatch, OL_DB_Code) 
		VALUES
		(B_preorderID, B_St_Code, B_Description, B_Qty, B_Qty, B_Qty, B_dbcode)
		-- CLOSE LOOP THROUGH ORDERLINES
		END LOOP;

  END LOOP;
END;

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 05:24:50

I can;t see what you need to use a loop, just insert the rows from [newserver].dbo.X_TBL_ORDER into dbo.X_TBL_ORDER (WHERE NewOrder = 0), then UPDATE [newserver].dbo.X_TBL_ORDER and SET NewOrder = 1 for all the orders above, then insert into dbo.X_TBL_ORDER_LINE all the values from [newserver].dbo.X_TBL_ORDER_LINE

each insert will be something like:


INSERT INTO dbo.X_TBL_ORDER
	(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, 
	O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
SELECT getdate(), O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, 
	O_PostCode, O_CCode, O_Service, O_Add_1, O_Instore, O_STC_Code)
FROM [newserver].dbo.X_TBL_ORDER 
WHERE NewOrder = 0

And put the lot in a Transaction so that you can rollback if any of them fail, otherwise (with your current code) you will get an INSERT INTO dbo.X_TBL_ORDER and no corresponding INSERT INTO dbo.X_TBL_ORDER_LINE if something goes wrong

Kristen

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-09-25 : 05:50:15

hi many thanks

but the reason why i need a loop is so i can get the maxid from the [oldserver].dbo.X_TBL_ORDER

so i can then insert that id number into the [oldserver].dbo.X_TBL_ORDERLINE

SO the process is

1. select all items from [NEWserver].dbo.X_TBL_ORDER.
2. for every item set NEWORDERID = [NEWserver].dbo.X_TBL_ORDER.O_R_ID
3. for every item set the MAXID from [OLDserver].dbo.X_TBL_ORDER

4. insert all items from the [NEWserver].dbo.X_TBL_ORDER into the [OLDserver].dbo.X_TBL_ORDER
5. select all items from [OLDserver].dbo.X_TBL_ORDERLINE WHERE OL_R_ID = NEWORDERID

6. insert all items from the [NEWserver].dbo.X_TBL_ORDERLINE into the [OLDserver].dbo.X_TBL_ORDERLINE WITH THE NEWORDERID to go into the column OL_R_ID

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 06:28:30

You definitely don't want to be inserting the rows one by one.

If you need to get an ID for each one either:

1. Set the ID column to IDENTITY so it is allocated automatically
2. Insert the Order into a temp table first, with an Identity. Find what the MAXID is and use that as a BASE, i.e. add BASe + IDENTITY from the temp table to get the actual ID number to insert.

Kristen

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-09-25 : 06:33:03

i dont think you understand what i need to do!!

i have 2 sql servers with the same tables

one is on my websever [NEWserver] and the other is on a local server [OLDserver]

so i need to loop through each record from the local server to get the maxid
then insert items from the webserver.

hope that makes it more clear

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 06:49:05

"so i need to loop through each record from the local server to get the maxid "

Why?

Lets say you get that before you start, and its, let say, 1234.

You have, say, 5 orders WHERE NewOrder = 0 which you want to insert.

Isn't the idea that they are allocated Order ID 1235, 1236, ... 1239 ??

Kristen

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-09-25 : 07:17:37

ok yes but then how do i get the next maxid

so the first one lets say is 1235 then i insert the orders and orderlines for that 1235

surley i need a loop to get the next id 1236?

also for every order i insert i need a new maxid for each one

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-25 : 09:40:24

oh, for the love of....

Listen, why don't you just use.....an IDENTITY Column?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-26 : 02:11:38

craigmacca,

Do you know, by any chance, what an IDENTITY column actually is???

And, what version of SQL Server are you using?

--Jeff Moden

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:04:18

"but then how do i get the next maxid"

Well this is a bit Micky-Mouse, but it will do for starters:


DECLARE @intMaxID int
SELECT	[O_Date] = getdate(), 
	[O_R_ID] = identity(int, 1, 1), 
	O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, 
	O_PostCode, O_CCode, O_Service, O_Add_1, O_Instore, O_STC_Code)
INTO #TEMP
FROM [newserver].dbo.X_TBL_ORDER 
WHERE NewOrder = 0

SELECT @intMaxID = Max(O_ID) FROM dbo.X_TBL_ORDER

INSERT INTO dbo.X_TBL_ORDER
	(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, 
	O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
SELECT O_Date, 
	O_R_ID + @intMaxID, 
	O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, 
	O_PostCode, O_CCode, O_Service, O_Add_1, O_Instore, O_STC_Code)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources