TSQL looping I need help!

I am still learning TSQL and have hit a big snag in coding for me.  I have been tasked to bring in data form a file that contains multiple lines to be concatenated into one long field of 1000 characters.

The issue is that I have no clear idea how to perform a loop in TSQL.   The file contains a purchase order field that I am thinking would be the field to break on in the Loop.   Even after reading the other questions regarding looping I am still lost.  I have tried to understand how to use WHILE in this case and still need some clarity.  Please excuse my lack of understanding. I need help.

Attached is a file (sample.txt) containing sample data of the file I need to pull the data from and concatenate into one single field.

Here is the SQL script at this time:

USE DmStagingVD

delete from [dbo].[DMFPURCHLINEENTITY2]
 insert into [dbo].[DMFPURCHLINEENTITY2]

 
SELECT  

			 ' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
	 		,' ' as [ACTIVITYNUMBER] 

--- Asset Info, not populating at this time.
			,' ' as [ASSETBOOKID]
			,' ' as [ASSETGROUP]
			,' ' as [ASSETID]

-- Barcode Information, not populating at this time.
			,' ' as [BARCODE]	
			,' ' as [BARCODETYPE]


			,' ' as [BLOCKED]
			,' ' as [CASETAGGING]
			,' ' as [CFOPTABLE_BR_CFOPID]
			,' ' as [COMPLETE]

-- ConfigId we need to populate ? --------------------------------------------------------------------
			,' ' as [CONFIGID]
			
----Confirmed info, not populat this time
			,0  as [CONFIRMEDDLV] -- datetime
			,0  as [CONFIRMEDTAXAMOUNT]
			,' ' as [CONFIRMEDTAXWRITECODE]


			,' ' as [COUNTYORIGDEST]
			,' ' as [COVREF]
			,' ' as [CREATEFIXEDASSET]

-- Cuurency code, USD
			,'USD' as [CURRENCYCODE]

-- Customer Information, not populating
			,' ' as [CUSTOMERREF]
			,' ' as [CUSTPURCHASEORDERFORMNUM]

-- Financial Dimension
			,AXSTRING AS [DEFAULTDIMENSION] 
 
-- Definition Group not populating right now
			,' ' as [DEFINITIONGROUP]

--- Delivery Address, this information is populataed by the migration process
			,' ' as [DELIVERYADDRESS]
			,' ' as [DELIVERYCITY]
			,' ' as [DELIVERYCOUNTRYREGIONID]
			,' ' as [DELIVERYCOUNTY]

	        ,[PECSDT] as [DELIVERYDATE] -- datetime
			,' ' as [DELIVERYLOCATIONROLE]

--- Delivery Address, this information is populataed by the migration process
			,' ' as [DELIVERYNAME]
			,' ' as [DELIVERYSTATE]
			,' ' as [DELIVERTYSTREET]
			,' ' as [DELIVERYTYPE]
			,' ' as [DELIVERYZIPCODE]


			,0   as [DEPRECIATIONSTARTDATE] -- datetime
			,0   as [DISCAMOUNT]
			,0   as [DISCPERCENT]
			,' ' as [EDITABLEINWORKFLOW]
			,' ' as [EXECUTIONID]

			,[POCATG] as [EXTERNALITEMID]
			,' ' as [GSTHSTTAXTYPE_CA]

-- Intercompany information, not populating
			,' ' as [INTERCOMPANYINVENTTRANSID]
			,' ' as [INTERCOMPANYORIGIN]

			,0   as [INTRASTATFULFILLMENTDATE_HU] -- datetime

