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:
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
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'
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
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].[S1022
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
My best guess is to replace current lines 152-158 with the lines below.
(SELECT STUFF(
(SELECT ';' + [FLSTCMST].[POCOM1]
FROM [INDY.ALLEGION.COM].[S1022
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
ASKER
@scottPlecher, I am giving this a whirl. I will get back to this comment string later today.
ASKER
@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:
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.DMFPURCHL
The statement has been terminated.
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_CombinedHere 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.DMFPURCHL
The statement has been terminated.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Then: It looks like an export task which is better handled in SSIS.
Otherwise take a look at Bulk Import.