November 15, 2014 at 11:46 pm
so I am working on this one timer and I am either too tired or struggling on how to pass multiple integers into the script. It parses fine but will it act as if i want to if it runs.
going through each ProductID updating with new one created.
DECLARE @ProductID INT
DECLARE@NewProductID INT
BEGIN
SET @ProductID = ',2674,2711,2756,2818,2845,2889,2891,2920,3623,3768,3866,3878,3889,4084,4091,4113,4742,4753,5310,5313,5505
,5512
,5542
,5569
,5620
,5781
,5926
,6012
,6082
,6090
,6091
,6117
,6118
,6207
,6226
,6233
,6298
,6316
,6383
,6477
,6523
,6589
,6601
,6715
,6728
,6785
,7216
,7326
,8096
,8643
,9836
,10335
,10387
,10403
,10957
,11007
,11031
,11184
,11490
,11722
,11857
,12105
,12121
,12126
,12130
,12190
,12744
,13144
,13523
,13665
,13693
,14865
,15293
,15294
,15302
,15724
,15784
,16175
,16307
,16843
,16846
,16995
,17184
,18133
,18877
,18911
,19263
,19402
,19666
,19955
,20601
,21484
,22620
,22622
,23298
,24815
,24821
,25148
,26194
,26976
,27078
,27333
,27572
,27761
,27803
,27806
,27825
,27830
,29380
,29478
,30278
,31263
,31742
,31745
,32548
,32625
,32719
,33525
,33565
,34581
,34674
,34861
,34910
,35415
,35780
,35999
,37080
,38366
,38953
,39843,
'
SET @NewProductID = ',49645
,49646
,49647
,49648
,49649
,49650
,49651
,49652
,49653
,49654
,49655
,49656
,49657
,49658
,49659
,49660
,49661
,49662
,49663
,49664
,49665
,49666
,49667
,49668
,49669
,49670
,49671
,49672
,49673
,49674
,49675
,49676
,49677
,49678
,49679
,49680
,49681
,49682
,49683
,49684
,49685
,49686
,49687
,49688
,49689
,49690
,49691
,49692
,49693
,49694
,49695
,49696
,49697
,49698
,49699
,49700
,49701
,49702
,49703
,49704
,49705
,49706
,49707
,49708
,49709
,49710
,49711
,49712
,49713
,49714
,49715
,49716
,49717
,49718
,49719
,49720
,49721
,49722
,49723
,49724
,49725
,49726
,49727
,49728
,49729
,49730
,49731
,49732
,49733
,49734
,49735
,49736
,49737
,49738
,49739
,49740
,49741
,49742
,49743
,49744
,49745
,49746
,49747
,49748
,49749
,49750
,49751
,49752
,49753
,49754
,49755
,49756
,49757
,49758
,49759
,49760
,49761
,49762
,49763
,49764
,49765
,49766
,49767
,49768
,49769
,49770
,49771
,49772
,49773
,49774,
'
INSERT INTO [dbo].[Products_Base]
([ManufacturerID]
,[ProductPartNumber]
,[ProductSeries]
,[ProductSeriesLabel]
,[ProductName]
,[ProductDescription]
,[WeightMajor]
,[WeightMinor]
,[Length]
,[Width]
,[Height]
,[MSRP]
,[MSRPLastUpdate]
,[SearchPartNumber]
,[SearchSeries]
,[MinimumOrderQuantity]
,[MFGDAvailable]
,[MFGDPrice]
,[MFGDCost]
,[MFGDLeadLow]
,[MFGDLeadHigh]
,[MFGDLeadUnits]
,[MFGDMinQty]
,[WarehouseID])
( SELECT PB.ManufacturerID,'-'+ PB.ProductPartNumber + '-' , PB.ProductSeries, PB.ProductSeriesLabel, PB.ProductName, PB.ProductDescription,
PB.WeightMajor, PB.WeightMinor, PB.Length, PB.Width, PB.Height, PB.MSRP, PB.MSRPLastUpdate, PB.SearchPartNumber, PB.SearchSeries,
PB.MinimumOrderQuantity, PB.MFGDAvailable, PB.MFGDPrice, PB.MFGDCost, PB.MFGDLeadLow, PB.MFGDLeadHigh, PB.MFGDLeadUnits, PB.MFGDMinQty,
2
FROM Products_Base AS PB
WHERE (WarehouseID = 2) AND (ProductID = @ProductID))
UPDATE Products_Items
SET ProductID = @NewProductID
WHERE (ProductID = @ProductID) and ShipmentID IN ( 308,310,322,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,341,343,346,348,351,352,354,356,357,358,359,361,362,363,364,366,370,372,373,374,376)
UPDATE Products_Images
SET ProductID = @NewProductID
FROM Products_Images INNER JOIN
Logins ON Products_Images.TakenBy = Logins.UserName
where (ProductID = @ProductID) and (Logins.WarehouseID = 2)
insert into Products_Categories(ProductID,CategoryID)
(select @NewProductID ,CategoryID from Products_Categories where (ProductID = @ProductID))
UPDATE Products_Base
set WarehouseID = 1
where ProductID = @ProductID
END
Thanks for any assistance.
November 16, 2014 at 8:38 am
Quick suggestion for a solution, many ways of doing this though
😎
DECLARE @ProductID XML;
SET @ProductID = CONVERT(XML,REPLACE(
'
' + '2674,2711,2756,2818,2845,2889,2891,2920,3623,3768,3866,3878,3889,4084,4091,4113,4742,4753,5310,5313,5505,5512,5542,5569,5620,5781,5926,6012,6082,6090,6091,6117,6118,6207,6226,6233,6298,6316,6383,6477,6523,6589,6601,6715,6728,6785,7216,7326,8096,8643,9836,10335,10387,10403,10957,11007,11031,11184,11490,11722,11857,12105,12121,12126,12130,12190,12744,13144,13523,13665,13693,14865,15293,15294,15302,15724,15784,16175,16307,16843,16846,16995,17184,18133,18877,18911,19263,19402,19666,19955,20601,21484,22620,22622,23298,24815,24821,25148,26194,26976,27078,27333,27572,27761,27803,27806,27825,27830,29380,29478,30278,31263,31742,31745,32548,32625,32719,33525,33565,34581,34674,34861,34910,35415,35780,35999,37080,38366,38953,39843',',','
') + '',0);
;WITH PRODUCT_IDS AS
(
SELECT
P.DATA.value('.[1]','INT') AS ProductID
FROM @ProductID.nodes('P') AS P(DATA)
)
-- Later in the query
.......
FROM Products_Base AS PB
INNER JOIN PRODUCT_IDS PID
ON PB.ProductID = PID.ProductID
WHERE (WarehouseID = 2) ;
November 16, 2014 at 1:05 pm
Need some more help setting this up. Can someone help me?
November 16, 2014 at 5:21 pm
Eirikur Eiriksson (11/16/2014)
Quick suggestion for a solution, many ways of doing this though😎
DECLARE @ProductID XML;SET @ProductID = CONVERT(XML,REPLACE(
'
' + '2674,2711,2756,2818,2845,2889,2891,2920,3623,3768,3866,3878,3889,4084,4091,4113,4742,4753,5310,5313,5505,5512,5542,5569,5620,5781,5926,6012,6082,6090,6091,6117,6118,6207,6226,6233,6298,6316,6383,6477,6523,6589,6601,6715,6728,6785,7216,7326,8096,8643,9836,10335,10387,10403,10957,11007,11031,11184,11490,11722,11857,12105,12121,12126,12130,12190,12744,13144,13523,13665,13693,14865,15293,15294,15302,15724,15784,16175,16307,16843,16846,16995,17184,18133,18877,18911,19263,19402,19666,19955,20601,21484,22620,22622,23298,24815,24821,25148,26194,26976,27078,27333,27572,27761,27803,27806,27825,27830,29380,29478,30278,31263,31742,31745,32548,32625,32719,33525,33565,34581,34674,34861,34910,35415,35780,35999,37080,38366,38953,39843',',','
') + '',0);
;WITH PRODUCT_IDS AS
(
SELECT
P.DATA.value('.[1]','INT') AS ProductID
FROM @ProductID.nodes('P') AS P(DATA)
)
-- Later in the query
.......
FROM Products_Base AS PB
INNER JOIN PRODUCT_IDS PID
ON PB.ProductID = PID.ProductID
WHERE (WarehouseID = 2) ;
I'm curious... Why doesn't the following work?
DECLARE @ProductID XML;
SET @ProductID = CONVERT(XML,REPLACE(
'
' + '2674,2711,2756,2818,2845,2889,2891,2920,3623,3768,3866,3878,3889,4084,4091,4113,4742,4753,5310,5313,5505,5512,5542,5569,5620,5781,5926,6012,6082,6090,6091,6117,6118,6207,6226,6233,6298,6316,6383,6477,6523,6589,6601,6715,6728,6785,7216,7326,8096,8643,9836,10335,10387,10403,10957,11007,11031,11184,11490,11722,11857,12105,12121,12126,12130,12190,12744,13144,13523,13665,13693,14865,15293,15294,15302,15724,15784,16175,16307,16843,16846,16995,17184,18133,18877,18911,19263,19402,19666,19955,20601,21484,22620,22622,23298,24815,24821,25148,26194,26976,27078,27333,27572,27761,27803,27806,27825,27830,29380,29478,30278,31263,31742,31745,32548,32625,32719,33525,33565,34581,34674,34861,34910,35415,35780,35999,37080,38366,38953,39843',',','
') + '',0);
;WITH PRODUCT_IDS AS
(
SELECT
P.DATA.value('.[1]','INT') AS ProductID
FROM @ProductID.nodes('P') AS P(DATA)
)
SELECT * FROM PRODUCT_IDS
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2014 at 10:35 pm
I made the mistake of using an HTML tag name in the code, it disappears from the code after it is posted. Here is the code again with a different node name.
😎
SET @ProductID = CONVERT(XML,REPLACE(
'<X>' + '2674,2711,2756,2818,2845,2889,2891,2920,3623,3768,3866,3878,3889,4084,4091,4113,4742,4753,5310,5313,5505,5512,5542,5569,5620,5781,5926,6012,6082,6090,6091,6117,6118,6207,6226,6233,6298,6316,6383,6477,6523,6589,6601,6715,6728,6785,7216,7326,8096,8643,9836,10335,10387,10403,10957,11007,11031,11184,11490,11722,11857,12105,12121,12126,12130,12190,12744,13144,13523,13665,13693,14865,15293,15294,15302,15724,15784,16175,16307,16843,16846,16995,17184,18133,18877,18911,19263,19402,19666,19955,20601,21484,22620,22622,23298,24815,24821,25148,26194,26976,27078,27333,27572,27761,27803,27806,27825,27830,29380,29478,30278,31263,31742,31745,32548,32625,32719,33525,33565,34581,34674,34861,34910,35415,35780,35999,37080,38366,38953,39843',',','</X><X>') + '</X>',0);
;WITH PRODUCT_IDS AS
(
SELECT
X.DATA.value('.[1]','INT') AS ProductID
FROM @ProductID.nodes('*') AS X(DATA)
)
SELECT
PID.ProductID
FROM PRODUCT_IDS PID
November 17, 2014 at 7:09 am
Ah, thanks.
I guess I'm going to have to figure out something to make a DelimitedSplit8K that works for things > 8K characters because doing a REPLACE to build the XML is pretty expensive. It doesn't make much difference on a one-off, but it can make a very large difference if called tens of thousands of times in a 10 hour period.
If the integers are actually passed as XML, then the XML will be very fast.
For this particular example, DelimitedSplit8K will handle the job just fine. On my way to work so can't post the code. I'll post it tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2014 at 10:29 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply