I am relatively new to SQL and could use some help. I have mashed together snippets of code that have helped me get close to my objective, but not achieve it. The essentials of my original data looks like this:
- Product - Qty_Shipping
- P65238 - - - - - 4
- P65236 - - - - - 2
- P65240 - - - - - 3
The essentials of what I want to end up with would look like this:
- Product - UCC Code
- P65238 - - 001475010
- P65238 - - 001475011
- P65238 - - 001475012
- P65238 - - 001475013
- P65236 - - 001475014
- P65236 - - 001475015
- P65240 - - 001475016
- P65240 - - 001475017
- P65240 - - 001475018
A unique UCC Code (beginning at: 001475010) must be assigned per quantity of product, to every shipment. Below is the code that will create a number of unique UCCs base on the total quantity of products ordered.\[code\]CREATE TABLE #TEMP(UCCs INT) DECLARE @UCC INT ,@textXML XML ,@data NVARCHAR(MAX) ,@delimiter NVARCHAR(5) ,@ConCatString NVARCHAR(MAX) ,@LoopCounter INTSET @ConCatString = ''SET @UCC = 001475009 + 1SET @LoopCounter = (SELECT CAST(Q.DSPTOTQTY_0 AS INT)FROM x3v6prem.PILOTNEW.SDELIVERY QWHERE Q.SOHNUM_0 = 'SO1300259')IF @LoopCounter = 1 GOTO Skip_LoopContinue_Loop:SET @ConCatString = @ConCatString + CAST(@UCC AS NVARCHAR(MAX)) + ','SET @UCC = @UCC + 1SET @LoopCounter = @LoopCounter -1IF @LoopCounter > 1 GOTO Continue_LoopSkip_Loop:SET @ConCatString = @ConCatString + CAST(@UCC AS NVARCHAR(MAX)) SELECT @data = http://stackoverflow.com/questions/15593498/@ConCatString, @delimiter =','SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)INSERT INTO #TEMPSELECT T.split.value('.', 'nvarchar(max)') AS dataFROM @textXML.nodes('/d') T(split)SELECT * FROM #TEMPDROP TABLE #TEMP\[/code\]Next is the code that will duplicate the product ID a number of times based on the quantity of the product being shipped. In the end, the data will be used to create a shipping label that will placed on every box of product ordered and show a unique (barcode) number for each box.\[code\]CREATE TABLE #TEMP2(Products NVARCHAR(MAX)) DECLARE @Item2 NVARCHAR(MAX) ,@textXML2 XML ,@data2 NVARCHAR(MAX) ,@delimiter2 NVARCHAR(5) ,@ConCatString2 NVARCHAR(MAX) ,@LoopCounter2 INT ,@LastLine2 INTDECLARE CC CURSOR LOCAL FAST_FORWARD FORSELECT T.ITMREF_0FROM x3v6prem.PILOTNEW.SDELIVERYD TWHERE T.SOHNUM_0 = 'SO1300259'ORDER BY T.SDDLIN_0OPEN CCSET @ConCatString2 = ''SET @LastLine2 = (SELECT CAST(L.DSPTOTQTY_0 AS INT)FROM x3v6prem.PILOTNEW.SDELIVERY LWHERE L.SOHNUM_0 = 'SO1300259')GetNextItem2:FETCH NEXT FROM CC INTO @Item2IF @@FETCH_STATUS <> 0 GOTO EndBothLoops2SET @LoopCounter2 = (SELECT CAST(Q.QTY_0 AS INT)FROM x3v6prem.PILOTNEW.SDELIVERYD QWHERE Q.SOHNUM_0 = 'SO1300259' AND Q.ITMREF_0 = @Item2)Continue_Loop2:IF @LoopCounter2 = 0 AND @LastLine2 > 1 GOTO GetNextItem2SET @ConCatString2 = @ConCatString2 + CAST(@Item2 AS NVARCHAR(MAX)) + ','SET @LoopCounter2 = @LoopCounter2 - 1SET @LastLine2 = @LastLine2 - 1IF @LoopCounter2 > 0 AND @LastLine2 > 1 GOTO Continue_Loop2IF @LoopCounter2 = 0 AND @LastLine2 > 1 GOTO GetNextItem2SET @ConCatString2 = @ConCatString2 + CAST(@Item2 AS NVARCHAR(MAX))EndBothLoops2:CLOSE CCDEALLOCATE CCSELECT @data2 = @ConCatString2 ,@delimiter2 = ','SELECT @textXML2 = CAST('<d>' + REPLACE(@data2, @delimiter2, '</d><d>') + '</d>' AS XML)INSERT INTO #TEMP2SELECT T.split.value('.', 'nvarchar(max)') AS dataFROM @textXML2.nodes('/d') T(split)SELECT * FROM #TEMP2DROP TABLE #TEMP2\[/code\]If I could simply figure out how to get the output that I have put into two separate tables into one table my problems would be solved and I could (I believe) manage the rest. I have no real experience with the XML code that I am using so I cannot discern how to insert multiple columns into the table(s) being created.
Other smaller issues: I cannot seem to format the UCC values in a way where they will keep their leading zeros