---Inventory information, we only populate Location and Site IDs
			,' ' as [INVENTBATCHID]
			,' ' as [INVENTCOLORID]
			,0 as [INVENTINVOICENOW]
			,'TD'as [INVENTLOCATIONID]
			,0   as [INVENTRECEIVEDNOW]
			,' ' as [INVENTREFID]
			,' ' as [INVENTREFTRANSID]
			,' ' as [INVENTSERIALID]
			,'IND' as [INVENTSITEID]
			,' '  as [INVENTSIZEID]
			,' ' as [INVENTSTYLEID]

			,' ' as [ISDELETED]
			,' ' as [ISFINALIZED]
			,' ' as [ISINVOICEMATCHED]
			,' ' as [ISMODIFIED]
			,' ' as [ISPWP]
			,0   as [ISSELECTED]
			,' ' as [ITEMBOMID]
			,[PEPN] as [ITEMID]
			,' ' as [ITEMPBAID]
			,' ' as [ITEMROUTEID]
			,' ' as [ITEMTAGGING]
			,' ' as [LEDGERDIMENSION]
-- Line amount --------------------------------------------------  purchase price * qty = line amount
		    ,POQTY * POCOST as [LINEAMOUNT]
			--,0   as [LINEAMOUNT]
			,' '  as [LINEDELIVERYTYPE]
			,0  as [LINEDISC]
			,' ' as [LINEHEADER]
			,cast([PELINE] as int) as [LINENUMBER]
			,0  as [LINEPERCENT]

-- MarkUp Information, not populating
			,0  as [MARKUPTRANS_CALCULATEDAMOUNT]
			,' ' as [MARKUPTRANS_CURRENCYCODE]
			,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
			,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
			,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
			,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
			,0  as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
			,' ' as [MARKUPTRANS_ISDELETED]
			,' ' as [MARKUPTRANS_ISMODIFIED]
			,' ' as [MARKUPTRANS_KEEP]
			,0  as [MARKUPTRANS_LINENUM]
			,' ' as [MARKUPTRANS_MARKUPCATEGORY]
			,' ' as [MARKUPTRANS_MARKUPCODE]
			,' ' as [MARKUPTRANS_MODULECATEGORY]
			,' ' as [MARKUPTRANS_MODULETYPE]
			,0   as [MARKUPTRANS_ORIGTABLEID]
			,0  as [MARKUPTRANS_POSTED]
			,0  as [MARKUPTRANS_TAXAMOUNT]
			,' ' as [MARKUPTRANS_TAXAUTOGENERATED]
			,' ' as [MARKUPTRANS_TAXGROUP]
			,' ' as [MARKUPTRANS_TAXITEMGROUP]
			,' ' as [MARKUPTRANS_TAXWRITECODE]
 			,0   as [MARKUPTRANS_TRANSDATE] --- datetime
			,0   as [MARKUPTRANS_TRANSTABLEID]
			,' ' as [MARKUPTRANS_TXT]
			,0   as [MARKUPTRANS_VALUE]
			,' ' as [MARKUPTRANS_VOUCHER]
			,' ' as [MATCHINGPOLICY]
			,0   as [MULTILNDISC]
			,0   as [MULTILNPERCENT]

			
			 ,(select 
	           ISNULL([POCOM1],'' ) 
 			      from  [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST]   
			   WHERE
			    exists (select pocom1
			                   from  [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] ) )
				     

			,' ' as [OPERATIONTYPE_MX]
			,0   as [OVERDELIVERYPCT]
			,' ' as [PALLETTAGGING]

--- PDS Info, not populating
			,' ' as  [PDSCALCULATIONID]
			,0   as [PDSCWINVENTRECEIVEDNOW]
			,0.00 as [PDSCWQTY]
			,0.00 as [PDSCWREMAININVENTFINANCIAL]
			,0.00 as [PDSCWREMAININVENTPHYSICAL]


			,' ' as [PORT]
			,0   as [PRICEUNIT]

