Insert updated record into db

[wbodger]

I have this table

SELECT [StockCode]
      ,[Route]
      ,[Operation]
      ,[WorkCentre]
      ,[WcRateInd]
      ,[WhatIfWcInd]
      ,[SubcontractFlag]
      ,[ISetUpTime]
      ,[IRunTime]
      ,[IStartupTime]
      ,[ITeardownTime]
      ,[IWaitTime]
      ,[IStartupQty]
      ,[IMachine]
      ,[IUnitCapacity]
      ,[IMaxWorkOpertrs]
      ,[IMaxProdUnits]
      ,[ITimeTaken]
      ,[IQuantity]
      ,[SubSupplier]
      ,[SubPoStockCode]
      ,[SubQtyPer]
      ,[SubOrderUom]
      ,[SubOpUnitValue]
      ,[SubWhatIfValue]
      ,[SubPlanner]
      ,[SubBuyer]
      ,[SubLeadTime]
      ,[SubDockToStock]
      ,[SubOffsiteDays]
      ,[Milestone]
      ,[ElapsedTime]
      ,[MovementTime]
      ,[NarrationCode]
      ,[AutoNarrCode]
      ,[NumOfPieces]
      ,[InspectionFlag]
      ,[OperYieldPct]
      ,[OperYieldQty]
      ,[MinorSetup]
      ,[MinorSetupCode]
      ,[ToolSet]
      ,[ToolSetQty]
      ,[ToolConsumption]
      ,[TimeCalcFlag]
      ,[TimeStamp]
      ,[Version]
      ,[Release]
      ,[TransferQtyOrPct]
      ,[TransferQtyPct]
      ,[CoProductCostVal]
      ,[ResourceMask]
      ,[MaxOpDelay]
      ,[IRunTimeEnt]
      ,[ITimeTakenEnt]
      ,[IStartupQtyEnt]
      ,[IQuantityEnt]
      ,[OperYieldQtyEnt]
      ,[TransferQtyPctEnt]
      ,[MaxOpSpan]
      ,[OpSlack]
      ,[AllowOpSplit]
  FROM [table].[dbo].[BomOperations]

