To table or not to table spool (Just wondering which one is better)

Question

To table or not to table spool (Just wondering which one is better)

Resender

SSCertifiable

Points: 7443
More actions
November 8, 2012 at 3:27 am

#284399

Ok, this is not something I'm stuck on just wondering, currently I'm the last day of a dataconversion project.
An hour ago I managed to optimize the stored procedure that has to pump over sales line data.(The data in the source comes from the tables Orderheader and Orderdetail and has to go to a Sales Line - Copy.)
The original query I had written took over 1 hour to process the test data (ouch especially since there less then 200,000 records in the develop database), using the display estimated execution plan I was able to bring it back to 20 seconds to a minute, I then was able to further lower the amount of lines while maintaining the same result.
I'm now writing the documentation for the developers in the next phase of the project, when writing the section about the fore mentioned stored procedure I wondered what more I could tweak.
I came to a point where I got 2 possible executing plans, both with the same runtime but 1 uses a table spool and the other doesn't.
Now the question is which is better, both runtime and batch performance are roughly the same.
Under the creation scripts for the tables, both source and destination and both the stored procedures
Source Orderheader
CREATE TABLE [dbo].[ORDERHEADER](
[OrderId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[BranchID] [char](3) NULL,
[Customer] [numeric](6, 0) NULL,
[OwnCustAddress] [bit] NOT NULL,
[KlantNaam] [char](40) NULL,
[KlantAdres] [char](40) NULL,
[KlantPostCode] [char](10) NULL,
[KlantGemeente] [char](40) NULL,
[KlantLand] [char](3) NULL,
[BVW] [char](3) NULL,
[TypeOrder] [char](3) NULL,
[OrderDate] [datetime] NULL,
[s_CreDat] [datetime] NULL,
[RefCustomer] [char](100) NULL,
[RefTP] [char](100) NULL,
[InvoiceCustomer] [numeric](6, 0) NULL,
[RefCustPrice] [numeric](6, 0) NULL,
[Representative] [char](5) NULL,
[DiscContPerc] [numeric](6, 3) NULL,
[DiscSalePerc] [numeric](6, 3) NULL,
[CreditPerc] [numeric](6, 3) NULL,
[BTWPercFree] [numeric](6, 3) NULL,
[Cancel] [char](6) NULL,
[CurrencyID] [char](3) NULL,
[BTWIncl] [bit] NOT NULL,
[isCredit] [bit] NOT NULL,
[isCancelled] [bit] NOT NULL,
[onInvoice] [bit] NOT NULL,
[waitOnInvoice] [bit] NOT NULL,
[asExpress] [bit] NOT NULL,
[GroupOnInvoice] [bit] NOT NULL,
[IsOpen] [bit] NOT NULL,
[TotalPriceAskInclBTW] [numeric](12, 2) NULL,
[TotalPriceAskExclBTW] [numeric](12, 2) NULL,
[TotalPriceDelInclBTW] [numeric](12, 2) NULL,
[TotalPriceDelExclBTW] [numeric](12, 2) NULL,
[TotalPriceNotInvInclBTW] [numeric](12, 2) NULL,
[TotalPriceNotInvExclBTW] [numeric](12, 2) NULL,
[Receiver] [char](30) NULL,
[VoucherId] [numeric](18, 0) NULL,
[ChargeId] [char](3) NULL,
[BTWID] [char](2) NULL,
[BTWPerc] [numeric](7, 3) NULL,
[TotalPayments] [numeric](12, 2) NULL,
[LockInterserver] [bit] NULL,
[DEFAULTCOMMONDISCOUNT] [numeric](6, 2) NULL,
[ONLYCOMMONDISCOUNTIFNODISCOUNT] [bit] NULL,
[DESCRIPTIONCOMMONDISCOUNT] [char](50) NULL,
[GeneralDiscountUniqueId] [uniqueidentifier] NULL,
[InvoiceDiscountUniqueId] [uniqueidentifier] NULL,
[InvoiceDiscount] [bit] NULL,
[InvoiceDiscountPercentage] [numeric](7, 3) NULL,
[InvoiceMinAmount] [numeric](12, 2) NULL,
[OrderStatusID] [char](5) NULL,
[GroupByDeliveryAddress] [bit] NULL,
[FullDelivery] [bit] NULL,
[ConfigurationID] [char](20) NULL,
[LayoutOffer] [char](2) NULL,
[AmountAdvance] [numeric](10, 0) NULL,
[NoBackorders] [bit] NULL,
CONSTRAINT [PK_ORDERHEADER] PRIMARY KEY CLUSTERED
(
[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Source Orderdetail
CREATE TABLE [dbo].[ORDERDETAIL](
[OrderId] [numeric](18, 0) NOT NULL,
[LineNumber] [numeric](7, 2) NOT NULL,
[IsTextLine] [bit] NOT NULL,
[IsPriceLine] [bit] NOT NULL,
[IsVisible] [bit] NOT NULL,
[HasLink] [bit] NOT NULL,
[LinkLine] [numeric](7, 2) NULL,
[LinkAmount] [numeric](9, 3) NULL,
[OrderDate] [datetime] NULL,
[UserID] [char](3) NULL,
[CashDeskNumber] [int] NULL,
[KnownProduct] [bit] NOT NULL,
[ProdId] [char](20) NULL,
[UseOwnProdDescr] [bit] NOT NULL,
[ProdDescr] [char](50) NULL,
[BarCode] [char](30) NULL,
[Info] [char](60) NULL,
[AmountAsked] [numeric](13, 3) NULL,
[AmountDeliverd] [numeric](13, 3) NULL,
[AmountDeliverdAlready] [numeric](13, 3) NULL,
[AmountReturn] [numeric](13, 3) NULL,
[HandPriceExclBTW] [numeric](12, 2) NULL,
[HandPriceInclBTW] [numeric](12, 2) NULL,
[HandBrutoPriceExclBTW] [numeric](12, 2) NULL,
[HandBrutoPriceInclBTW] [numeric](12, 2) NULL,
[CalcPriceNoDiscExclBTW] [numeric](12, 2) NULL,
[CalcPriceDiscExclBTW] [numeric](12, 2) NULL,
[CalcPriceNoDiscInclBTW] [numeric](12, 2) NULL,
[CalcPriceDiscInclBTW] [numeric](12, 2) NULL,
[CalcDiscPerc] [numeric](7, 3) NULL,
[LinePriceNoDiscExclBTW] [numeric](16, 6) NULL,
[LinePriceDiscExclBTW] [numeric](16, 6) NULL,
[LinePriceNoDiscInclBTW] [numeric](16, 6) NULL,
[LinePriceDiscInclBTW] [numeric](16, 6) NULL,
[LineDiscPerc] [numeric](7, 3) NULL,
[UnitPriceNoDiscExclBTW] [numeric](12, 2) NULL,
[UnitPriceDiscExclBTW] [numeric](12, 2) NULL,
[UnitPriceNoDiscInclBTW] [numeric](12, 2) NULL,
[UnitPriceDiscInclBTW] [numeric](12, 2) NULL,
[UnitDiscPerc] [numeric](7, 3) NULL,
[CostPrice] [numeric](12, 2) NULL,
[PurchasePriceAvg] [numeric](14, 4) NULL,
[CurrencyID] [char](3) NULL,
[Discount] [char](3) NULL,
[DiscountPerc] [numeric](7, 3) NULL,
[DivKeyNum] [numeric](9, 2) NULL,
[DivKeyDenom] [numeric](9, 2) NULL,
[UseLength] [bit] NOT NULL,
[UseWidth] [bit] NOT NULL,
[UseHeight] [bit] NOT NULL,
[Length] [numeric](8, 0) NULL,
[Width] [numeric](8, 0) NULL,
[Height] [numeric](8, 0) NULL,
[BranchID] [char](3) NULL,
[WarehousID] [char](3) NULL,
[LocationID] [char](20) NULL,
[AmountStockOrder] [numeric](13, 3) NULL,
[AmountExpress] [numeric](13, 3) NULL,
[ProfitID] [char](3) NULL,
[BTWID] [char](2) NULL,
[BTWPerc] [numeric](7, 3) NULL,
[Weight] [numeric](15, 3) NULL,
[AanvullendeHoeveelheid] [numeric](12, 2) NULL,
[AanvullendeEenheid] [char](15) NULL,
[TransactionType] [numeric](1, 0) NULL,
[IntraStat] [char](20) NULL,
[withCustomerCard] [bit] NOT NULL,
[onPicking] [bit] NOT NULL,
[forPrognose] [bit] NOT NULL,
[InvoiceNumber] [char](20) NULL,
[StockUnit] [char](3) NULL,
[SaleUnit] [char](3) NULL,
[DeliveryNumber] [numeric](10, 0) NULL,
[s_credat] [datetime] NULL,
[Commentaar] [text] NULL,
[FollowedProdRefCustomer] [char](20) NULL,
[CalcUsedTypeDiscount] [numeric](8, 0) NULL,
[HasNoSubDelivery] [bit] NOT NULL,
[VoucherId] [char](15) NULL,
[IntrastatValue] [numeric](12, 2) NULL,
[LinkedOrderID] [numeric](18, 0) NULL,
[LinkedLineNumber] [numeric](7, 2) NULL,
[BagId] [numeric](18, 0) NULL,
[BagName] [char](50) NULL,
[ModifiedByPicking] [numeric](3, 0) NOT NULL,
[DisplayOrder] [numeric](7, 2) NULL,
[AllowNoDiscount] [bit] NULL,
[PackingId] [char](3) NULL,
[AmountOnPacking] [numeric](18, 8) NULL,
[ReferencePacking] [char](20) NULL,
[CommonDiscount] [numeric](6, 2) NULL,
[DescriptionCommonDiscount] [char](50) NULL,
[UniqueBagID] [uniqueidentifier] NULL,
[AmountFullPackageAsk] [numeric](18, 4) NULL,
[AmountRestAsk] [numeric](18, 4) NULL,
[AmountFullPackageDel] [numeric](18, 4) NULL,
[AmountRestDel] [numeric](18, 4) NULL,
[SourceCountry] [char](3) NULL,
[CalcPriceDiscExclBTWNoExtraDisc] [numeric](12, 2) NULL,
[CalcRoundPrice] [numeric](12, 2) NULL,
[InvoiceDiscount] [bit] NULL,
[LineType] [char](1) NULL,
[IsNewProd] [bit] NULL,
[ChargeId] [char](3) NULL,
[AskPriority] [bit] NULL,
[AskDate] [datetime] NULL,
[AskYear] [numeric](4, 0) NULL,
[AskWeek] [numeric](2, 0) NULL,
[AskInfo] [text] NULL,
[ConfirmedPriority] [bit] NULL,
[ConfirmedWeek] [numeric](2, 0) NULL,
[ConfirmedInfo] [text] NULL,
[ConfirmedDate] [datetime] NULL,
[ConfirmedYear] [numeric](4, 0) NULL,
[RefQuotation] [numeric](18, 0) NULL,
[RefQuotationLineNumber] [numeric](7, 2) NULL,
[PrintCommentAllSublines] [bit] NOT NULL,
[IsDelivered] [bit] NOT NULL,
[PurchasePrice] [numeric](14, 4) NULL,
[AmountReserved] [numeric](13, 3) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Destination Sales Line
CREATE TABLE [dbo].[Sales Line - Copy](
[timestamp] [timestamp] NOT NULL,
[Document Type] [int] NOT NULL,
[Document No_] [varchar](20) NOT NULL,
[Line No_] [int] NOT NULL,
[Sell-to Customer No_] [varchar](20) NOT NULL,
[Type] [int] NOT NULL,
[No_] [varchar](20) NOT NULL,
[Location Code] [varchar](10) NOT NULL,
[Posting Group] [varchar](10) NOT NULL,
[Shipment Date] [datetime] NOT NULL,
[Description] [varchar](50) NOT NULL,
[Description 2] [varchar](50) NOT NULL,
[Unit of Measure] [varchar](10) NOT NULL,
[Quantity] [decimal](38, 20) NOT NULL,
[Outstanding Quantity] [decimal](38, 20) NOT NULL,
[Qty_ to Invoice] [decimal](38, 20) NOT NULL,
[Qty_ to Ship] [decimal](38, 20) NOT NULL,
[Unit Price] [decimal](38, 20) NOT NULL,
[Unit Cost (LCY)] [decimal](38, 20) NOT NULL,
[VAT %] [decimal](38, 20) NOT NULL,
[Line Discount %] [decimal](38, 20) NOT NULL,
[Line Discount Amount] [decimal](38, 20) NOT NULL,
[Amount] [decimal](38, 20) NOT NULL,
[Amount Including VAT] [decimal](38, 20) NOT NULL,
[Allow Invoice Disc_] [tinyint] NOT NULL,
[Gross Weight] [decimal](38, 20) NOT NULL,
[Net Weight] [decimal](38, 20) NOT NULL,
[Units per Parcel] [decimal](38, 20) NOT NULL,
[Unit Volume] [decimal](38, 20) NOT NULL,
[Appl_-to Item Entry] [int] NOT NULL,
[Shortcut Dimension 1 Code] [varchar](20) NOT NULL,
[Shortcut Dimension 2 Code] [varchar](20) NOT NULL,
[Customer Price Group] [varchar](10) NOT NULL,
[Job No_] [varchar](20) NOT NULL,
[Work Type Code] [varchar](10) NOT NULL,
[Outstanding Amount] [decimal](38, 20) NOT NULL,
[Qty_ Shipped Not Invoiced] [decimal](38, 20) NOT NULL,
[Shipped Not Invoiced] [decimal](38, 20) NOT NULL,
[Quantity Shipped] [decimal](38, 20) NOT NULL,
[Quantity Invoiced] [decimal](38, 20) NOT NULL,
[Shipment No_] [varchar](20) NOT NULL,
[Shipment Line No_] [int] NOT NULL,
[Profit %] [decimal](38, 20) NOT NULL,
[Bill-to Customer No_] [varchar](20) NOT NULL,
[Inv_ Discount Amount] [decimal](38, 20) NOT NULL,
[Purchase Order No_] [varchar](20) NOT NULL,
[Purch_ Order Line No_] [int] NOT NULL,
[Drop Shipment] [tinyint] NOT NULL,
[Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
[Gen_ Prod_ Posting Group] [varchar](10) NOT NULL,
[VAT Calculation Type] [int] NOT NULL,
[Transaction Type] [varchar](10) NOT NULL,
[Transport Method] [varchar](10) NOT NULL,
[Attached to Line No_] [int] NOT NULL,
[Exit Point] [varchar](10) NOT NULL,
[Area] [varchar](10) NOT NULL,
[Transaction Specification] [varchar](10) NOT NULL,
[Tax Area Code] [varchar](20) NOT NULL,
[Tax Liable] [tinyint] NOT NULL,
[Tax Group Code] [varchar](10) NOT NULL,
[VAT Bus_ Posting Group] [varchar](10) NOT NULL,
[VAT Prod_ Posting Group] [varchar](10) NOT NULL,
[Currency Code] [varchar](10) NOT NULL,
[Outstanding Amount (LCY)] [decimal](38, 20) NOT NULL,
[Shipped Not Invoiced (LCY)] [decimal](38, 20) NOT NULL,
[Reserve] [int] NOT NULL,
[Blanket Order No_] [varchar](20) NOT NULL,
[Blanket Order Line No_] [int] NOT NULL,
[VAT Base Amount] [decimal](38, 20) NOT NULL,
[Unit Cost] [decimal](38, 20) NOT NULL,
[System-Created Entry] [tinyint] NOT NULL,
[Line Amount] [decimal](38, 20) NOT NULL,
[VAT Difference] [decimal](38, 20) NOT NULL,
[Inv_ Disc_ Amount to Invoice] [decimal](38, 20) NOT NULL,
[VAT Identifier] [varchar](10) NOT NULL,
[IC Partner Ref_ Type] [int] NOT NULL,
[IC Partner Reference] [varchar](20) NOT NULL,
[Prepayment %] [decimal](38, 20) NOT NULL,
[Prepmt_ Line Amount] [decimal](38, 20) NOT NULL,
[Prepmt_ Amt_ Inv_] [decimal](38, 20) NOT NULL,
[Prepmt_ Amt_ Incl_ VAT] [decimal](38, 20) NOT NULL,
[Prepayment Amount] [decimal](38, 20) NOT NULL,
[Prepmt_ VAT Base Amt_] [decimal](38, 20) NOT NULL,
[Prepayment VAT %] [decimal](38, 20) NOT NULL,
[Prepmt_ VAT Calc_ Type] [int] NOT NULL,
[Prepayment VAT Identifier] [varchar](10) NOT NULL,
[Prepayment Tax Area Code] [varchar](20) NOT NULL,
[Prepayment Tax Liable] [tinyint] NOT NULL,
[Prepayment Tax Group Code] [varchar](10) NOT NULL,
[Prepmt Amt to Deduct] [decimal](38, 20) NOT NULL,
[Prepmt Amt Deducted] [decimal](38, 20) NOT NULL,
[Prepayment Line] [tinyint] NOT NULL,
[Prepmt_ Amount Inv_ Incl_ VAT] [decimal](38, 20) NOT NULL,
[Prepmt_ Amount Inv_ (LCY)] [decimal](38, 20) NOT NULL,
[IC Partner Code] [varchar](20) NOT NULL,
[Prepayment VAT Difference] [decimal](38, 20) NOT NULL,
[Prepmt VAT Diff_ to Deduct] [decimal](38, 20) NOT NULL,
[Prepmt VAT Diff_ Deducted] [decimal](38, 20) NOT NULL,
[Job Task No_] [varchar](20) NOT NULL,
[Job Contract Entry No_] [int] NOT NULL,
[Variant Code] [varchar](10) NOT NULL,
[Bin Code] [varchar](20) NOT NULL,
[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
[Planned] [tinyint] NOT NULL,
[Unit of Measure Code] [varchar](10) NOT NULL,
[Quantity (Base)] [decimal](38, 20) NOT NULL,
[Outstanding Qty_ (Base)] [decimal](38, 20) NOT NULL,
[Qty_ to Invoice (Base)] [decimal](38, 20) NOT NULL,
[Qty_ to Ship (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Shipped Not Invd_ (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Shipped (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Invoiced (Base)] [decimal](38, 20) NOT NULL,
[FA Posting Date] [datetime] NOT NULL,
[Depreciation Book Code] [varchar](10) NOT NULL,
[Depr_ until FA Posting Date] [tinyint] NOT NULL,
[Duplicate in Depreciation Book] [varchar](10) NOT NULL,
[Use Duplication List] [tinyint] NOT NULL,
[Responsibility Center] [varchar](10) NOT NULL,
[Out-of-Stock Substitution] [tinyint] NOT NULL,
[Originally Ordered No_] [varchar](20) NOT NULL,
[Originally Ordered Var_ Code] [varchar](10) NOT NULL,
[Cross-Reference No_] [varchar](20) NOT NULL,
[Unit of Measure (Cross Ref_)] [varchar](10) NOT NULL,
[Cross-Reference Type] [int] NOT NULL,
[Cross-Reference Type No_] [varchar](30) NOT NULL,
[Item Category Code] [varchar](10) NOT NULL,
[Nonstock] [tinyint] NOT NULL,
[Purchasing Code] [varchar](10) NOT NULL,
[Product Group Code] [varchar](10) NOT NULL,
[Special Order] [tinyint] NOT NULL,
[Special Order Purchase No_] [varchar](20) NOT NULL,
[Special Order Purch_ Line No_] [int] NOT NULL,
[Completely Shipped] [tinyint] NOT NULL,
[Requested Delivery Date] [datetime] NOT NULL,
[Promised Delivery Date] [datetime] NOT NULL,
[Shipping Time] [varchar](32) NOT NULL,
[Outbound Whse_ Handling Time] [varchar](32) NOT NULL,
[Planned Delivery Date] [datetime] NOT NULL,
[Planned Shipment Date] [datetime] NOT NULL,
[Shipping Agent Code] [varchar](10) NOT NULL,
[Shipping Agent Service Code] [varchar](10) NOT NULL,
[Allow Item Charge Assignment] [tinyint] NOT NULL,
[Return Qty_ to Receive] [decimal](38, 20) NOT NULL,
[Return Qty_ to Receive (Base)] [decimal](38, 20) NOT NULL,
[Return Qty_ Rcd_ Not Invd_] [decimal](38, 20) NOT NULL,
[Ret_ Qty_ Rcd_ Not Invd_(Base)] [decimal](38, 20) NOT NULL,
[Return Rcd_ Not Invd_] [decimal](38, 20) NOT NULL,
[Return Rcd_ Not Invd_ (LCY)] [decimal](38, 20) NOT NULL,
[Return Qty_ Received] [decimal](38, 20) NOT NULL,
[Return Qty_ Received (Base)] [decimal](38, 20) NOT NULL,
[Appl_-from Item Entry] [int] NOT NULL,
[BOM Item No_] [varchar](20) NOT NULL,
[Return Receipt No_] [varchar](20) NOT NULL,
[Return Receipt Line No_] [int] NOT NULL,
[Return Reason Code] [varchar](10) NOT NULL,
[Allow Line Disc_] [tinyint] NOT NULL,
[Customer Disc_ Group] [varchar](10) NOT NULL,
[Pmt_ Discount Amount] [decimal](38, 20) NOT NULL,
[StandardUnitPrice] [decimal](38, 20) NOT NULL,
[NoPaymentDiscount] [tinyint] NOT NULL,
[Unit Price M2] [decimal](38, 20) NOT NULL,
[PrintOption] [int] NOT NULL,
[Territory Code] [varchar](10) NOT NULL,
[Unit Cost Changed] [tinyint] NOT NULL,
[Tariff No_] [varchar](10) NOT NULL,
[Site] [varchar](20) NOT NULL,
[Site Sublocation] [int] NOT NULL,
[Invoice Unit of Measure] [varchar](10) NOT NULL,
[Qty in Invoice Measure] [decimal](38, 20) NOT NULL,
[Validation Date] [datetime] NOT NULL,
[Close Quote Code] [varchar](10) NOT NULL,
[BorgItem] [tinyint] NOT NULL,
[Salesperson Code] [varchar](10) NOT NULL,
[Line Discount Amount 1] [decimal](38, 20) NOT NULL,
[Line Discount % 2] [decimal](38, 20) NOT NULL,
[Line Discount Amount 2] [decimal](38, 20) NOT NULL,
[Quote No_] [varchar](20) NOT NULL,
[Contract No_] [varchar](20) NOT NULL,
[Ordered] [tinyint] NOT NULL,
[Minimum Quantity to Ship] [decimal](38, 20) NOT NULL,
[Purch_ Contract No_] [varchar](20) NOT NULL,
[Purch_ Contract Line] [int] NOT NULL,
[Shipment End Date] [datetime] NOT NULL,
[Planned for Transport] [tinyint] NOT NULL,
[Supplement] [tinyint] NOT NULL,
[Quote Posted] [tinyint] NOT NULL,
[Item Recyclage] [tinyint] NOT NULL,
[No_OfCollies] [int] NOT NULL,
[Prim_ Vendor No_] [varchar](20) NOT NULL,
[Simulation] [tinyint] NOT NULL,
[Line Net Weight] [decimal](38, 20) NOT NULL,
[Line Deliver Weight] [decimal](38, 20) NOT NULL,
[Line Volume] [decimal](38, 20) NOT NULL,
[Line Deliver Volume] [decimal](38, 20) NOT NULL,
[Order No_] [varchar](20) NOT NULL,
[Transport Line] [tinyint] NOT NULL,
[Detailled Resource] [varchar](20) NOT NULL,
[Relocation Resource] [tinyint] NOT NULL,
[Detailled Activity] [int] NOT NULL,
[Site from] [varchar](20) NOT NULL,
[Detailled Remark] [varchar](80) NOT NULL,
[Item Shpt_ Entry No_] [int] NOT NULL,
[Unit Price Incl_ VAT] [decimal](38, 20) NOT NULL,
[Commission %] [decimal](38, 20) NOT NULL,
[Line Type] [int] NOT NULL,
[BOM Attached Line No_] [int] NOT NULL,
[Calc Formula] [varchar](50) NOT NULL,
[Parent Line No_] [int] NOT NULL,
[Original Line No_] [int] NOT NULL,
[SkipGetPriceQuoteAndContract] [tinyint] NOT NULL,
[Description 3] [varchar](100) NOT NULL,
[Back Order] [tinyint] NOT NULL,
[Quantity Back Order] [decimal](38, 20) NOT NULL,
[Qty (Base) Back Order] [decimal](38, 20) NOT NULL,
[Reason Code change Price_Disc] [varchar](10) NOT NULL,
[Orig_ Line Discount %] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount % 2] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount Amount 1] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount Amount 2] [decimal](38, 20) NOT NULL,
[UOM Rounding Precision Unit] [decimal](38, 20) NOT NULL,
[UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[UOM Rounding Type] [int] NOT NULL,
[Inv_ UOM Round_ Precision Unit] [decimal](38, 20) NOT NULL,
[Inv_ UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[Inv_ UOM Rounding Type] [int] NOT NULL,
[Base UOM Round_ Precision Unit] [decimal](38, 20) NOT NULL,
[Base UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[Base UOM Rounding Type] [int] NOT NULL,
[Cad import] [tinyint] NOT NULL,
[Picking Status] [int] NOT NULL,
[New Location Code] [varchar](10) NOT NULL,
[New Bin Code] [varchar](20) NOT NULL,
[New Picking Status] [int] NOT NULL,
[Concrete Supplement] [tinyint] NOT NULL,
[Custom Code 1] [tinyint] NOT NULL,
[Price Option] [int] NOT NULL,
[Building Department] [varchar](10) NOT NULL,
[IC Purch_ Doc No_] [varchar](20) NOT NULL,
[IC Purch_ Line No_] [int] NOT NULL,
[NoCheckChangePrice] [tinyint] NOT NULL,
[Lot No_] [varchar](20) NOT NULL,
[Sample] [int] NOT NULL,
[Print Picture] [tinyint] NOT NULL,
[Std_ Price Exists] [int] NOT NULL,
[Line Disc Exists] [int] NOT NULL,
CONSTRAINT [dbo.Sales Line - Copy$0] PRIMARY KEY CLUSTERED
(
[Document No_] ASC,
[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
OK before we go onto the procedures there's 2 things to note
1)This is a navision project so empty dates in SQL server are treated as 1900-01-01 by navision, in order to get empty dates in navision you have to use '1753-01-01 00:00:00.000'
2)Each line needs a number that goes up with 10000 each time (this is so that sublines can be added later on, these sublines are stored somewhere else though)
Stored procedure without table spool
declare @tSalesLine table
(
[Document Type] [int] NOT NULL,
[Document No_] [varchar](20) NOT NULL,
[Line No_] [int] NOT NULL,
[Sell-to Customer No_] [varchar](20) NOT NULL,
[Type] [int] NOT NULL,
[No_] [varchar](20) NOT NULL,
[Location Code] [varchar](10) NOT NULL,
[Posting Group] [varchar](10) NOT NULL,
[Shipment Date] [datetime] NOT NULL,
[Description] [varchar](50) NOT NULL,
[Description 2] [varchar](50) NOT NULL,
[Unit of Measure] [varchar](10) NOT NULL,
[Quantity] [decimal](38, 20) NOT NULL,
[Outstanding Quantity] [decimal](38, 20) NOT NULL,
[Qty_ to Invoice] [decimal](38, 20) NOT NULL,
[Qty_ to Ship] [decimal](38, 20) NOT NULL,
[Unit Price] [decimal](38, 20) NOT NULL,
[Unit Cost (LCY)] [decimal](38, 20) NOT NULL,
[VAT %] [decimal](38, 20) NOT NULL,
[Line Discount %] [decimal](38, 20) NOT NULL,
[Line Discount Amount] [decimal](38, 20) NOT NULL,
[Amount] [decimal](38, 20) NOT NULL,
[Amount Including VAT] [decimal](38, 20) NOT NULL,
[Allow Invoice Disc_] [tinyint] NOT NULL,
[Gross Weight] [decimal](38, 20) NOT NULL,
[Net Weight] [decimal](38, 20) NOT NULL,
[Units per Parcel] [decimal](38, 20) NOT NULL,
[Unit Volume] [decimal](38, 20) NOT NULL,
[Appl_-to Item Entry] [int] NOT NULL,
[Shortcut Dimension 1 Code] [varchar](20) NOT NULL,
[Shortcut Dimension 2 Code] [varchar](20) NOT NULL,
[Customer Price Group] [varchar](10) NOT NULL,
[Job No_] [varchar](20) NOT NULL,
[Work Type Code] [varchar](10) NOT NULL,
[Outstanding Amount] [decimal](38, 20) NOT NULL,
[Qty_ Shipped Not Invoiced] [decimal](38, 20) NOT NULL,
[Shipped Not Invoiced] [decimal](38, 20) NOT NULL,
[Quantity Shipped] [decimal](38, 20) NOT NULL,
[Quantity Invoiced] [decimal](38, 20) NOT NULL,
[Shipment No_] [varchar](20) NOT NULL,
[Shipment Line No_] [int] NOT NULL,
[Profit %] [decimal](38, 20) NOT NULL,
[Bill-to Customer No_] [varchar](20) NOT NULL,
[Inv_ Discount Amount] [decimal](38, 20) NOT NULL,
[Purchase Order No_] [varchar](20) NOT NULL,
[Purch_ Order Line No_] [int] NOT NULL,
[Drop Shipment] [tinyint] NOT NULL,
[Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
[Gen_ Prod_ Posting Group] [varchar](10) NOT NULL,
[VAT Calculation Type] [int] NOT NULL,
[Transaction Type] [varchar](10) NOT NULL,
[Transport Method] [varchar](10) NOT NULL,
[Attached to Line No_] [int] NOT NULL,
[Exit Point] [varchar](10) NOT NULL,
[Area] [varchar](10) NOT NULL,
[Transaction Specification] [varchar](10) NOT NULL,
[Tax Area Code] [varchar](20) NOT NULL,
[Tax Liable] [tinyint] NOT NULL,
[Tax Group Code] [varchar](10) NOT NULL,
[VAT Bus_ Posting Group] [varchar](10) NOT NULL,
[VAT Prod_ Posting Group] [varchar](10) NOT NULL,
[Currency Code] [varchar](10) NOT NULL,
[Outstanding Amount (LCY)] [decimal](38, 20) NOT NULL,
[Shipped Not Invoiced (LCY)] [decimal](38, 20) NOT NULL,
[Reserve] [int] NOT NULL,
[Blanket Order No_] [varchar](20) NOT NULL,
[Blanket Order Line No_] [int] NOT NULL,
[VAT Base Amount] [decimal](38, 20) NOT NULL,
[Unit Cost] [decimal](38, 20) NOT NULL,
[System-Created Entry] [tinyint] NOT NULL,
[Line Amount] [decimal](38, 20) NOT NULL,
[VAT Difference] [decimal](38, 20) NOT NULL,
[Inv_ Disc_ Amount to Invoice] [decimal](38, 20) NOT NULL,
[VAT Identifier] [varchar](10) NOT NULL,
[IC Partner Ref_ Type] [int] NOT NULL,
[IC Partner Reference] [varchar](20) NOT NULL,
[Prepayment %] [decimal](38, 20) NOT NULL,
[Prepmt_ Line Amount] [decimal](38, 20) NOT NULL,
[Prepmt_ Amt_ Inv_] [decimal](38, 20) NOT NULL,
[Prepmt_ Amt_ Incl_ VAT] [decimal](38, 20) NOT NULL,
[Prepayment Amount] [decimal](38, 20) NOT NULL,
[Prepmt_ VAT Base Amt_] [decimal](38, 20) NOT NULL,
[Prepayment VAT %] [decimal](38, 20) NOT NULL,
[Prepmt_ VAT Calc_ Type] [int] NOT NULL,
[Prepayment VAT Identifier] [varchar](10) NOT NULL,
[Prepayment Tax Area Code] [varchar](20) NOT NULL,
[Prepayment Tax Liable] [tinyint] NOT NULL,
[Prepayment Tax Group Code] [varchar](10) NOT NULL,
[Prepmt Amt to Deduct] [decimal](38, 20) NOT NULL,
[Prepmt Amt Deducted] [decimal](38, 20) NOT NULL,
[Prepayment Line] [tinyint] NOT NULL,
[Prepmt_ Amount Inv_ Incl_ VAT] [decimal](38, 20) NOT NULL,
[Prepmt_ Amount Inv_ (LCY)] [decimal](38, 20) NOT NULL,
[IC Partner Code] [varchar](20) NOT NULL,
[Prepayment VAT Difference] [decimal](38, 20) NOT NULL,
[Prepmt VAT Diff_ to Deduct] [decimal](38, 20) NOT NULL,
[Prepmt VAT Diff_ Deducted] [decimal](38, 20) NOT NULL,
[Job Task No_] [varchar](20) NOT NULL,
[Job Contract Entry No_] [int] NOT NULL,
[Variant Code] [varchar](10) NOT NULL,
[Bin Code] [varchar](20) NOT NULL,
[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
[Planned] [tinyint] NOT NULL,
[Unit of Measure Code] [varchar](10) NOT NULL,
[Quantity (Base)] [decimal](38, 20) NOT NULL,
[Outstanding Qty_ (Base)] [decimal](38, 20) NOT NULL,
[Qty_ to Invoice (Base)] [decimal](38, 20) NOT NULL,
[Qty_ to Ship (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Shipped Not Invd_ (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Shipped (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Invoiced (Base)] [decimal](38, 20) NOT NULL,
[FA Posting Date] [datetime] NOT NULL,
[Depreciation Book Code] [varchar](10) NOT NULL,
[Depr_ until FA Posting Date] [tinyint] NOT NULL,
[Duplicate in Depreciation Book] [varchar](10) NOT NULL,
[Use Duplication List] [tinyint] NOT NULL,
[Responsibility Center] [varchar](10) NOT NULL,
[Out-of-Stock Substitution] [tinyint] NOT NULL,
[Originally Ordered No_] [varchar](20) NOT NULL,
[Originally Ordered Var_ Code] [varchar](10) NOT NULL,
[Cross-Reference No_] [varchar](20) NOT NULL,
[Unit of Measure (Cross Ref_)] [varchar](10) NOT NULL,
[Cross-Reference Type] [int] NOT NULL,
[Cross-Reference Type No_] [varchar](30) NOT NULL,
[Item Category Code] [varchar](10) NOT NULL,
[Nonstock] [tinyint] NOT NULL,
[Purchasing Code] [varchar](10) NOT NULL,
[Product Group Code] [varchar](10) NOT NULL,
[Special Order] [tinyint] NOT NULL,
[Special Order Purchase No_] [varchar](20) NOT NULL,
[Special Order Purch_ Line No_] [int] NOT NULL,
[Completely Shipped] [tinyint] NOT NULL,
[Requested Delivery Date] [datetime] NOT NULL,
[Promised Delivery Date] [datetime] NOT NULL,
[Shipping Time] [varchar](32) NOT NULL,
[Outbound Whse_ Handling Time] [varchar](32) NOT NULL,
[Planned Delivery Date] [datetime] NOT NULL,
[Planned Shipment Date] [datetime] NOT NULL,
[Shipping Agent Code] [varchar](10) NOT NULL,
[Shipping Agent Service Code] [varchar](10) NOT NULL,
[Allow Item Charge Assignment] [tinyint] NOT NULL,
[Return Qty_ to Receive] [decimal](38, 20) NOT NULL,
[Return Qty_ to Receive (Base)] [decimal](38, 20) NOT NULL,
[Return Qty_ Rcd_ Not Invd_] [decimal](38, 20) NOT NULL,
[Ret_ Qty_ Rcd_ Not Invd_(Base)] [decimal](38, 20) NOT NULL,
[Return Rcd_ Not Invd_] [decimal](38, 20) NOT NULL,
[Return Rcd_ Not Invd_ (LCY)] [decimal](38, 20) NOT NULL,
[Return Qty_ Received] [decimal](38, 20) NOT NULL,
[Return Qty_ Received (Base)] [decimal](38, 20) NOT NULL,
[Appl_-from Item Entry] [int] NOT NULL,
[BOM Item No_] [varchar](20) NOT NULL,
[Return Receipt No_] [varchar](20) NOT NULL,
[Return Receipt Line No_] [int] NOT NULL,
[Return Reason Code] [varchar](10) NOT NULL,
[Allow Line Disc_] [tinyint] NOT NULL,
[Customer Disc_ Group] [varchar](10) NOT NULL,
[Pmt_ Discount Amount] [decimal](38, 20) NOT NULL,
[StandardUnitPrice] [decimal](38, 20) NOT NULL,
[NoPaymentDiscount] [tinyint] NOT NULL,
[Unit Price M2] [decimal](38, 20) NOT NULL,
[PrintOption] [int] NOT NULL,
[Territory Code] [varchar](10) NOT NULL,
[Unit Cost Changed] [tinyint] NOT NULL,
[Tariff No_] [varchar](10) NOT NULL,
[Site] [varchar](20) NOT NULL,
[Site Sublocation] [int] NOT NULL,
[Invoice Unit of Measure] [varchar](10) NOT NULL,
[Qty in Invoice Measure] [decimal](38, 20) NOT NULL,
[Validation Date] [datetime] NOT NULL,
[Close Quote Code] [varchar](10) NOT NULL,
[BorgItem] [tinyint] NOT NULL,
[Salesperson Code] [varchar](10) NOT NULL,
[Line Discount Amount 1] [decimal](38, 20) NOT NULL,
[Line Discount % 2] [decimal](38, 20) NOT NULL,
[Line Discount Amount 2] [decimal](38, 20) NOT NULL,
[Quote No_] [varchar](20) NOT NULL,
[Contract No_] [varchar](20) NOT NULL,
[Ordered] [tinyint] NOT NULL,
[Minimum Quantity to Ship] [decimal](38, 20) NOT NULL,
[Purch_ Contract No_] [varchar](20) NOT NULL,
[Purch_ Contract Line] [int] NOT NULL,
[Shipment End Date] [datetime] NOT NULL,
[Planned for Transport] [tinyint] NOT NULL,
[Supplement] [tinyint] NOT NULL,
[Quote Posted] [tinyint] NOT NULL,
[Item Recyclage] [tinyint] NOT NULL,
[No_OfCollies] [int] NOT NULL,
[Prim_ Vendor No_] [varchar](20) NOT NULL,
[Simulation] [tinyint] NOT NULL,
[Line Net Weight] [decimal](38, 20) NOT NULL,
[Line Deliver Weight] [decimal](38, 20) NOT NULL,
[Line Volume] [decimal](38, 20) NOT NULL,
[Line Deliver Volume] [decimal](38, 20) NOT NULL,
[Order No_] [varchar](20) NOT NULL,
[Transport Line] [tinyint] NOT NULL,
[Detailled Resource] [varchar](20) NOT NULL,
[Relocation Resource] [tinyint] NOT NULL,
[Detailled Activity] [int] NOT NULL,
[Site from] [varchar](20) NOT NULL,
[Detailled Remark] [varchar](80) NOT NULL,
[Item Shpt_ Entry No_] [int] NOT NULL,
[Unit Price Incl_ VAT] [decimal](38, 20) NOT NULL,
[Commission %] [decimal](38, 20) NOT NULL,
[Line Type] [int] NOT NULL,
[BOM Attached Line No_] [int] NOT NULL,
[Calc Formula] [varchar](50) NOT NULL,
[Parent Line No_] [int] NOT NULL,
[Original Line No_] [int] NOT NULL,
[SkipGetPriceQuoteAndContract] [tinyint] NOT NULL,
[Description 3] [varchar](100) NOT NULL,
[Back Order] [tinyint] NOT NULL,
[Quantity Back Order] [decimal](38, 20) NOT NULL,
[Qty (Base) Back Order] [decimal](38, 20) NOT NULL,
[Reason Code change Price_Disc] [varchar](10) NOT NULL,
[Orig_ Line Discount %] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount % 2] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount Amount 1] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount Amount 2] [decimal](38, 20) NOT NULL,
[UOM Rounding Precision Unit] [decimal](38, 20) NOT NULL,
[UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[UOM Rounding Type] [int] NOT NULL,
[Inv_ UOM Round_ Precision Unit] [decimal](38, 20) NOT NULL,
[Inv_ UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[Inv_ UOM Rounding Type] [int] NOT NULL,
[Base UOM Round_ Precision Unit] [decimal](38, 20) NOT NULL,
[Base UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[Base UOM Rounding Type] [int] NOT NULL,
[Cad import] [tinyint] NOT NULL,
[Picking Status] [int] NOT NULL,
[New Location Code] [varchar](10) NOT NULL,
[New Bin Code] [varchar](20) NOT NULL,
[New Picking Status] [int] NOT NULL,
[Concrete Supplement] [tinyint] NOT NULL,
[Custom Code 1] [tinyint] NOT NULL,
[Price Option] [int] NOT NULL,
[Building Department] [varchar](10) NOT NULL,
[IC Purch_ Doc No_] [varchar](20) NOT NULL,
[IC Purch_ Line No_] [int] NOT NULL,
[NoCheckChangePrice] [tinyint] NOT NULL,
[Lot No_] [varchar](20) NOT NULL,
[Sample] [int] NOT NULL,
[Print Picture] [tinyint] NOT NULL,
[Std_ Price Exists] [int] NOT NULL,
[Line Disc Exists] [int] NOT NULL,
UNIQUE CLUSTERED ([Document No_],[Line No_])
)
insert into @tSalesLine
([Document Type],[Sell-to Customer No_],[Document No_],[Line No_],[Type],[No_],[Location Code],[Posting Group],[Shipment Date],[Description],[Description 2],[Unit of Measure],[Quantity],[Outstanding Quantity],[Qty_ to Invoice],[Qty_ to Ship],[Unit Price],[Unit Cost (LCY)],[VAT %],[Line Discount %],[Line Discount Amount],[Amount],[Amount Including VAT],[Allow Invoice Disc_],[Gross Weight],[Net Weight],[Units per Parcel],[Unit Volume],[Appl_-to Item Entry],[Shortcut Dimension 1 Code],[Shortcut Dimension 2 Code],[Customer Price Group],[Job No_],[Work Type Code],[Outstanding Amount],[Qty_ Shipped Not Invoiced],[Shipped Not Invoiced],[Quantity Shipped],[Quantity Invoiced],[Shipment No_],[Shipment Line No_],[Profit %],[Bill-to Customer No_],[Inv_ Discount Amount],[Purchase Order No_],[Purch_ Order Line No_],[Drop Shipment],[Gen_ Bus_ Posting Group],[Gen_ Prod_ Posting Group],[VAT Calculation Type],[Transaction Type],[Transport Method],[Attached to Line No_],[Exit Point],[Area],[Transaction Specification]
,[Tax Area Code],[Tax Liable],[Tax Group Code],[VAT Bus_ Posting Group],[VAT Prod_ Posting Group],[Currency Code],[Outstanding Amount (LCY)],[Shipped Not Invoiced (LCY)],[Reserve],[Blanket Order No_],[Blanket Order Line No_],[VAT Base Amount],[Unit Cost],[System-Created Entry],[Line Amount],[VAT Difference],[Inv_ Disc_ Amount to Invoice],[VAT Identifier],[IC Partner Ref_ Type],[IC Partner Reference],[Prepayment %],[Prepmt_ Line Amount],[Prepmt_ Amt_ Inv_],[Prepmt_ Amt_ Incl_ VAT],[Prepayment Amount],[Prepmt_ VAT Base Amt_],[Prepayment VAT %],[Prepmt_ VAT Calc_ Type],[Prepayment VAT Identifier],[Prepayment Tax Area Code],[Prepayment Tax Liable],[Prepayment Tax Group Code],[Prepmt Amt to Deduct],[Prepmt Amt Deducted],[Prepayment Line],[Prepmt_ Amount Inv_ Incl_ VAT],[Prepmt_ Amount Inv_ (LCY)],[IC Partner Code],[Prepayment VAT Difference],[Prepmt VAT Diff_ to Deduct],[Prepmt VAT Diff_ Deducted],[Job Task No_],[Job Contract Entry No_],[Variant Code],[Bin Code],[Qty_ per Unit of Measure],[Planned]
,[Unit of Measure Code],[Quantity (Base)],[Outstanding Qty_ (Base)],[Qty_ to Invoice (Base)],[Qty_ to Ship (Base)],[Qty_ Shipped Not Invd_ (Base)],[Qty_ Shipped (Base)],[Qty_ Invoiced (Base)],[FA Posting Date],[Depreciation Book Code],[Depr_ until FA Posting Date],[Duplicate in Depreciation Book],[Use Duplication List],[Responsibility Center],[Out-of-Stock Substitution],[Originally Ordered No_],[Originally Ordered Var_ Code],[Cross-Reference No_],[Unit of Measure (Cross Ref_)],[Cross-Reference Type],[Cross-Reference Type No_],[Item Category Code],[Nonstock],[Purchasing Code],[Product Group Code],[Special Order],[Special Order Purchase No_],[Special Order Purch_ Line No_],[Completely Shipped],[Requested Delivery Date],[Promised Delivery Date],[Shipping Time],[Outbound Whse_ Handling Time],[Planned Delivery Date],[Planned Shipment Date],[Shipping Agent Code],[Shipping Agent Service Code],[Allow Item Charge Assignment],[Return Qty_ to Receive],[Return Qty_ to Receive (Base)],[Return Qty_ Rcd_ Not Invd_]
,[Ret_ Qty_ Rcd_ Not Invd_(Base)],[Return Rcd_ Not Invd_],[Return Rcd_ Not Invd_ (LCY)],[Return Qty_ Received],[Return Qty_ Received (Base)],[Appl_-from Item Entry],[BOM Item No_],[Return Receipt No_],[Return Receipt Line No_],[Return Reason Code],[Allow Line Disc_],[Customer Disc_ Group],[Pmt_ Discount Amount],[StandardUnitPrice],[NoPaymentDiscount],[Unit Price M2],[PrintOption],[Territory Code],[Unit Cost Changed],[Tariff No_],[Site],[Site Sublocation],[Invoice Unit of Measure],[Qty in Invoice Measure],[Validation Date],[Close Quote Code],[BorgItem],[Salesperson Code],[Line Discount Amount 1],[Line Discount % 2],[Line Discount Amount 2],[Quote No_],[Contract No_],[Ordered],[Minimum Quantity to Ship],[Purch_ Contract No_],[Purch_ Contract Line],[Shipment End Date],[Planned for Transport],[Supplement],[Quote Posted],[Item Recyclage],[No_OfCollies],[Prim_ Vendor No_],[Simulation],[Line Net Weight],[Line Deliver Weight],[Line Volume],[Line Deliver Volume],[Order No_],[Transport Line],[Detailled Resource]
,[Relocation Resource],[Detailled Activity],[Site from],[Detailled Remark],[Item Shpt_ Entry No_],[Unit Price Incl_ VAT],[Commission %],[Line Type],[BOM Attached Line No_],[Calc Formula],[Parent Line No_],[Original Line No_],[SkipGetPriceQuoteAndContract],[Description 3],[Back Order],[Quantity Back Order],[Qty (Base) Back Order],[Reason Code change Price_Disc],[Orig_ Line Discount %],[Orig_ Line Discount % 2],[Orig_ Line Discount Amount 1],[Orig_ Line Discount Amount 2],[UOM Rounding Precision Unit],[UOM Rounding Precision],[UOM Rounding Type],[Inv_ UOM Round_ Precision Unit],[Inv_ UOM Rounding Precision],[Inv_ UOM Rounding Type],[Base UOM Round_ Precision Unit],[Base UOM Rounding Precision],[Base UOM Rounding Type],[Cad import],[Picking Status],[New Location Code],[New Bin Code],[New Picking Status],[Concrete Supplement],[Custom Code 1],[Price Option],[Building Department],[IC Purch_ Doc No_],[IC Purch_ Line No_],[NoCheckChangePrice],[Lot No_],[Sample],[Print Picture],[Std_ Price Exists],[Line Disc Exists])
select
1,oh.Customer,od.OrderID,(ROW_NUMBER() OVER(PARTITION BY od.OrderID ORDER BY od.OrderID))*10000 As [Line No],
CASE WHEN od.IsTextLine = 0 THEN 2 ELSE 0 END,
CASE WHEN od.PRODID LIKE '%-%[0-9]%' THEN
SUBSTRING(od.PRODID,1,CHARINDEX('-',od.PRODID)-1)
ELSE od.PRODID END
,'MAGAZIJN','VOORRAAD','1/01/2012','','',
ISNULL(od.SaleUnit,''),0,0,0,0,0,0,ISNULL(od.BTWPerc,0),
0,0,0,0,1,0,0,0,0,0,'','','','','',0,0,0,
ISNULL(od.AmountDeliverd,0),0,'',0,0,'',0,'',0,0,'','',0,
'','',0,'','','','',0,'','','','',0,0,0,'',0,0,
ISNULL(od.Costprice,0),0,0,0,0,'',0,'',0,0,0,0,
0,0,0,0,'','',0,'',0,0,0,0,0,'',0,0,0,'',0,'',
UPPER(ISNULL(od.LocationID,'')),0,0,'',0,0,0,0,
0,0,0,'1753-01-01 00:00:00.000',
'',0,'',0,'',0,'','','','',0,'','',0,'','',0,'',0,0,
'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',
'','',0,0,0,0,0,0,0,0,0,0,'','',0,'',0,'',0,0,0,0,0,
'',0,ISNULL(od.Intrastat,''),'',0,ISNULL(od.StockUnit + cast(cast(od.DivKeyNum as integer)as varchar),''),
CASE WHEN ISNULL(AmountAsked,0) > 0 THEN AmountAsked
ELSE ISNULL(AmountReturn,0)*-1 END,
'1753-01-01 00:00:00.000','',0,'',0,0,0,'','',0,0,'',0,
'1753-01-01 00:00:00.000',0,0,0,0,0,'',0,0,0,0,0,'',0,'',0,0,
'','',0,0,0,0,0,'',0,0,0,'',0,0,0,'',0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,'','',0,0,0,0,'','',0,0,'',0,0,0,0
from VMSER_TP7VM.dbo.Orderdetail od
inner join VMSER_TP7VM.dbo.Orderheader oh on od.OrderId = oh.OrderID
MERGE dbo.[Sales Line - Copy] t
USING @tSalesLine s
ON s.[Document No_] = t.[Document No_]
AND s.[Line No_] = t.[Line No_]
WHEN NOT MATCHED BY TARGET THEN
insert
([Document Type],[Sell-to Customer No_],[Document No_],[Line No_],[Type],[No_],[Location Code],[Posting Group],[Shipment Date],[Description],[Description 2],[Unit of Measure],[Quantity],[Outstanding Quantity],[Qty_ to Invoice],[Qty_ to Ship],[Unit Price],[Unit Cost (LCY)],[VAT %],[Line Discount %],[Line Discount Amount],[Amount],[Amount Including VAT],[Allow Invoice Disc_],[Gross Weight],[Net Weight],[Units per Parcel],[Unit Volume],[Appl_-to Item Entry],[Shortcut Dimension 1 Code],[Shortcut Dimension 2 Code],[Customer Price Group],[Job No_],[Work Type Code],[Outstanding Amount],[Qty_ Shipped Not Invoiced],[Shipped Not Invoiced],[Quantity Shipped],[Quantity Invoiced],[Shipment No_],[Shipment Line No_],[Profit %],[Bill-to Customer No_],[Inv_ Discount Amount],[Purchase Order No_],[Purch_ Order Line No_],[Drop Shipment],[Gen_ Bus_ Posting Group],[Gen_ Prod_ Posting Group],[VAT Calculation Type],[Transaction Type],[Transport Method],[Attached to Line No_],[Exit Point],[Area],[Transaction Specification]
,[Tax Area Code],[Tax Liable],[Tax Group Code],[VAT Bus_ Posting Group],[VAT Prod_ Posting Group],[Currency Code],[Outstanding Amount (LCY)],[Shipped Not Invoiced (LCY)],[Reserve],[Blanket Order No_],[Blanket Order Line No_],[VAT Base Amount],[Unit Cost],[System-Created Entry],[Line Amount],[VAT Difference],[Inv_ Disc_ Amount to Invoice],[VAT Identifier],[IC Partner Ref_ Type],[IC Partner Reference],[Prepayment %],[Prepmt_ Line Amount],[Prepmt_ Amt_ Inv_],[Prepmt_ Amt_ Incl_ VAT],[Prepayment Amount],[Prepmt_ VAT Base Amt_],[Prepayment VAT %],[Prepmt_ VAT Calc_ Type],[Prepayment VAT Identifier],[Prepayment Tax Area Code],[Prepayment Tax Liable],[Prepayment Tax Group Code],[Prepmt Amt to Deduct],[Prepmt Amt Deducted],[Prepayment Line],[Prepmt_ Amount Inv_ Incl_ VAT],[Prepmt_ Amount Inv_ (LCY)],[IC Partner Code],[Prepayment VAT Difference],[Prepmt VAT Diff_ to Deduct],[Prepmt VAT Diff_ Deducted],[Job Task No_],[Job Contract Entry No_],[Variant Code],[Bin Code],[Qty_ per Unit of Measure],[Planned]
,[Unit of Measure Code],[Quantity (Base)],[Outstanding Qty_ (Base)],[Qty_ to Invoice (Base)],[Qty_ to Ship (Base)],[Qty_ Shipped Not Invd_ (Base)],[Qty_ Shipped (Base)],[Qty_ Invoiced (Base)],[FA Posting Date],[Depreciation Book Code],[Depr_ until FA Posting Date],[Duplicate in Depreciation Book],[Use Duplication List],[Responsibility Center],[Out-of-Stock Substitution],[Originally Ordered No_],[Originally Ordered Var_ Code],[Cross-Reference No_],[Unit of Measure (Cross Ref_)],[Cross-Reference Type],[Cross-Reference Type No_],[Item Category Code],[Nonstock],[Purchasing Code],[Product Group Code],[Special Order],[Special Order Purchase No_],[Special Order Purch_ Line No_],[Completely Shipped],[Requested Delivery Date],[Promised Delivery Date],[Shipping Time],[Outbound Whse_ Handling Time],[Planned Delivery Date],[Planned Shipment Date],[Shipping Agent Code],[Shipping Agent Service Code],[Allow Item Charge Assignment],[Return Qty_ to Receive],[Return Qty_ to Receive (Base)],[Return Qty_ Rcd_ Not Invd_]
,[Ret_ Qty_ Rcd_ Not Invd_(Base)],[Return Rcd_ Not Invd_],[Return Rcd_ Not Invd_ (LCY)],[Return Qty_ Received],[Return Qty_ Received (Base)],[Appl_-from Item Entry],[BOM Item No_],[Return Receipt No_],[Return Receipt Line No_],[Return Reason Code],[Allow Line Disc_],[Customer Disc_ Group],[Pmt_ Discount Amount],[StandardUnitPrice],[NoPaymentDiscount],[Unit Price M2],[PrintOption],[Territory Code],[Unit Cost Changed],[Tariff No_],[Site],[Site Sublocation],[Invoice Unit of Measure],[Qty in Invoice Measure],[Validation Date],[Close Quote Code],[BorgItem],[Salesperson Code],[Line Discount Amount 1],[Line Discount % 2],[Line Discount Amount 2],[Quote No_],[Contract No_],[Ordered],[Minimum Quantity to Ship],[Purch_ Contract No_],[Purch_ Contract Line],[Shipment End Date],[Planned for Transport],[Supplement],[Quote Posted],[Item Recyclage],[No_OfCollies],[Prim_ Vendor No_],[Simulation],[Line Net Weight],[Line Deliver Weight],[Line Volume],[Line Deliver Volume],[Order No_],[Transport Line],[Detailled Resource]
,[Relocation Resource],[Detailled Activity],[Site from],[Detailled Remark],[Item Shpt_ Entry No_],[Unit Price Incl_ VAT],[Commission %],[Line Type],[BOM Attached Line No_],[Calc Formula],[Parent Line No_],[Original Line No_],[SkipGetPriceQuoteAndContract],[Description 3],[Back Order],[Quantity Back Order],[Qty (Base) Back Order],[Reason Code change Price_Disc],[Orig_ Line Discount %],[Orig_ Line Discount % 2],[Orig_ Line Discount Amount 1],[Orig_ Line Discount Amount 2],[UOM Rounding Precision Unit],[UOM Rounding Precision],[UOM Rounding Type],[Inv_ UOM Round_ Precision Unit],[Inv_ UOM Rounding Precision],[Inv_ UOM Rounding Type],[Base UOM Round_ Precision Unit],[Base UOM Rounding Precision],[Base UOM Rounding Type],[Cad import],[Picking Status],[New Location Code],[New Bin Code],[New Picking Status],[Concrete Supplement],[Custom Code 1],[Price Option],[Building Department],[IC Purch_ Doc No_],[IC Purch_ Line No_],[NoCheckChangePrice],[Lot No_],[Sample],[Print Picture],[Std_ Price Exists],[Line Disc Exists])
values
(s.[Document Type],s.[Sell-to Customer No_],s.[Document No_],s.[Line No_],s.[Type],s.[No_],s.[Location Code],s.[Posting Group],s.[Shipment Date],s.[Description],s.[Description 2],s.[Unit of Measure],s.[Quantity],s.[Outstanding Quantity],s.[Qty_ to Invoice],s.[Qty_ to Ship],s.[Unit Price],s.[Unit Cost (LCY)],s.[VAT %],s.[Line Discount %],s.[Line Discount Amount],s.[Amount],s.[Amount Including VAT],s.[Allow Invoice Disc_],s.[Gross Weight],s.[Net Weight],s.[Units per Parcel],s.[Unit Volume],s.[Appl_-to Item Entry],s.[Shortcut Dimension 1 Code],s.[Shortcut Dimension 2 Code],s.[Customer Price Group],s.[Job No_],s.[Work Type Code],s.[Outstanding Amount],s.[Qty_ Shipped Not Invoiced],s.[Shipped Not Invoiced],s.[Quantity Shipped],s.[Quantity Invoiced],s.[Shipment No_],s.[Shipment Line No_],s.[Profit %],s.[Bill-to Customer No_],s.[Inv_ Discount Amount],s.[Purchase Order No_],s.[Purch_ Order Line No_],s.[Drop Shipment],s.[Gen_ Bus_ Posting Group],s.[Gen_ Prod_ Posting Group],s.[VAT Calculation Type],s.[Transaction Type],s.[Transport Method],s.[Attached to Line No_],s.[Exit Point],s.[Area],s.[Transaction Specification]
,[Tax Area Code],s.[Tax Liable],s.[Tax Group Code],s.[VAT Bus_ Posting Group],s.[VAT Prod_ Posting Group],s.[Currency Code],s.[Outstanding Amount (LCY)],s.[Shipped Not Invoiced (LCY)],s.[Reserve],s.[Blanket Order No_],s.[Blanket Order Line No_],s.[VAT Base Amount],s.[Unit Cost],s.[System-Created Entry],s.[Line Amount],s.[VAT Difference],s.[Inv_ Disc_ Amount to Invoice],s.[VAT Identifier],s.[IC Partner Ref_ Type],s.[IC Partner Reference],s.[Prepayment %],s.[Prepmt_ Line Amount],s.[Prepmt_ Amt_ Inv_],s.[Prepmt_ Amt_ Incl_ VAT],s.[Prepayment Amount],s.[Prepmt_ VAT Base Amt_],s.[Prepayment VAT %],s.[Prepmt_ VAT Calc_ Type],s.[Prepayment VAT Identifier],s.[Prepayment Tax Area Code],s.[Prepayment Tax Liable],s.[Prepayment Tax Group Code],s.[Prepmt Amt to Deduct],s.[Prepmt Amt Deducted],s.[Prepayment Line],s.[Prepmt_ Amount Inv_ Incl_ VAT],s.[Prepmt_ Amount Inv_ (LCY)],s.[IC Partner Code],s.[Prepayment VAT Difference],s.[Prepmt VAT Diff_ to Deduct],s.[Prepmt VAT Diff_ Deducted],s.[Job Task No_],s.[Job Contract Entry No_],s.[Variant Code],s.[Bin Code],s.[Qty_ per Unit of Measure],s.[Planned]
,[Unit of Measure Code],s.[Quantity (Base)],s.[Outstanding Qty_ (Base)],s.[Qty_ to Invoice (Base)],s.[Qty_ to Ship (Base)],s.[Qty_ Shipped Not Invd_ (Base)],s.[Qty_ Shipped (Base)],s.[Qty_ Invoiced (Base)],s.[FA Posting Date],s.[Depreciation Book Code],s.[Depr_ until FA Posting Date],s.[Duplicate in Depreciation Book],s.[Use Duplication List],s.[Responsibility Center],s.[Out-of-Stock Substitution],s.[Originally Ordered No_],s.[Originally Ordered Var_ Code],s.[Cross-Reference No_],s.[Unit of Measure (Cross Ref_)],s.[Cross-Reference Type],s.[Cross-Reference Type No_],s.[Item Category Code],s.[Nonstock],s.[Purchasing Code],s.[Product Group Code],s.[Special Order],s.[Special Order Purchase No_],s.[Special Order Purch_ Line No_],s.[Completely Shipped],s.[Requested Delivery Date],s.[Promised Delivery Date],s.[Shipping Time],s.[Outbound Whse_ Handling Time],s.[Planned Delivery Date],s.[Planned Shipment Date],s.[Shipping Agent Code],s.[Shipping Agent Service Code],s.[Allow Item Charge Assignment],s.[Return Qty_ to Receive],s.[Return Qty_ to Receive (Base)],s.[Return Qty_ Rcd_ Not Invd_]
,[Ret_ Qty_ Rcd_ Not Invd_(Base)],s.[Return Rcd_ Not Invd_],s.[Return Rcd_ Not Invd_ (LCY)],s.[Return Qty_ Received],s.[Return Qty_ Received (Base)],s.[Appl_-from Item Entry],s.[BOM Item No_],s.[Return Receipt No_],s.[Return Receipt Line No_],s.[Return Reason Code],s.[Allow Line Disc_],s.[Customer Disc_ Group],s.[Pmt_ Discount Amount],s.[StandardUnitPrice],s.[NoPaymentDiscount],s.[Unit Price M2],s.[PrintOption],s.[Territory Code],s.[Unit Cost Changed],s.[Tariff No_],s.[Site],s.[Site Sublocation],s.[Invoice Unit of Measure],s.[Qty in Invoice Measure],s.[Validation Date],s.[Close Quote Code],s.[BorgItem],s.[Salesperson Code],s.[Line Discount Amount 1],s.[Line Discount % 2],s.[Line Discount Amount 2],s.[Quote No_],s.[Contract No_],s.[Ordered],s.[Minimum Quantity to Ship],s.[Purch_ Contract No_],s.[Purch_ Contract Line],s.[Shipment End Date],s.[Planned for Transport],s.[Supplement],s.[Quote Posted],s.[Item Recyclage],s.[No_OfCollies],s.[Prim_ Vendor No_],s.[Simulation],s.[Line Net Weight],s.[Line Deliver Weight],s.[Line Volume],s.[Line Deliver Volume],s.[Order No_],s.[Transport Line],s.[Detailled Resource]
,[Relocation Resource],s.[Detailled Activity],s.[Site from],s.[Detailled Remark],s.[Item Shpt_ Entry No_],s.[Unit Price Incl_ VAT],s.[Commission %],s.[Line Type],s.[BOM Attached Line No_],s.[Calc Formula],s.[Parent Line No_],s.[Original Line No_],s.[SkipGetPriceQuoteAndContract],s.[Description 3],s.[Back Order],s.[Quantity Back Order],s.[Qty (Base) Back Order],s.[Reason Code change Price_Disc],s.[Orig_ Line Discount %],s.[Orig_ Line Discount % 2],s.[Orig_ Line Discount Amount 1],s.[Orig_ Line Discount Amount 2],s.[UOM Rounding Precision Unit],s.[UOM Rounding Precision],s.[UOM Rounding Type],s.[Inv_ UOM Round_ Precision Unit],s.[Inv_ UOM Rounding Precision],s.[Inv_ UOM Rounding Type],s.[Base UOM Round_ Precision Unit],s.[Base UOM Rounding Precision],s.[Base UOM Rounding Type],s.[Cad import],s.[Picking Status],s.[New Location Code],s.[New Bin Code],s.[New Picking Status],s.[Concrete Supplement],s.[Custom Code 1],s.[Price Option],s.[Building Department],s.[IC Purch_ Doc No_],s.[IC Purch_ Line No_],s.[NoCheckChangePrice],s.[Lot No_],s.[Sample],s.[Print Picture],s.[Std_ Price Exists],s.[Line Disc Exists]);
Stored procedure with table spool
declare @tSalesLine table
(
[Document Type] [int] NOT NULL,
[Document No_] [varchar](20) NOT NULL,
[Line No_] [int] NOT NULL,
[Sell-to Customer No_] [varchar](20) NOT NULL,
[Type] [int] NOT NULL,
[No_] [varchar](20) NOT NULL,
[Location Code] [varchar](10) NOT NULL,
[Posting Group] [varchar](10) NOT NULL,
[Shipment Date] [datetime] NOT NULL,
[Description] [varchar](50) NOT NULL,
[Description 2] [varchar](50) NOT NULL,
[Unit of Measure] [varchar](10) NOT NULL,
[Quantity] [decimal](38, 20) NOT NULL,
[Outstanding Quantity] [decimal](38, 20) NOT NULL,
[Qty_ to Invoice] [decimal](38, 20) NOT NULL,
[Qty_ to Ship] [decimal](38, 20) NOT NULL,
[Unit Price] [decimal](38, 20) NOT NULL,
[Unit Cost (LCY)] [decimal](38, 20) NOT NULL,
[VAT %] [decimal](38, 20) NOT NULL,
[Line Discount %] [decimal](38, 20) NOT NULL,
[Line Discount Amount] [decimal](38, 20) NOT NULL,
[Amount] [decimal](38, 20) NOT NULL,
[Amount Including VAT] [decimal](38, 20) NOT NULL,
[Allow Invoice Disc_] [tinyint] NOT NULL,
[Gross Weight] [decimal](38, 20) NOT NULL,
[Net Weight] [decimal](38, 20) NOT NULL,
[Units per Parcel] [decimal](38, 20) NOT NULL,
[Unit Volume] [decimal](38, 20) NOT NULL,
[Appl_-to Item Entry] [int] NOT NULL,
[Shortcut Dimension 1 Code] [varchar](20) NOT NULL,
[Shortcut Dimension 2 Code] [varchar](20) NOT NULL,
[Customer Price Group] [varchar](10) NOT NULL,
[Job No_] [varchar](20) NOT NULL,
[Work Type Code] [varchar](10) NOT NULL,
[Outstanding Amount] [decimal](38, 20) NOT NULL,
[Qty_ Shipped Not Invoiced] [decimal](38, 20) NOT NULL,
[Shipped Not Invoiced] [decimal](38, 20) NOT NULL,
[Quantity Shipped] [decimal](38, 20) NOT NULL,
[Quantity Invoiced] [decimal](38, 20) NOT NULL,
[Shipment No_] [varchar](20) NOT NULL,
[Shipment Line No_] [int] NOT NULL,
[Profit %] [decimal](38, 20) NOT NULL,
[Bill-to Customer No_] [varchar](20) NOT NULL,
[Inv_ Discount Amount] [decimal](38, 20) NOT NULL,
[Purchase Order No_] [varchar](20) NOT NULL,
[Purch_ Order Line No_] [int] NOT NULL,
[Drop Shipment] [tinyint] NOT NULL,
[Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
[Gen_ Prod_ Posting Group] [varchar](10) NOT NULL,
[VAT Calculation Type] [int] NOT NULL,
[Transaction Type] [varchar](10) NOT NULL,
[Transport Method] [varchar](10) NOT NULL,
[Attached to Line No_] [int] NOT NULL,
[Exit Point] [varchar](10) NOT NULL,
[Area] [varchar](10) NOT NULL,
[Transaction Specification] [varchar](10) NOT NULL,
[Tax Area Code] [varchar](20) NOT NULL,
[Tax Liable] [tinyint] NOT NULL,
[Tax Group Code] [varchar](10) NOT NULL,
[VAT Bus_ Posting Group] [varchar](10) NOT NULL,
[VAT Prod_ Posting Group] [varchar](10) NOT NULL,
[Currency Code] [varchar](10) NOT NULL,
[Outstanding Amount (LCY)] [decimal](38, 20) NOT NULL,
[Shipped Not Invoiced (LCY)] [decimal](38, 20) NOT NULL,
[Reserve] [int] NOT NULL,
[Blanket Order No_] [varchar](20) NOT NULL,
[Blanket Order Line No_] [int] NOT NULL,
[VAT Base Amount] [decimal](38, 20) NOT NULL,
[Unit Cost] [decimal](38, 20) NOT NULL,
[System-Created Entry] [tinyint] NOT NULL,
[Line Amount] [decimal](38, 20) NOT NULL,
[VAT Difference] [decimal](38, 20) NOT NULL,
[Inv_ Disc_ Amount to Invoice] [decimal](38, 20) NOT NULL,
[VAT Identifier] [varchar](10) NOT NULL,
[IC Partner Ref_ Type] [int] NOT NULL,
[IC Partner Reference] [varchar](20) NOT NULL,
[Prepayment %] [decimal](38, 20) NOT NULL,
[Prepmt_ Line Amount] [decimal](38, 20) NOT NULL,
[Prepmt_ Amt_ Inv_] [decimal](38, 20) NOT NULL,
[Prepmt_ Amt_ Incl_ VAT] [decimal](38, 20) NOT NULL,
[Prepayment Amount] [decimal](38, 20) NOT NULL,
[Prepmt_ VAT Base Amt_] [decimal](38, 20) NOT NULL,
[Prepayment VAT %] [decimal](38, 20) NOT NULL,
[Prepmt_ VAT Calc_ Type] [int] NOT NULL,
[Prepayment VAT Identifier] [varchar](10) NOT NULL,
[Prepayment Tax Area Code] [varchar](20) NOT NULL,
[Prepayment Tax Liable] [tinyint] NOT NULL,
[Prepayment Tax Group Code] [varchar](10) NOT NULL,
[Prepmt Amt to Deduct] [decimal](38, 20) NOT NULL,
[Prepmt Amt Deducted] [decimal](38, 20) NOT NULL,
[Prepayment Line] [tinyint] NOT NULL,
[Prepmt_ Amount Inv_ Incl_ VAT] [decimal](38, 20) NOT NULL,
[Prepmt_ Amount Inv_ (LCY)] [decimal](38, 20) NOT NULL,
[IC Partner Code] [varchar](20) NOT NULL,
[Prepayment VAT Difference] [decimal](38, 20) NOT NULL,
[Prepmt VAT Diff_ to Deduct] [decimal](38, 20) NOT NULL,
[Prepmt VAT Diff_ Deducted] [decimal](38, 20) NOT NULL,
[Job Task No_] [varchar](20) NOT NULL,
[Job Contract Entry No_] [int] NOT NULL,
[Variant Code] [varchar](10) NOT NULL,
[Bin Code] [varchar](20) NOT NULL,
[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
[Planned] [tinyint] NOT NULL,
[Unit of Measure Code] [varchar](10) NOT NULL,
[Quantity (Base)] [decimal](38, 20) NOT NULL,
[Outstanding Qty_ (Base)] [decimal](38, 20) NOT NULL,
[Qty_ to Invoice (Base)] [decimal](38, 20) NOT NULL,
[Qty_ to Ship (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Shipped Not Invd_ (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Shipped (Base)] [decimal](38, 20) NOT NULL,
[Qty_ Invoiced (Base)] [decimal](38, 20) NOT NULL,
[FA Posting Date] [datetime] NOT NULL,
[Depreciation Book Code] [varchar](10) NOT NULL,
[Depr_ until FA Posting Date] [tinyint] NOT NULL,
[Duplicate in Depreciation Book] [varchar](10) NOT NULL,
[Use Duplication List] [tinyint] NOT NULL,
[Responsibility Center] [varchar](10) NOT NULL,
[Out-of-Stock Substitution] [tinyint] NOT NULL,
[Originally Ordered No_] [varchar](20) NOT NULL,
[Originally Ordered Var_ Code] [varchar](10) NOT NULL,
[Cross-Reference No_] [varchar](20) NOT NULL,
[Unit of Measure (Cross Ref_)] [varchar](10) NOT NULL,
[Cross-Reference Type] [int] NOT NULL,
[Cross-Reference Type No_] [varchar](30) NOT NULL,
[Item Category Code] [varchar](10) NOT NULL,
[Nonstock] [tinyint] NOT NULL,
[Purchasing Code] [varchar](10) NOT NULL,
[Product Group Code] [varchar](10) NOT NULL,
[Special Order] [tinyint] NOT NULL,
[Special Order Purchase No_] [varchar](20) NOT NULL,
[Special Order Purch_ Line No_] [int] NOT NULL,
[Completely Shipped] [tinyint] NOT NULL,
[Requested Delivery Date] [datetime] NOT NULL,
[Promised Delivery Date] [datetime] NOT NULL,
[Shipping Time] [varchar](32) NOT NULL,
[Outbound Whse_ Handling Time] [varchar](32) NOT NULL,
[Planned Delivery Date] [datetime] NOT NULL,
[Planned Shipment Date] [datetime] NOT NULL,
[Shipping Agent Code] [varchar](10) NOT NULL,
[Shipping Agent Service Code] [varchar](10) NOT NULL,
[Allow Item Charge Assignment] [tinyint] NOT NULL,
[Return Qty_ to Receive] [decimal](38, 20) NOT NULL,
[Return Qty_ to Receive (Base)] [decimal](38, 20) NOT NULL,
[Return Qty_ Rcd_ Not Invd_] [decimal](38, 20) NOT NULL,
[Ret_ Qty_ Rcd_ Not Invd_(Base)] [decimal](38, 20) NOT NULL,
[Return Rcd_ Not Invd_] [decimal](38, 20) NOT NULL,
[Return Rcd_ Not Invd_ (LCY)] [decimal](38, 20) NOT NULL,
[Return Qty_ Received] [decimal](38, 20) NOT NULL,
[Return Qty_ Received (Base)] [decimal](38, 20) NOT NULL,
[Appl_-from Item Entry] [int] NOT NULL,
[BOM Item No_] [varchar](20) NOT NULL,
[Return Receipt No_] [varchar](20) NOT NULL,
[Return Receipt Line No_] [int] NOT NULL,
[Return Reason Code] [varchar](10) NOT NULL,
[Allow Line Disc_] [tinyint] NOT NULL,
[Customer Disc_ Group] [varchar](10) NOT NULL,
[Pmt_ Discount Amount] [decimal](38, 20) NOT NULL,
[StandardUnitPrice] [decimal](38, 20) NOT NULL,
[NoPaymentDiscount] [tinyint] NOT NULL,
[Unit Price M2] [decimal](38, 20) NOT NULL,
[PrintOption] [int] NOT NULL,
[Territory Code] [varchar](10) NOT NULL,
[Unit Cost Changed] [tinyint] NOT NULL,
[Tariff No_] [varchar](10) NOT NULL,
[Site] [varchar](20) NOT NULL,
[Site Sublocation] [int] NOT NULL,
[Invoice Unit of Measure] [varchar](10) NOT NULL,
[Qty in Invoice Measure] [decimal](38, 20) NOT NULL,
[Validation Date] [datetime] NOT NULL,
[Close Quote Code] [varchar](10) NOT NULL,
[BorgItem] [tinyint] NOT NULL,
[Salesperson Code] [varchar](10) NOT NULL,
[Line Discount Amount 1] [decimal](38, 20) NOT NULL,
[Line Discount % 2] [decimal](38, 20) NOT NULL,
[Line Discount Amount 2] [decimal](38, 20) NOT NULL,
[Quote No_] [varchar](20) NOT NULL,
[Contract No_] [varchar](20) NOT NULL,
[Ordered] [tinyint] NOT NULL,
[Minimum Quantity to Ship] [decimal](38, 20) NOT NULL,
[Purch_ Contract No_] [varchar](20) NOT NULL,
[Purch_ Contract Line] [int] NOT NULL,
[Shipment End Date] [datetime] NOT NULL,
[Planned for Transport] [tinyint] NOT NULL,
[Supplement] [tinyint] NOT NULL,
[Quote Posted] [tinyint] NOT NULL,
[Item Recyclage] [tinyint] NOT NULL,
[No_OfCollies] [int] NOT NULL,
[Prim_ Vendor No_] [varchar](20) NOT NULL,
[Simulation] [tinyint] NOT NULL,
[Line Net Weight] [decimal](38, 20) NOT NULL,
[Line Deliver Weight] [decimal](38, 20) NOT NULL,
[Line Volume] [decimal](38, 20) NOT NULL,
[Line Deliver Volume] [decimal](38, 20) NOT NULL,
[Order No_] [varchar](20) NOT NULL,
[Transport Line] [tinyint] NOT NULL,
[Detailled Resource] [varchar](20) NOT NULL,
[Relocation Resource] [tinyint] NOT NULL,
[Detailled Activity] [int] NOT NULL,
[Site from] [varchar](20) NOT NULL,
[Detailled Remark] [varchar](80) NOT NULL,
[Item Shpt_ Entry No_] [int] NOT NULL,
[Unit Price Incl_ VAT] [decimal](38, 20) NOT NULL,
[Commission %] [decimal](38, 20) NOT NULL,
[Line Type] [int] NOT NULL,
[BOM Attached Line No_] [int] NOT NULL,
[Calc Formula] [varchar](50) NOT NULL,
[Parent Line No_] [int] NOT NULL,
[Original Line No_] [int] NOT NULL,
[SkipGetPriceQuoteAndContract] [tinyint] NOT NULL,
[Description 3] [varchar](100) NOT NULL,
[Back Order] [tinyint] NOT NULL,
[Quantity Back Order] [decimal](38, 20) NOT NULL,
[Qty (Base) Back Order] [decimal](38, 20) NOT NULL,
[Reason Code change Price_Disc] [varchar](10) NOT NULL,
[Orig_ Line Discount %] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount % 2] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount Amount 1] [decimal](38, 20) NOT NULL,
[Orig_ Line Discount Amount 2] [decimal](38, 20) NOT NULL,
[UOM Rounding Precision Unit] [decimal](38, 20) NOT NULL,
[UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[UOM Rounding Type] [int] NOT NULL,
[Inv_ UOM Round_ Precision Unit] [decimal](38, 20) NOT NULL,
[Inv_ UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[Inv_ UOM Rounding Type] [int] NOT NULL,
[Base UOM Round_ Precision Unit] [decimal](38, 20) NOT NULL,
[Base UOM Rounding Precision] [decimal](38, 20) NOT NULL,
[Base UOM Rounding Type] [int] NOT NULL,
[Cad import] [tinyint] NOT NULL,
[Picking Status] [int] NOT NULL,
[New Location Code] [varchar](10) NOT NULL,
[New Bin Code] [varchar](20) NOT NULL,
[New Picking Status] [int] NOT NULL,
[Concrete Supplement] [tinyint] NOT NULL,
[Custom Code 1] [tinyint] NOT NULL,
[Price Option] [int] NOT NULL,
[Building Department] [varchar](10) NOT NULL,
[IC Purch_ Doc No_] [varchar](20) NOT NULL,
[IC Purch_ Line No_] [int] NOT NULL,
[NoCheckChangePrice] [tinyint] NOT NULL,
[Lot No_] [varchar](20) NOT NULL,
[Sample] [int] NOT NULL,
[Print Picture] [tinyint] NOT NULL,
[Std_ Price Exists] [int] NOT NULL,
[Line Disc Exists] [int] NOT NULL--,
--UNIQUE CLUSTERED ([Document No_],[Line No_])
)
insert into @tSalesLine
([Document Type],[Sell-to Customer No_],[Document No_],[Line No_],[Type],[No_],[Location Code],[Posting Group],[Shipment Date],[Description],[Description 2],[Unit of Measure],[Quantity],[Outstanding Quantity],[Qty_ to Invoice],[Qty_ to Ship],[Unit Price],[Unit Cost (LCY)],[VAT %],[Line Discount %],[Line Discount Amount],[Amount],[Amount Including VAT],[Allow Invoice Disc_],[Gross Weight],[Net Weight],[Units per Parcel],[Unit Volume],[Appl_-to Item Entry],[Shortcut Dimension 1 Code],[Shortcut Dimension 2 Code],[Customer Price Group],[Job No_],[Work Type Code],[Outstanding Amount],[Qty_ Shipped Not Invoiced],[Shipped Not Invoiced],[Quantity Shipped],[Quantity Invoiced],[Shipment No_],[Shipment Line No_],[Profit %],[Bill-to Customer No_],[Inv_ Discount Amount],[Purchase Order No_],[Purch_ Order Line No_],[Drop Shipment],[Gen_ Bus_ Posting Group],[Gen_ Prod_ Posting Group],[VAT Calculation Type],[Transaction Type],[Transport Method],[Attached to Line No_],[Exit Point],[Area],[Transaction Specification]
,[Tax Area Code],[Tax Liable],[Tax Group Code],[VAT Bus_ Posting Group],[VAT Prod_ Posting Group],[Currency Code],[Outstanding Amount (LCY)],[Shipped Not Invoiced (LCY)],[Reserve],[Blanket Order No_],[Blanket Order Line No_],[VAT Base Amount],[Unit Cost],[System-Created Entry],[Line Amount],[VAT Difference],[Inv_ Disc_ Amount to Invoice],[VAT Identifier],[IC Partner Ref_ Type],[IC Partner Reference],[Prepayment %],[Prepmt_ Line Amount],[Prepmt_ Amt_ Inv_],[Prepmt_ Amt_ Incl_ VAT],[Prepayment Amount],[Prepmt_ VAT Base Amt_],[Prepayment VAT %],[Prepmt_ VAT Calc_ Type],[Prepayment VAT Identifier],[Prepayment Tax Area Code],[Prepayment Tax Liable],[Prepayment Tax Group Code],[Prepmt Amt to Deduct],[Prepmt Amt Deducted],[Prepayment Line],[Prepmt_ Amount Inv_ Incl_ VAT],[Prepmt_ Amount Inv_ (LCY)],[IC Partner Code],[Prepayment VAT Difference],[Prepmt VAT Diff_ to Deduct],[Prepmt VAT Diff_ Deducted],[Job Task No_],[Job Contract Entry No_],[Variant Code],[Bin Code],[Qty_ per Unit of Measure],[Planned]
,[Unit of Measure Code],[Quantity (Base)],[Outstanding Qty_ (Base)],[Qty_ to Invoice (Base)],[Qty_ to Ship (Base)],[Qty_ Shipped Not Invd_ (Base)],[Qty_ Shipped (Base)],[Qty_ Invoiced (Base)],[FA Posting Date],[Depreciation Book Code],[Depr_ until FA Posting Date],[Duplicate in Depreciation Book],[Use Duplication List],[Responsibility Center],[Out-of-Stock Substitution],[Originally Ordered No_],[Originally Ordered Var_ Code],[Cross-Reference No_],[Unit of Measure (Cross Ref_)],[Cross-Reference Type],[Cross-Reference Type No_],[Item Category Code],[Nonstock],[Purchasing Code],[Product Group Code],[Special Order],[Special Order Purchase No_],[Special Order Purch_ Line No_],[Completely Shipped],[Requested Delivery Date],[Promised Delivery Date],[Shipping Time],[Outbound Whse_ Handling Time],[Planned Delivery Date],[Planned Shipment Date],[Shipping Agent Code],[Shipping Agent Service Code],[Allow Item Charge Assignment],[Return Qty_ to Receive],[Return Qty_ to Receive (Base)],[Return Qty_ Rcd_ Not Invd_]
,[Ret_ Qty_ Rcd_ Not Invd_(Base)],[Return Rcd_ Not Invd_],[Return Rcd_ Not Invd_ (LCY)],[Return Qty_ Received],[Return Qty_ Received (Base)],[Appl_-from Item Entry],[BOM Item No_],[Return Receipt No_],[Return Receipt Line No_],[Return Reason Code],[Allow Line Disc_],[Customer Disc_ Group],[Pmt_ Discount Amount],[StandardUnitPrice],[NoPaymentDiscount],[Unit Price M2],[PrintOption],[Territory Code],[Unit Cost Changed],[Tariff No_],[Site],[Site Sublocation],[Invoice Unit of Measure],[Qty in Invoice Measure],[Validation Date],[Close Quote Code],[BorgItem],[Salesperson Code],[Line Discount Amount 1],[Line Discount % 2],[Line Discount Amount 2],[Quote No_],[Contract No_],[Ordered],[Minimum Quantity to Ship],[Purch_ Contract No_],[Purch_ Contract Line],[Shipment End Date],[Planned for Transport],[Supplement],[Quote Posted],[Item Recyclage],[No_OfCollies],[Prim_ Vendor No_],[Simulation],[Line Net Weight],[Line Deliver Weight],[Line Volume],[Line Deliver Volume],[Order No_],[Transport Line],[Detailled Resource]
,[Relocation Resource],[Detailled Activity],[Site from],[Detailled Remark],[Item Shpt_ Entry No_],[Unit Price Incl_ VAT],[Commission %],[Line Type],[BOM Attached Line No_],[Calc Formula],[Parent Line No_],[Original Line No_],[SkipGetPriceQuoteAndContract],[Description 3],[Back Order],[Quantity Back Order],[Qty (Base) Back Order],[Reason Code change Price_Disc],[Orig_ Line Discount %],[Orig_ Line Discount % 2],[Orig_ Line Discount Amount 1],[Orig_ Line Discount Amount 2],[UOM Rounding Precision Unit],[UOM Rounding Precision],[UOM Rounding Type],[Inv_ UOM Round_ Precision Unit],[Inv_ UOM Rounding Precision],[Inv_ UOM Rounding Type],[Base UOM Round_ Precision Unit],[Base UOM Rounding Precision],[Base UOM Rounding Type],[Cad import],[Picking Status],[New Location Code],[New Bin Code],[New Picking Status],[Concrete Supplement],[Custom Code 1],[Price Option],[Building Department],[IC Purch_ Doc No_],[IC Purch_ Line No_],[NoCheckChangePrice],[Lot No_],[Sample],[Print Picture],[Std_ Price Exists],[Line Disc Exists])
select
1,oh.Customer,od.OrderID,(ROW_NUMBER() OVER(PARTITION BY od.OrderID ORDER BY od.OrderID))*10000 As [Line No],
CASE WHEN od.IsTextLine = 0 THEN 2 ELSE 0 END,
CASE WHEN od.PRODID LIKE '%-%[0-9]%' THEN
SUBSTRING(od.PRODID,1,CHARINDEX('-',od.PRODID)-1)
ELSE od.PRODID END
,'MAGAZIJN','VOORRAAD','1/01/2012','','',
ISNULL(od.SaleUnit,''),0,0,0,0,0,0,ISNULL(od.BTWPerc,0),
0,0,0,0,1,0,0,0,0,0,'','','','','',0,0,0,
ISNULL(od.AmountDeliverd,0),0,'',0,0,'',0,'',0,0,'','',0,
'','',0,'','','','',0,'','','','',0,0,0,'',0,0,
ISNULL(od.Costprice,0),0,0,0,0,'',0,'',0,0,0,0,
0,0,0,0,'','',0,'',0,0,0,0,0,'',0,0,0,'',0,'',
UPPER(ISNULL(od.LocationID,'')),0,0,'',0,0,0,0,
0,0,0,'1753-01-01 00:00:00.000',
'',0,'',0,'',0,'','','','',0,'','',0,'','',0,'',0,0,
'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000','1753-01-01 00:00:00.000',
'','',0,0,0,0,0,0,0,0,0,0,'','',0,'',0,'',0,0,0,0,0,
'',0,ISNULL(od.Intrastat,''),'',0,ISNULL(od.StockUnit + cast(cast(od.DivKeyNum as integer)as varchar),''),
CASE WHEN ISNULL(AmountAsked,0) > 0 THEN AmountAsked
ELSE ISNULL(AmountReturn,0)*-1 END,
'1753-01-01 00:00:00.000','',0,'',0,0,0,'','',0,0,'',0,
'1753-01-01 00:00:00.000',0,0,0,0,0,'',0,0,0,0,0,'',0,'',0,0,
'','',0,0,0,0,0,'',0,0,0,'',0,0,0,'',0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,'','',0,0,0,0,'','',0,0,'',0,0,0,0
from VMSER_TP7VM.dbo.Orderdetail od
inner join VMSER_TP7VM.dbo.Orderheader oh on od.OrderId = oh.OrderID
MERGE dbo.[Sales Line - Copy] t
USING @tSalesLine s
ON s.[Document No_] = t.[Document No_]
AND s.[Line No_] = t.[Line No_]
WHEN NOT MATCHED BY TARGET THEN
insert
([Document Type],[Sell-to Customer No_],[Document No_],[Line No_],[Type],[No_],[Location Code],[Posting Group],[Shipment Date],[Description],[Description 2],[Unit of Measure],[Quantity],[Outstanding Quantity],[Qty_ to Invoice],[Qty_ to Ship],[Unit Price],[Unit Cost (LCY)],[VAT %],[Line Discount %],[Line Discount Amount],[Amount],[Amount Including VAT],[Allow Invoice Disc_],[Gross Weight],[Net Weight],[Units per Parcel],[Unit Volume],[Appl_-to Item Entry],[Shortcut Dimension 1 Code],[Shortcut Dimension 2 Code],[Customer Price Group],[Job No_],[Work Type Code],[Outstanding Amount],[Qty_ Shipped Not Invoiced],[Shipped Not Invoiced],[Quantity Shipped],[Quantity Invoiced],[Shipment No_],[Shipment Line No_],[Profit %],[Bill-to Customer No_],[Inv_ Discount Amount],[Purchase Order No_],[Purch_ Order Line No_],[Drop Shipment],[Gen_ Bus_ Posting Group],[Gen_ Prod_ Posting Group],[VAT Calculation Type],[Transaction Type],[Transport Method],[Attached to Line No_],[Exit Point],[Area],[Transaction Specification]
,[Tax Area Code],[Tax Liable],[Tax Group Code],[VAT Bus_ Posting Group],[VAT Prod_ Posting Group],[Currency Code],[Outstanding Amount (LCY)],[Shipped Not Invoiced (LCY)],[Reserve],[Blanket Order No_],[Blanket Order Line No_],[VAT Base Amount],[Unit Cost],[System-Created Entry],[Line Amount],[VAT Difference],[Inv_ Disc_ Amount to Invoice],[VAT Identifier],[IC Partner Ref_ Type],[IC Partner Reference],[Prepayment %],[Prepmt_ Line Amount],[Prepmt_ Amt_ Inv_],[Prepmt_ Amt_ Incl_ VAT],[Prepayment Amount],[Prepmt_ VAT Base Amt_],[Prepayment VAT %],[Prepmt_ VAT Calc_ Type],[Prepayment VAT Identifier],[Prepayment Tax Area Code],[Prepayment Tax Liable],[Prepayment Tax Group Code],[Prepmt Amt to Deduct],[Prepmt Amt Deducted],[Prepayment Line],[Prepmt_ Amount Inv_ Incl_ VAT],[Prepmt_ Amount Inv_ (LCY)],[IC Partner Code],[Prepayment VAT Difference],[Prepmt VAT Diff_ to Deduct],[Prepmt VAT Diff_ Deducted],[Job Task No_],[Job Contract Entry No_],[Variant Code],[Bin Code],[Qty_ per Unit of Measure],[Planned]
,[Unit of Measure Code],[Quantity (Base)],[Outstanding Qty_ (Base)],[Qty_ to Invoice (Base)],[Qty_ to Ship (Base)],[Qty_ Shipped Not Invd_ (Base)],[Qty_ Shipped (Base)],[Qty_ Invoiced (Base)],[FA Posting Date],[Depreciation Book Code],[Depr_ until FA Posting Date],[Duplicate in Depreciation Book],[Use Duplication List],[Responsibility Center],[Out-of-Stock Substitution],[Originally Ordered No_],[Originally Ordered Var_ Code],[Cross-Reference No_],[Unit of Measure (Cross Ref_)],[Cross-Reference Type],[Cross-Reference Type No_],[Item Category Code],[Nonstock],[Purchasing Code],[Product Group Code],[Special Order],[Special Order Purchase No_],[Special Order Purch_ Line No_],[Completely Shipped],[Requested Delivery Date],[Promised Delivery Date],[Shipping Time],[Outbound Whse_ Handling Time],[Planned Delivery Date],[Planned Shipment Date],[Shipping Agent Code],[Shipping Agent Service Code],[Allow Item Charge Assignment],[Return Qty_ to Receive],[Return Qty_ to Receive (Base)],[Return Qty_ Rcd_ Not Invd_]
,[Ret_ Qty_ Rcd_ Not Invd_(Base)],[Return Rcd_ Not Invd_],[Return Rcd_ Not Invd_ (LCY)],[Return Qty_ Received],[Return Qty_ Received (Base)],[Appl_-from Item Entry],[BOM Item No_],[Return Receipt No_],[Return Receipt Line No_],[Return Reason Code],[Allow Line Disc_],[Customer Disc_ Group],[Pmt_ Discount Amount],[StandardUnitPrice],[NoPaymentDiscount],[Unit Price M2],[PrintOption],[Territory Code],[Unit Cost Changed],[Tariff No_],[Site],[Site Sublocation],[Invoice Unit of Measure],[Qty in Invoice Measure],[Validation Date],[Close Quote Code],[BorgItem],[Salesperson Code],[Line Discount Amount 1],[Line Discount % 2],[Line Discount Amount 2],[Quote No_],[Contract No_],[Ordered],[Minimum Quantity to Ship],[Purch_ Contract No_],[Purch_ Contract Line],[Shipment End Date],[Planned for Transport],[Supplement],[Quote Posted],[Item Recyclage],[No_OfCollies],[Prim_ Vendor No_],[Simulation],[Line Net Weight],[Line Deliver Weight],[Line Volume],[Line Deliver Volume],[Order No_],[Transport Line],[Detailled Resource]
,[Relocation Resource],[Detailled Activity],[Site from],[Detailled Remark],[Item Shpt_ Entry No_],[Unit Price Incl_ VAT],[Commission %],[Line Type],[BOM Attached Line No_],[Calc Formula],[Parent Line No_],[Original Line No_],[SkipGetPriceQuoteAndContract],[Description 3],[Back Order],[Quantity Back Order],[Qty (Base) Back Order],[Reason Code change Price_Disc],[Orig_ Line Discount %],[Orig_ Line Discount % 2],[Orig_ Line Discount Amount 1],[Orig_ Line Discount Amount 2],[UOM Rounding Precision Unit],[UOM Rounding Precision],[UOM Rounding Type],[Inv_ UOM Round_ Precision Unit],[Inv_ UOM Rounding Precision],[Inv_ UOM Rounding Type],[Base UOM Round_ Precision Unit],[Base UOM Rounding Precision],[Base UOM Rounding Type],[Cad import],[Picking Status],[New Location Code],[New Bin Code],[New Picking Status],[Concrete Supplement],[Custom Code 1],[Price Option],[Building Department],[IC Purch_ Doc No_],[IC Purch_ Line No_],[NoCheckChangePrice],[Lot No_],[Sample],[Print Picture],[Std_ Price Exists],[Line Disc Exists])
values
(s.[Document Type],s.[Sell-to Customer No_],s.[Document No_],s.[Line No_],s.[Type],s.[No_],s.[Location Code],s.[Posting Group],s.[Shipment Date],s.[Description],s.[Description 2],s.[Unit of Measure],s.[Quantity],s.[Outstanding Quantity],s.[Qty_ to Invoice],s.[Qty_ to Ship],s.[Unit Price],s.[Unit Cost (LCY)],s.[VAT %],s.[Line Discount %],s.[Line Discount Amount],s.[Amount],s.[Amount Including VAT],s.[Allow Invoice Disc_],s.[Gross Weight],s.[Net Weight],s.[Units per Parcel],s.[Unit Volume],s.[Appl_-to Item Entry],s.[Shortcut Dimension 1 Code],s.[Shortcut Dimension 2 Code],s.[Customer Price Group],s.[Job No_],s.[Work Type Code],s.[Outstanding Amount],s.[Qty_ Shipped Not Invoiced],s.[Shipped Not Invoiced],s.[Quantity Shipped],s.[Quantity Invoiced],s.[Shipment No_],s.[Shipment Line No_],s.[Profit %],s.[Bill-to Customer No_],s.[Inv_ Discount Amount],s.[Purchase Order No_],s.[Purch_ Order Line No_],s.[Drop Shipment],s.[Gen_ Bus_ Posting Group],s.[Gen_ Prod_ Posting Group],s.[VAT Calculation Type],s.[Transaction Type],s.[Transport Method],s.[Attached to Line No_],s.[Exit Point],s.[Area],s.[Transaction Specification]
,[Tax Area Code],s.[Tax Liable],s.[Tax Group Code],s.[VAT Bus_ Posting Group],s.[VAT Prod_ Posting Group],s.[Currency Code],s.[Outstanding Amount (LCY)],s.[Shipped Not Invoiced (LCY)],s.[Reserve],s.[Blanket Order No_],s.[Blanket Order Line No_],s.[VAT Base Amount],s.[Unit Cost],s.[System-Created Entry],s.[Line Amount],s.[VAT Difference],s.[Inv_ Disc_ Amount to Invoice],s.[VAT Identifier],s.[IC Partner Ref_ Type],s.[IC Partner Reference],s.[Prepayment %],s.[Prepmt_ Line Amount],s.[Prepmt_ Amt_ Inv_],s.[Prepmt_ Amt_ Incl_ VAT],s.[Prepayment Amount],s.[Prepmt_ VAT Base Amt_],s.[Prepayment VAT %],s.[Prepmt_ VAT Calc_ Type],s.[Prepayment VAT Identifier],s.[Prepayment Tax Area Code],s.[Prepayment Tax Liable],s.[Prepayment Tax Group Code],s.[Prepmt Amt to Deduct],s.[Prepmt Amt Deducted],s.[Prepayment Line],s.[Prepmt_ Amount Inv_ Incl_ VAT],s.[Prepmt_ Amount Inv_ (LCY)],s.[IC Partner Code],s.[Prepayment VAT Difference],s.[Prepmt VAT Diff_ to Deduct],s.[Prepmt VAT Diff_ Deducted],s.[Job Task No_],s.[Job Contract Entry No_],s.[Variant Code],s.[Bin Code],s.[Qty_ per Unit of Measure],s.[Planned]
,[Unit of Measure Code],s.[Quantity (Base)],s.[Outstanding Qty_ (Base)],s.[Qty_ to Invoice (Base)],s.[Qty_ to Ship (Base)],s.[Qty_ Shipped Not Invd_ (Base)],s.[Qty_ Shipped (Base)],s.[Qty_ Invoiced (Base)],s.[FA Posting Date],s.[Depreciation Book Code],s.[Depr_ until FA Posting Date],s.[Duplicate in Depreciation Book],s.[Use Duplication List],s.[Responsibility Center],s.[Out-of-Stock Substitution],s.[Originally Ordered No_],s.[Originally Ordered Var_ Code],s.[Cross-Reference No_],s.[Unit of Measure (Cross Ref_)],s.[Cross-Reference Type],s.[Cross-Reference Type No_],s.[Item Category Code],s.[Nonstock],s.[Purchasing Code],s.[Product Group Code],s.[Special Order],s.[Special Order Purchase No_],s.[Special Order Purch_ Line No_],s.[Completely Shipped],s.[Requested Delivery Date],s.[Promised Delivery Date],s.[Shipping Time],s.[Outbound Whse_ Handling Time],s.[Planned Delivery Date],s.[Planned Shipment Date],s.[Shipping Agent Code],s.[Shipping Agent Service Code],s.[Allow Item Charge Assignment],s.[Return Qty_ to Receive],s.[Return Qty_ to Receive (Base)],s.[Return Qty_ Rcd_ Not Invd_]
,[Ret_ Qty_ Rcd_ Not Invd_(Base)],s.[Return Rcd_ Not Invd_],s.[Return Rcd_ Not Invd_ (LCY)],s.[Return Qty_ Received],s.[Return Qty_ Received (Base)],s.[Appl_-from Item Entry],s.[BOM Item No_],s.[Return Receipt No_],s.[Return Receipt Line No_],s.[Return Reason Code],s.[Allow Line Disc_],s.[Customer Disc_ Group],s.[Pmt_ Discount Amount],s.[StandardUnitPrice],s.[NoPaymentDiscount],s.[Unit Price M2],s.[PrintOption],s.[Territory Code],s.[Unit Cost Changed],s.[Tariff No_],s.[Site],s.[Site Sublocation],s.[Invoice Unit of Measure],s.[Qty in Invoice Measure],s.[Validation Date],s.[Close Quote Code],s.[BorgItem],s.[Salesperson Code],s.[Line Discount Amount 1],s.[Line Discount % 2],s.[Line Discount Amount 2],s.[Quote No_],s.[Contract No_],s.[Ordered],s.[Minimum Quantity to Ship],s.[Purch_ Contract No_],s.[Purch_ Contract Line],s.[Shipment End Date],s.[Planned for Transport],s.[Supplement],s.[Quote Posted],s.[Item Recyclage],s.[No_OfCollies],s.[Prim_ Vendor No_],s.[Simulation],s.[Line Net Weight],s.[Line Deliver Weight],s.[Line Volume],s.[Line Deliver Volume],s.[Order No_],s.[Transport Line],s.[Detailled Resource]
,[Relocation Resource],s.[Detailled Activity],s.[Site from],s.[Detailled Remark],s.[Item Shpt_ Entry No_],s.[Unit Price Incl_ VAT],s.[Commission %],s.[Line Type],s.[BOM Attached Line No_],s.[Calc Formula],s.[Parent Line No_],s.[Original Line No_],s.[SkipGetPriceQuoteAndContract],s.[Description 3],s.[Back Order],s.[Quantity Back Order],s.[Qty (Base) Back Order],s.[Reason Code change Price_Disc],s.[Orig_ Line Discount %],s.[Orig_ Line Discount % 2],s.[Orig_ Line Discount Amount 1],s.[Orig_ Line Discount Amount 2],s.[UOM Rounding Precision Unit],s.[UOM Rounding Precision],s.[UOM Rounding Type],s.[Inv_ UOM Round_ Precision Unit],s.[Inv_ UOM Rounding Precision],s.[Inv_ UOM Rounding Type],s.[Base UOM Round_ Precision Unit],s.[Base UOM Rounding Precision],s.[Base UOM Rounding Type],s.[Cad import],s.[Picking Status],s.[New Location Code],s.[New Bin Code],s.[New Picking Status],s.[Concrete Supplement],s.[Custom Code 1],s.[Price Option],s.[Building Department],s.[IC Purch_ Doc No_],s.[IC Purch_ Line No_],s.[NoCheckChangePrice],s.[Lot No_],s.[Sample],s.[Print Picture],s.[Std_ Price Exists],s.[Line Disc Exists]);
So the question is with such a small diffrence in runtime and performance which version is prefarable and why

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

It was not easy to find the difference in the code "noodles" you have, I could not even copy it from browser to my SSMS as it's too wide at the end...

But finally, I've managed to spot the difference. Your first one uses table variable with CLUSTERED index and another one has not defined it.

As second example uses heap table SQL will need to do row id lookups (RID's) to get data from it, so it's probably spools the table for this reason.

As advice, I first I would suggest to use temp table instead of table variable and do not create it separately - use SELECT INTO. You can see if creating clustered index on it will make any difference and if using #table instead of table variable will give you any performance difference.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Resender SSCertifiable Points: 7443 More actions · Answer 2

The problem with creating a temp table gives the following possible errors

--There is already an object named '#temp' in the database.

~~Second I need business logic in the like '' for fields, a select into will give this error for this kind of values~~

--An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Ok so I replaced declare @tSalesLine table with create table #tSalesLine

The runtime went down, performance not that much.