-- PROJ Information not being populated
			,' ' as [PROJCATEGORYID]
			,' ' as [PROJID]
			,' ' as [PROJLINEPROPERTYID]
			,' ' as [PROJSALESCURRENCYID]
			,0.00 as [PROJSALESPRICE]
			,' ' as [PROJSALESUNITID]
			,' ' as [PROJTAXGROUPID]
			,' ' as [PROJTAXITEMGROUPID]
			,' ' as [PPROJTRANSID]

			,' '   as [PSARETAINSCHEDULEID]
			,0.00  as [PSATOTALRETAINAMOUNT]

			,' ' as [PURCHASETYPE]
			,[PENO] as [PURCHID] 
			,0.00 as [PURCHMARKUP]
			,cast([POCOST] as decimal (12,4)) as [PURCHPRICE]
			,[POQTY1] as [PURCHQTY] 

-- Misc Purchase Info, not populating 
			,0.00 as [PURCHRECEIVEDNOW]
			,' ' as [PURCHREQID]
			,' ' as [PURCHSTATUS]
			,' ' as [PURCHUNIT]


			,0.00 as [RBOPACKAGELINENUM]
			,0.00 as [REMAINDER]

-- Req information, not populating
			,' ' as [REQATTENTION]
			,' ' as [REQPLANIDSCHED]
			,' ' as [REQPOID]

-- Retail information, not populating
			,0.00 as [RETAILLINENUMEX1]
			,' ' as [RETAILPACKAGEID]
			,0.00 as [RETAILTEMPVALUEEX2]

-- Return information, not populating
			,' ' as [RETURNACTIONID]
			,' ' as [RETURNDISPOSITIONCODEID]
			,' ' as [RETURNSTATUS]


			,0  as [ROWID]
			,' ' as [SCRAP]
			,0 as [SEARCHKEY]

-- Service info, not populating
			,' ' as [SERVICEADDRESS]
			,' ' as [SERVICEDATE] -- datetime

-- Shippping info, not populating
			,' ' as [SHIPPINGDATECONFIRMED] --datetime  ?????????????????????????????????????
			,' ' as [SHIPPINGDATEREQUESTED] -- datetime  ?????????????????????????????????????

			,' ' as [SKIPUPDATE]

-- Stats info, not populating
			,0.00 as [STATISTICVALUE_LT]
			,' ' as [STATPROCID]
			,' ' as [STATTRIANGULARDEAL]

			,0 as [STOCKEDPRODUCT]
			,0 as [SYSTEMENTRYSOURCE]

--1099 infi, not populating
			,0.00 as [TAX1099AMOUNT]
			,' ' as [TAX1099BOX]
			,' ' as [TAX1099STATE]
			,0.00 as [TAX1099STATEAMOUNT]
			,' ' as [TAX1099TYPE]

-- Misc Tax info, not populating
			,' ' as [TAXAUTOGENERATED]
			,' ' as [TAXGROUP]
			,' ' as [TAXITEMGROUP]
			,' ' as [TAXSERVICECODE_BR]
			,0.00 as [TAXWITHHOLDBASECUR_TH]
			,' ' as [TAXWITHHOLDGROUP_TH]
			,' ' as [TAXWITHHOLDITEMGROUP_TH]

-- Transfer info, not populating
			,' ' as [TRANSACTIONCODE]
			,0   as [TRANSFERSTATUS]
			,' ' as [TRANSPORT]


			,0.00 as [UNDERDELIVERYPCT]
			,' ' as [VARIANTID]
			,[ACCOUNTNUM]  as [VENDACCOUNT]
			--,'8001-00592' as [VENDACCOUNT]

			,[VENDGROUP] 
			,' ' as [WMSLOCATIONID]
			,' ' as [WMSPALLETID]
			,' ' as [WORKFLOWSTATE]
			,0 as [RECVERSION]
			,0 as [PARTITION]
			,0 as[RecId]