in which I have template ('T-') StockCodes and FG StockCodes. They would look like 'T-2D7024-13' and '2D7024-13ABC' but ABC could be anywhere from 2-6 alpha characters. Given the template StockCode ('T-'), I want to grab a specific row (specifically the Workcentre='QC' row and use that to insert a row for each FG StockCode. I have the code to grab all of the information that I need, but am not sure how I can go about the insert process without using a cursor and I would really like to get a handle on how to do something like this without using cursors. The code that I have thus far is:

use EncoreCompanyK

Declare @TStockCode varchar(50)
Declare @Length int
Declare @StockCode varchar(20)
SET @TStockCode = 'T-2D7024-13'
SET @StockCode = RIGHT(@TStockCode,len(@TStockCode)-2)

SELECT @Length=(LEN(StockCode)-2)
  FROM [EncoreCompanyK].[dbo].[BomOperations]
  where StockCode=@TStockCode
and WorkCentre='QC'

select StockCode,MAX(Operation) Operation,MAX(Operation)+1 as 'Next Operation'
from dbo.BomOperations bo 
WHERE LEFT(StockCode,@Length) = @StockCode
group by StockCode
order by StockCode, Operation

as an example for 'T-2D7024-13' I have these FG StockCodes with the corresponding Operation numbers

2D7024-13ADB                  	4	5
2D7024-13AKL                  	4	5
2D7024-13AMD                  	4	5
2D7024-13AMV                  	4	5
2D7024-13AMVCAL               	4	5
2D7024-13AQE                  	4	5
2D7024-13BL                   	4	5
2D7024-13CSU                  	4	5
2D7024-13CYK                  	4	5
2D7024-13JN                   	4	5

So, I would insert the data from 'T-2D7024-13' into dbo.BomOperations replacing 'T-2D7024-13' with '2D7024-13ADB' and replacing the Operation line with '5', leaving everything else just as it was pulled for 'T-2D7024-13'. Does that make sense? It seems like it should be easy, I am just missing some connection in my head, so any help you could give me would be greatly appreciated.

Thanks,
wb

 

[TamarGranor]

I don't totally understand what you're trying to do, but it sounds like you need to use a CTE (common table expression) to assemble the data and apply it to an INSERT command. The syntax is:

WITH cteName (fieldlist)
AS
(query to put together the data you need)

INSERT INTO YourTable 
  SELECT * FROM cteName

You can cascade a series of CTE's if you need to do a bunch of things to build the data you want, something like:

WITH cte1 (fieldlist)
AS
(SELECT ...), 

cte2 (fieldlist)
AS
(SELECT ...),

cte3 (fieldlist)
AS
(SELECT ...)

INSERT INTO YourTable 
   SELECT * FROM cte3

Each CTE along the way can use the result of the preceding one.

Tamar

 

[wbodger]

Thanks, I just finished writing it up using CTEs when I saw your reply

with cte as
(
SELECT	StockCode,Route,Operation,WorkCentre,WcRateInd,WhatIfWcInd,SubcontractFlag,ISetUpTime,IRunTime,IStartupTime,ITeardownTime,IWaitTime,IStartupQty
		,IMachine,IUnitCapacity,IMaxWorkOpertrs,IMaxProdUnits,ITimeTaken,IQuantity,SubSupplier,SubPoStockCode,SubQtyPer,SubOrderUom,SubOpUnitValue
		,SubWhatIfValue,SubPlanner,SubBuyer,SubLeadTime,SubDockToStock,SubOffsiteDays,Milestone,ElapsedTime,MovementTime,NarrationCode,AutoNarrCode
		,NumOfPieces,InspectionFlag,OperYieldPct,OperYieldQty,MinorSetup,MinorSetupCode,ToolSet,ToolSetQty,ToolConsumption,TimeCalcFlag,TimeStamp
		,Version,Release,TransferQtyOrPct,TransferQtyPct,CoProductCostVal,ResourceMask,MaxOpDelay,IRunTimeEnt,ITimeTakenEnt,IStartupQtyEnt,IQuantityEnt
		,OperYieldQtyEnt,TransferQtyPctEnt,MaxOpSpan,OpSlack,AllowOpSplit
FROM EncoreCompanyK.dbo.BomOperations
WHERE StockCode=@TStockCode and WorkCentre='QC' and NarrationCode='ANTV'
)


, cte2 as
(
select StockCode,MAX(Operation)+1 as Operation
from EncoreCompanyK.dbo.BomOperations bo 
WHERE LEFT(StockCode,LEN(@TStockCode)-2) = RIGHT(@TStockCode,len(@TStockCode)-2) and (WorkCentre <> 'QC' OR (WorkCentre='QC' and NarrationCode<>'ANTV'))
group by StockCode
)

, cte3 as
(
select	cte2.StockCode,cte.Route,cte2.Operation,cte.WorkCentre,cte.WcRateInd,cte.WhatIfWcInd,cte.SubcontractFlag,cte.ISetUpTime,cte.IRunTime,cte.IStartupTime
		,cte.ITeardownTime,cte.IWaitTime,cte.IStartupQty,cte.IMachine,cte.IUnitCapacity,cte.IMaxWorkOpertrs,cte.IMaxProdUnits,cte.ITimeTaken,cte.IQuantity,cte.SubSupplier
		,cte.SubPoStockCode,cte.SubQtyPer,cte.SubOrderUom,cte.SubOpUnitValue,cte.SubWhatIfValue,cte.SubPlanner,cte.SubBuyer,cte.SubLeadTime,cte.SubDockToStock,cte.SubOffsiteDays
		,cte.Milestone,cte.ElapsedTime,cte.MovementTime,cte.NarrationCode,cte.AutoNarrCode,cte.NumOfPieces,cte.InspectionFlag,cte.OperYieldPct,cte.OperYieldQty,cte.MinorSetup
		,cte.MinorSetupCode,cte.ToolSet,cte.ToolSetQty,cte.ToolConsumption,cte.TimeCalcFlag,cte.TimeStamp,cte.Version,cte.Release,cte.TransferQtyOrPct,cte.TransferQtyPct
		,cte.CoProductCostVal,cte.ResourceMask,cte.MaxOpDelay,cte.IRunTimeEnt,cte.ITimeTakenEnt,cte.IStartupQtyEnt,cte.IQuantityEnt
		,cte.OperYieldQtyEnt,cte.TransferQtyPctEnt,cte.MaxOpSpan,cte.OpSlack,cte.AllowOpSplit
from cte join cte2 on RIGHT(rtrim(cte.StockCode),len(rtrim(cte.StockCode))-2) = LEFT(cte2.StockCode,charindex('-',cte2.StockCode)-1+PATINDEX('%[^0-9]%',substring(cte2.StockCode,(charindex('-',cte2.StockCode)+1),LEN(cte2.StockCode))))
)

insert into EncoreCompanyK.dbo.BomOperations(StockCode,Route,Operation,WorkCentre,WcRateInd,WhatIfWcInd,SubcontractFlag,ISetUpTime,IRunTime,IStartupTime,ITeardownTime,IWaitTime,IStartupQty
		,IMachine,IUnitCapacity,IMaxWorkOpertrs,IMaxProdUnits,ITimeTaken,IQuantity,SubSupplier,SubPoStockCode,SubQtyPer,SubOrderUom,SubOpUnitValue
		,SubWhatIfValue,SubPlanner,SubBuyer,SubLeadTime,SubDockToStock,SubOffsiteDays,Milestone,ElapsedTime,MovementTime,NarrationCode,AutoNarrCode
		,NumOfPieces,InspectionFlag,OperYieldPct,OperYieldQty,MinorSetup,MinorSetupCode,ToolSet,ToolSetQty,ToolConsumption,TimeCalcFlag
		,Version,Release,TransferQtyOrPct,TransferQtyPct,CoProductCostVal,ResourceMask,MaxOpDelay,IRunTimeEnt,ITimeTakenEnt,IStartupQtyEnt,IQuantityEnt
		,OperYieldQtyEnt,TransferQtyPctEnt,MaxOpSpan,OpSlack,AllowOpSplit) 
		select StockCode,Route,Operation,WorkCentre,WcRateInd,WhatIfWcInd,SubcontractFlag,ISetUpTime,IRunTime,IStartupTime,ITeardownTime,IWaitTime,IStartupQty
		,IMachine,IUnitCapacity,IMaxWorkOpertrs,IMaxProdUnits,ITimeTaken,IQuantity,SubSupplier,SubPoStockCode,SubQtyPer,SubOrderUom,SubOpUnitValue
		,SubWhatIfValue,SubPlanner,SubBuyer,SubLeadTime,SubDockToStock,SubOffsiteDays,Milestone,ElapsedTime,MovementTime,NarrationCode,AutoNarrCode
		,NumOfPieces,InspectionFlag,OperYieldPct,OperYieldQty,MinorSetup,MinorSetupCode,ToolSet,ToolSetQty,ToolConsumption,TimeCalcFlag
		,Version,Release,TransferQtyOrPct,TransferQtyPct,CoProductCostVal,ResourceMask,MaxOpDelay,IRunTimeEnt,ITimeTakenEnt,IStartupQtyEnt,IQuantityEnt
		,OperYieldQtyEnt,TransferQtyPctEnt,MaxOpSpan,OpSlack,AllowOpSplit from cte3

Just one more piece to include to not look at one certain category of items.

thanks!

wb