FROM OPENQUERY  
      ([INDY.ALLEGION.COM],

	 ' SELECT   
	      
		    b.[POCATG]
		   ,c.[PENO]
		   ,b.[POCOST]
		   ,b.[POQTY]
		   ,b.[POQTY1]
		   ,c.[PECO]
		   ,b.[POSTS]
		   ,c.[PELINE]
		   ,c.[PEPN]
		   ,b.[POCO]
		   ,c.[PECSDT] 
		   ,e.[AXSTRING]
		   ,f.[ACCOUNTNUM]
		   ,f.[VENDGROUP]
		 

 FROM  [KBM400MFG].[FLPOSUM] b   
  inner join [KBM400MFG].[FLPODET] C on b.[POCO] = C.[PECO] and b.[PONO] = C.[PENO] and b.[POLINE] = C.[PELINE]
  inner join [VD_GPL].[AX_FINDIM] e on e.[ACCOUNT] = b.[POACT] AND e.[LOCATION] = b.[POLOC]
  inner join [VD_GPL].[AX_VENDOR] f on f.[ORGNUMBER] = b.[POVEND]
 


  '
   ) AS A 
		                                                          
			  inner join [DmStagingVD].[dbo].[DMFPRODUCTENTITY]  F on  f.[ITEMID]  = a.[PEPN]  
                    where A.PECO in (1,3,5,8) and A.[POSTS] = 'O' 
 

 
 
 

Open in new window


Attached is file layout with the fields and their attributes of the table I need to use in the Sub select, FLSTCMST.

I think what I need is to read the table [FLSTCMST] and loop through until the purchase order number (PONO)  changes.  The field I would be concatenating is POCOM1 , a comment line. Then move on down the Script.  I am at a total loss how to accomplish this.
sample.txt
flstcmst-layout.txt
LeogalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: Avoid loops! RDBMS are built to handle sets.

Then: It looks like an export task which is better handled in SSIS.

Otherwise take a look at Bulk Import.
0
Scott PletcherSenior DBACommented:
Having to do a LOT of guessing here.
My best guess is to replace current lines 152-158 with the lines below.

    (SELECT STUFF(
        (SELECT ';' + [FLSTCMST].[POCOM1]
         FROM [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] [FLSTCMST]
         WHERE
             [FLSTCMST].[PURCHSE ORDER #] = PENO --Is this the P/O# from the outer/main query??
         ORDER BY [FLSTCMST].[POLN#]
         FOR XML PATH('')
        ), 1, 1, '') AS POCOMMENTS
    ) AS POCOM1_Combined
0
LeogalAuthor Commented:
@scottPlecher, I am giving this a whirl.  I will get back to this comment string later today.
0
LeogalAuthor Commented:
@scottPlecher, I have implemented the code and it works well! The only issue is that some of the input comments are equal to NULL. The file that I am writing to (inserting into)  will not accept nulls.
I went in to modify the code and still am getting the error.  

Here is code that you gave me with my attempt to handle NULLS:

,(SELECT STUFF
			     (
				    (SELECT ';' + ISNULL(LTRIM(RTRIM([FLSTCMST].[POCOM1])),' ')
					      FROM [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] [FLSTCMST]
					         WHERE [FLSTCMST].[PONO] = PENO 
						ORDER BY [FLSTCMST].[POLINE]
					 FOR XML PATH('')         ), 1, 1, '') AS NAME
				) AS POCOM1_Combined

Open in new window


Here is the error received  when running the code and finding a incoming null in the field POCOM1:


(0 row(s) affected)
Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'NAME', table 'DmStagingVD.dbo.DMFPURCHLINEENTITY2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
Scott PletcherSenior DBACommented:
,ISNULL((SELECT STUFF
                       (
                            (SELECT ';' + LTRIM(RTRIM(ISNULL([FLSTCMST].[POCOM1], '')))
                                    FROM [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] [FLSTCMST]
                                       WHERE [FLSTCMST].[PONO] = PENO
                                    ORDER BY [FLSTCMST].[POLINE]
                               FOR XML PATH('')         ), 1, 1, '') AS NAME
                        ), '') AS POCOM1_Combined
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.