Help with an insert script

• dandrews 45259

  SSC Enthusiast

  Points: 170

  More actions

  January 20, 2016 at 4:38 am

  #310967

  Hello,

  I need help performing a large insert script if possible.

  Two tables are involved, 'fecon2' and 'stockm'. They both contain product information.

  First I need to identify the records in 'stockm' that have both a record for warehouse BE and a record for warehouse BF, with the same product code.

  Once identified, I need to use the BF row from 'stockm' and replicate that record in the fecon2 table but with a warehouse value of BE. There will already be a BF record in fecon2.

  Im suspecting I'll need a case statement to identify the first bit, the a big insert script with all the table values but I'm struggling, I've done this for one record, but in this case there is over 3000 that this will apply to. Any help or advice would be hugely appreciated.

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  January 20, 2016 at 5:37 am

  #1853101

  No CASE expression needed.

  To find the BF rows that also have a BE, use a SELECT with BF in the WHERE clause, and an EXISTS subquery to check of there's a row with the same value in all relevant rows but warehouse code BE.

  To insert that in another table, usue that SELECT statement in an INSERT ... SELECT. To change the warehouscode from BF to BE, simply use the constant 'BE' at the appropriate place in the SELECT list.

  Next time you ask a question, please provide CREATE TABLE statements for tables involved, INSERT statements with a few well-chosen rows of sample data, and expected results. That will enable people to give you much better help.

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• dandrews 45259

  SSC Enthusiast

  Points: 170

  More actions

  January 20, 2016 at 5:50 am

  #1853104

  Thanks for reply, very useful bit Id be a bit unsure of how to put all that together.

  CREATE TABLE script for fecon2:

  CREATE TABLE [scheme].[fecon2](

  [WAREHOUSE] [varchar](2) NOT NULL,

  [SAGE_PRODUCT_CODE] [varchar](20) NOT NULL,

  [PRODUCT_GROUP] [varchar](30) NULL,

  [AQUILA_PRODUCT_CODE] [varchar](20) NULL,

  [DESCRIPTION] [varchar](40) NULL,

  [ANALYSIS_FIELD_A] [varchar](10) NULL,

  [ANALYSIS_FIELD_B] [varchar](10) NULL,

  [ANALYSIS_FIELD_C] [varchar](10) NULL,

  [ANALYSIS_FIELD_D] [varchar](10) NULL,

  [ALPHA_CODE] [varchar](10) NULL,

  [GENERIC_BLEND_CODE] [varchar](30) NULL,

  [END_USER_APPLICATION] [varchar](30) NULL,

  [WIDTH_MM] [int] NULL,

  [LENGTH_MTRS] [int] NULL,

  [MICRON_MM] [float] NULL,

  [COLOUR] [varchar](20) NULL,

  [OPACITY] [varchar](6) NULL,

  [UNIT] [varchar](6) NULL,

  [CORE] [varchar](30) NULL,

  [NETT_OR_GROSS_CORE] [varchar](10) NULL,

  [BLEND] [varchar](30) NULL,

  [ROLL_WEIGHT] [float] NULL,

  [UVI] [varchar](4) NULL,

  [PACKAGING_TYPE] [varchar](30) NULL,

  [ROLLS_PER_BOX] [int] NULL,

  [ROLLS_PER_PALLET] [int] NULL,

  [TPL_BOX_TYPE] [varchar](20) NULL,

  [BOX_DIMENSIONS] [varchar](20) NULL,

  [FOLDED_OR_UNFOLDED] [varchar](20) NULL,

  [LABEL_CODE] [varchar](20) NULL,

  [LABEL_DETAIL] [varchar](40) NULL,

  [LABEL_PALLET] [varchar](100) NULL,

  [EXTR_FACE_WIDTH_MM] [int] NULL,

  [EXTR_OPEN_WIDTH_MM] [int] NULL,

  [EXTR_REEL_LENGTH] [int] NULL,

  [EXTR_MICRON_MM] [float] NULL,

  [EXTR_REEL_OD] [int] NULL,

  [EXTR_UNIT] [varchar](6) NULL,

  [FILM_SPEC] [varchar](30) NULL,

  [EXTR_FOOD_GRADE] [varchar](3) NULL,

  [EXTR_COLOUR] [varchar](40) NULL,

  [EXTR_GLOSS_OR_MATT] [varchar](6) NULL,

  [EXTR_OPACITY] [varchar](6) NULL,

  [EXTR_BLEND] [varchar](20) NULL,

  [EXTR_CORE] [varchar](30) NULL,

  [EXTR_NETT_OR_GROSS_CORE] [varchar](10) NULL,

  [EXTR_MTRS_PER_REEL] [float] NULL,

  [EXTR_IMPS_PER_REEL] [float] NULL,

  [KGS_PER_REEL] [float] NULL,

  [EXTR_KGS_PER_1000_IMP] [float] NULL,

  [SHRINK_FILM] [varchar](3) NULL,

  [TREATMENT] [varchar](10) NULL,

  [SLIP] [varchar](15) NULL,

  [SAR_INFO] [varchar](10) NULL,

  [MASTERBATCH_1] [varchar](20) NULL,

  [MASTERBATCH_2] [varchar](20) NULL,

  [MASTERBATCH_3] [varchar](20) NULL,

  [ADDITIVES_1] [varchar](20) NULL,

  [ADDITIVES_2] [varchar](20) NULL,

  [ADDITIVES_3] [varchar](20) NULL,

  [EXTR_LIP_WIDTH_MM] [int] NULL,

  [EXTR_BLUE_STAR] [varchar](3) NULL,

  [MONO_OR_COEX] [varchar](4) NULL,

  [EXTR_PERF_YES_NO] [varchar](3) NULL,

  [EXTR_PERF_SIZE_TYPE] [varchar](40) NULL,

  [EXTR_PERF_FREE_TEXT] [varchar](40) NULL,

  [PRINT_INLINE_MANZONI] [varchar](20) NULL,

  [EXTR_TRIAL_JOB] [varchar](20) NULL,

  [EXTR_LABEL_CODE] [varchar](20) NULL,

  [EXTR_LABEL_DETAIL] [varchar](20) NULL,

  [EXTR_LABEL_PALLET] [varchar](20) NULL,

  [CONV_TYPE] [varchar](30) NULL,

  [CONV_FOOD_GRADE] [varchar](3) NULL,

  [CONV_FACE_WIDTH_MM] [int] NULL,

  [CONV_OPEN_WIDTH_MM] [int] NULL,

  [CONV_DRAW_LENGTH_MM] [int] NULL,

  [CONV_MICRON_MM] [float] NULL,

  [CONV_CORE] [varchar](30) NULL,

  [CONV_REEL_OD] [int] NULL,

  [CONV_NETT_OR_GROSS_CORE] [varchar](10) NULL,

  [CONV_UNIT] [varchar](6) NULL,

  [CONV_BLUE_STAR] [varchar](3) NULL,

  [LENGTH_OUT_MTRS] [int] NULL,

  [CONV_LIP_WIDTH_MM] [int] NULL,

  [SLITS] [varchar](30) NULL,

  [CONV_PERF_YES_NO] [varchar](3) NULL,

  [CONV_PERF_SIZE_TYPE] [varchar](40) NULL,

  [CONV_PERF_FREE_TEXT] [varchar](40) NULL,

  [POR_QTY_PER_REEL] [int] NULL,

  [KGS_PER_1000_BAGS] [float] NULL,

  [KGS_PER_1000_IMP] [float] NULL,

  [KGS_PER_1000_MTRS] [float] NULL,

  [KGS_REEL] [float] NULL,

  [CONV_MTRS_PER_REEL] [float] NULL,

  [CONV_IMPS_PER_REEL] [float] NULL,

  [KGS_PER_BOX] [float] NULL,

  [QTY_PER_BOX] [float] NULL,

  [PACKING_QUANTITY] [int] NULL,

  [BAG_TYPE] [varchar](20) NULL,

  [CONV_PACKAGING_TYPE] [varchar](20) NULL,

  [SHEETS_PER_BOX] [int] NULL,

  [KILOS_PER_BOX_ROLL] [float] NULL,

  [BAGS_PER_PALLET] [float] NULL,

  [BOXES_PER_PALLET] [float] NULL,

  [CONV_LABEL_CODE] [varchar](20) NULL,

  [CONV_LABEL_DETAIL] [varchar](20) NULL,

  [CONV_LABEL_PALLET] [varchar](20) NULL,

  [PRINT_DESIGN] [varchar](64) NULL,

  [FILE_NUMBER] [varchar](5) NULL,

  [PRINT_FOOD_GRADE] [varchar](3) NULL,

  [FACE_WIDTH] [int] NULL,

  [THICKNESS_MM] [float] NULL,

  [COLOUR_AND_SIDES] [char](15) NULL,

  [COLOUR_1] [varchar](20) NULL,

  [COLOUR_2] [varchar](20) NULL,

  [COLOUR_3] [varchar](20) NULL,

  [COLOUR_4] [varchar](20) NULL,

  [COLOUR_5] [varchar](20) NULL,

  [COLOUR_6] [varchar](20) NULL,

  [COLOUR_7] [varchar](20) NULL,

  [COLOUR_8] [varchar](20) NULL,

  [COLOUR_9] [varchar](20) NULL,

  [COLOUR_10] [varchar](20) NULL,

  [PRINT_BLUE_STAR] [varchar](3) NULL,

  [REPEAT_TYPE] [varchar](12) NULL,

  [REPEAT_DIMENSION_MM] [varchar](12) NULL,

  [CYLINDER_SIZE] [int] NULL,

  [UNWIND_CODE] [varchar](50) NULL,

  [NUMBER_ROUND] [int] NULL,

  [NUMBER_ACROSS] [int] NULL,

  [EYEMARK] [varchar](20) NULL,

  [EYEMARK_DETAIL] [varchar](30) NULL,

  [NARRATIVE_NOTE_1] [varchar](100) NULL,

  [NARRATIVE_NOTE_2] [varchar](100) NULL,

  [NARRATIVE_NOTE_3] [varchar](100) NULL,

  [NARRATIVE_NOTE_4] [varchar](100) NULL,

  [NARRATIVE_NOTE_5] [varchar](100) NULL,

  [NARRATIVE_NOTE_6] [varchar](100) NULL,

  [ROWSTAMP] [timestamp] NULL,

  CONSTRAINT [PK_fecon2] PRIMARY KEY CLUSTERED

  (

  [WAREHOUSE] ASC,

  [SAGE_PRODUCT_CODE] 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

  CREATE TABLE script for stockm:

  CREATE TABLE [scheme].[stockm](

  [warehouse] [char](2) NOT NULL,

  [product] [char](20) NOT NULL,

  [alpha] [char](10) NOT NULL,

  [description] [char](20) NOT NULL,

  [supersession] [char](22) NOT NULL,

  [supsession_date] [datetime] NULL,

  [alternatives01] [char](22) NOT NULL,

  [alternatives02] [char](22) NOT NULL,

  [alternatives03] [char](22) NOT NULL,

  [alternatives04] [char](22) NOT NULL,

  [alternatives05] [char](22) NOT NULL,

  [alternatives06] [char](22) NOT NULL,

  [alternatives07] [char](22) NOT NULL,

  [alternatives08] [char](22) NOT NULL,

  [alternatives09] [char](22) NOT NULL,

  [alternatives10] [char](22) NOT NULL,

  [unit_code] [char](10) NOT NULL,

  [bin_number] [char](10) NOT NULL,

  [issue_date] [datetime] NULL,

  [stock_date] [datetime] NULL,

  [delivery_date] [datetime] NULL,

  [long_description] [char](40) NOT NULL,

  [old_vat_code] [char](1) NOT NULL,

  [abc_category] [char](1) NOT NULL,

  [discount] [char](8) NOT NULL,

  [nominal_key] [char](3) NOT NULL,

  [purchase_key] [char](3) NOT NULL,

  [serial_numbers] [char](1) NOT NULL,

  [analysis_a] [char](10) NOT NULL,

  [analysis_b] [char](10) NOT NULL,

  [analysis_c] [char](10) NOT NULL,

  [queue_sequence] [char](6) NOT NULL,

  [qty_decimal_places] [char](1) NOT NULL,

  [mrp_batching] [char](1) NOT NULL,

  [working_level] [char](1) NOT NULL,

  [make_or_buy] [char](1) NOT NULL,

  [purchase_unit] [char](10) NOT NULL,

  [selling_unit] [char](10) NOT NULL,

  [bulk_item] [char](1) NOT NULL,

  [drawing_number] [char](20) NOT NULL,

  [catalogue_number] [char](20) NOT NULL,

  [unit_length] [char](4) NOT NULL,

  [unit_width] [char](4) NOT NULL,

  [unit_height] [char](4) NOT NULL,

  [supplier] [char](8) NOT NULL,

  [batch_traceability] [char](1) NOT NULL,

  [last_supplier] [char](8) NOT NULL,

  [std_cost_freeze] [char](1) NOT NULL,

  [reorder_freeze] [char](1) NOT NULL,

  [physical_qty] [float] NOT NULL,

  [allocated_qty] [float] NOT NULL,

  [back_order_qty] [float] NOT NULL,

  [forward_order_qty] [float] NOT NULL,

  [on_order_qty] [float] NOT NULL,

  [price] [float] NOT NULL,

  [weight] [float] NOT NULL,

  [standard_cost] [float] NOT NULL,

  [current_cost] [float] NOT NULL,

  [expected_cost] [float] NOT NULL,

  [economic_reorder_q] [float] NOT NULL,

  [lead_time] [float] NOT NULL,

  [min_stock_level] [float] NOT NULL,

  [average_sales_valu] [float] NOT NULL,

  [month_to_date01] [float] NOT NULL,

  [month_to_date02] [float] NOT NULL,

  [month_to_date03] [float] NOT NULL,

  [month_to_date04] [float] NOT NULL,

  [month_to_date05] [float] NOT NULL,

  [month_to_date06] [float] NOT NULL,

  [month_to_date07] [float] NOT NULL,

  [month_to_date08] [float] NOT NULL,

  [month_to_date09] [float] NOT NULL,

  [month_to_date10] [float] NOT NULL,

  [year_to_date01] [float] NOT NULL,

  [year_to_date02] [float] NOT NULL,

  [year_to_date03] [float] NOT NULL,

  [year_to_date04] [float] NOT NULL,

  [year_to_date05] [float] NOT NULL,

  [year_to_date06] [float] NOT NULL,

  [year_to_date07] [float] NOT NULL,

  [year_to_date08] [float] NOT NULL,

  [year_to_date09] [float] NOT NULL,

  [year_to_date10] [float] NOT NULL,

  [previous_sale_val1] [float] NOT NULL,

  [previous_sale_val2] [float] NOT NULL,

  [previous_sale_val3] [float] NOT NULL,

  [period_issue_qty01] [float] NOT NULL,

  [period_issue_qty02] [float] NOT NULL,

  [period_issue_qty03] [float] NOT NULL,

  [period_issue_qty04] [float] NOT NULL,

  [period_issue_qty05] [float] NOT NULL,

  [period_issue_qty06] [float] NOT NULL,

  [period_issue_qty07] [float] NOT NULL,

  [period_issue_qty08] [float] NOT NULL,

  [period_issue_qty09] [float] NOT NULL,

  [period_issue_qty10] [float] NOT NULL,

  [period_issue_qty11] [float] NOT NULL,

  [period_issue_qty12] [float] NOT NULL,

  [period_issue_qty13] [float] NOT NULL,

  [period_issue_val01] [float] NOT NULL,

  [period_issue_val02] [float] NOT NULL,

  [period_issue_val03] [float] NOT NULL,

  [period_issue_val04] [float] NOT NULL,

  [period_issue_val05] [float] NOT NULL,

  [period_issue_val06] [float] NOT NULL,

  [period_issue_val07] [float] NOT NULL,

  [period_issue_val08] [float] NOT NULL,

  [period_issue_val09] [float] NOT NULL,

  [period_issue_val10] [float] NOT NULL,

  [period_issue_val11] [float] NOT NULL,

  [period_issue_val12] [float] NOT NULL,

  [period_issue_val13] [float] NOT NULL,

  [standard_labour] [float] NOT NULL,

  [standard_overhead] [float] NOT NULL,

  [standard_material] [float] NOT NULL,

  [standard_subcontra] [float] NOT NULL,

  [mrp_reorder_roundi] [float] NOT NULL,

  [purchase_factor] [float] NOT NULL,

  [selling_factor] [float] NOT NULL,

  [safety_stock_level] [float] NOT NULL,

  [material_on_cost] [float] NOT NULL,

  [labour_on_cost] [float] NOT NULL,

  [overhead_on_cost] [float] NOT NULL,

  [maximum_stock_leve] [float] NOT NULL,

  [shelf_life] [float] NOT NULL,

  [stock_length] [float] NOT NULL,

  [stock_width] [float] NOT NULL,

  [stock_height] [float] NOT NULL,

  [current_material] [float] NOT NULL,

  [current_labour] [float] NOT NULL,

  [current_overhead] [float] NOT NULL,

  [current_subcontrac] [float] NOT NULL,

  [uninspected_qty] [float] NOT NULL,

  [period_demand01] [float] NOT NULL,

  [period_demand02] [float] NOT NULL,

  [period_demand03] [float] NOT NULL,

  [period_demand04] [float] NOT NULL,

  [period_demand05] [float] NOT NULL,

  [period_demand06] [float] NOT NULL,

  [period_demand07] [float] NOT NULL,

  [period_demand08] [float] NOT NULL,

  [period_demand09] [float] NOT NULL,

  [period_demand10] [float] NOT NULL,

  [period_demand11] [float] NOT NULL,

  [period_demand12] [float] NOT NULL,

  [period_demand13] [float] NOT NULL,

  [safety_days] [float] NOT NULL,

  [review_days] [float] NOT NULL,

  [reorder_days] [float] NOT NULL,

  [pr_yield] [float] NOT NULL,

  [setup_cost] [float] NOT NULL,

  [dspare01] [float] NOT NULL,

  [dspare02] [float] NOT NULL,

  [dspare03] [float] NOT NULL,

  [dspare04] [float] NOT NULL,

  [dspare05] [float] NOT NULL,

  [dspare06] [float] NOT NULL,

  [dspare07] [float] NOT NULL,

  [dspare08] [float] NOT NULL,

  [dspare09] [float] NOT NULL,

  [dspare10] [float] NOT NULL,

  [inspection_yield] [float] NOT NULL,

  [holding_cost] [float] NOT NULL,

  [inspect_cycle] [int] NOT NULL,

  [batch_receipts] [int] NOT NULL,

  [lowest_where_used] [int] NOT NULL,

  [last_revalue] [datetime] NULL,

  [last_where_used_ru] [int] NOT NULL,

  [sub_oncost_] [float] NOT NULL,

  [spare_integer] [int] NOT NULL,

  [stock_take_toleran] [float] NOT NULL,

  [weight_low] [float] NOT NULL,

  [mps_type] [char](1) NOT NULL,

  [spare] [char](6) NOT NULL,

  [wholesale_price] [float] NOT NULL,

  [worder_allocations] [float] NOT NULL,

  [spare2] [char](48) NOT NULL,

  [analysis_x_ref] [char](3) NOT NULL,

  [vat_inclusive] [char](1) NOT NULL,

  [stock_vat_type] [char](10) NOT NULL,

  [buyer_id] [char](8) NOT NULL,

  [level_of_detail] [char](1) NOT NULL,

  [pricing_discount] [char](8) NOT NULL,

  [despatch_units] [char](10) NOT NULL,

  [despatch_unit_use] [char](1) NOT NULL,

  [stock_type] [char](1) NOT NULL,

  [packaging] [char](10) NOT NULL,

  [shipping_category] [char](10) NOT NULL,

  [replenish_category] [char](3) NOT NULL,

  [held_flag] [char](1) NOT NULL,

  [obsolete_flag] [char](1) NOT NULL,

  [rowstamp] [timestamp] NOT NULL

  ) ON [PRIMARY]

  GO

• dandrews 45259

  SSC Enthusiast

  Points: 170

  More actions

  January 20, 2016 at 8:17 am

  #1853162

  Thanks could you clarify how to do that please, I added the create table scripts but think they were removed, guess they were too long??

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  January 20, 2016 at 9:15 am

  #1853191

  Probably something like this:

  INSERT INTO dbo.fecon2

  (ProductCode, Warehouse, Other, Columns)

  SELECT s.ProductCode, 'BE', s.Other, s.Columns

  FROM dbo.stockm AS s

  WHERE s.Warehouse = 'BF'

  AND EXISTS

  (SELECT *

  FROM dbo.stockm AS s2

  WHERE s2.ProductCode = s.ProductCode

  AND s2.Warehoude = 'BE');

  (untested)

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• dandrews 45259

  SSC Enthusiast

  Points: 170

  More actions

  January 20, 2016 at 9:45 am

  #1853202

  Hello,

  I've just realised I explained this wrong, I need reference stockm as explained to find the products that have a BE and BF record. When I have that I need to then then use the product code record that exists in fecon2 that has a matching product code from stockm and just replicate that as it is but with the warehouse as BE instead of BF

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  January 20, 2016 at 10:00 am

  #1853208

  This looks like a fine point to refer back to what I wrote before:

  "Next time you ask a question, please provide CREATE TABLE statements for tables involved, INSERT statements with a few well-chosen rows of sample data, and expected results. That will enable people to give you much better help."

  If the script is very long, then remove columns not relevant to the query, and trim down the test data to just a handful of rows that nicely demonstrate the issue.

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• dandrews 45259

  SSC Enthusiast

  Points: 170

  More actions

  January 20, 2016 at 10:11 am

  #1853213

  This is the script I need to run:

  INSERT INTO pres.scheme.fecon2

  ([WAREHOUSE],

  [SAGE_PRODUCT_CODE] ,[PRODUCT_GROUP] ,[AQUILA_PRODUCT_CODE] ,[DESCRIPTION] ,[ANALYSIS_FIELD_A] ,[ANALYSIS_FIELD_B]

  ,[ANALYSIS_FIELD_C]

  ,[ANALYSIS_FIELD_D]

  ,[ALPHA_CODE]

  ,[GENERIC_BLEND_CODE]

  ,[END_USER_APPLICATION]

  ,[WIDTH_MM]

  ,[LENGTH_MTRS]

  ,[MICRON_MM]

  ,[COLOUR]

  ,[OPACITY]

  ,[UNIT]

  ,[CORE]

  ,[NETT_OR_GROSS_CORE]

  SELECT

  'BE',

  [SAGE_PRODUCT_CODE]

  ,[PRODUCT_GROUP]

  ,[AQUILA_PRODUCT_CODE]

  ,[DESCRIPTION]

  ,[ANALYSIS_FIELD_A]

  ,[ANALYSIS_FIELD_B]

  ,[ANALYSIS_FIELD_C]

  ,[ANALYSIS_FIELD_D]

  ,[ALPHA_CODE]

  ,[GENERIC_BLEND_CODE]

  ,[END_USER_APPLICATION]

  ,[WIDTH_MM]

  ,[LENGTH_MTRS]

  ,[MICRON_MM]

  ,[COLOUR]

  ,[OPACITY]

  ,[UNIT]

  ,[CORE]

  ,[NETT_OR_GROSS_CORE]

  FROM pres.scheme.fecon2

  Ive removed half the entries above from both the SELECT and INSERT INTO

  Where needs to be where the sage_prdocut code matches the product code in stockm that has a record for warehouse BF and BE in stockm

  Thank you

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 20, 2016 at 11:35 am

  #1853248

  possibly may get you started?

  use TEMPDB;

  SET NOCOUNT ON

  CREATE TABLE #stockm(

  productcode INTEGER NOT NULL

  ,QOH INTEGER NOT NULL

  ,Warehouse VARCHAR(2) NOT NULL

  ,PRIMARY KEY(productcode,Warehouse)

  );

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (1000,10,'BF');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (1000,50,'BE');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (2000,40,'BF');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (2000,30,'BZ');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (3000,60,'BG');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (3000,80,'BD');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (4000,20,'BF');

  INSERT INTO #stockm(productcode,QOH,Warehouse) VALUES (4000,70,'BE');

  CREATE TABLE #fecon2(

  productcode INTEGER NOT NULL

  ,QOH INTEGER NOT NULL

  ,Warehouse VARCHAR(2) NOT NULL

  ,PRIMARY KEY(productcode,Warehouse)

  );

  INSERT INTO #fecon2(productcode,QOH,Warehouse) VALUES (1000,10,'BF');

  INSERT INTO #fecon2(productcode,QOH,Warehouse) VALUES (2000,40,'BF');

  INSERT INTO #fecon2(productcode,QOH,Warehouse) VALUES (2000,30,'BZ');

  INSERT INTO #fecon2(productcode,QOH,Warehouse) VALUES (3000,60,'BG');

  INSERT INTO #fecon2(productcode,QOH,Warehouse) VALUES (4000,20,'BF');

  --SELECT * FROM #stockm;

  SELECT * FROM #fecon2;

  WITH cte

  AS (SELECT productcode AS pcode

  FROM #stockm

  WHERE(Warehouse = 'BE')

  OR (Warehouse = 'BF')

  GROUP BY productcode

  HAVING(COUNT(Warehouse) = 2))

  INSERT INTO #fecon2

  (productcode,QOH,Warehouse)

  SELECT #stockm.productcode,

  #stockm.QOH,

  #stockm.Warehouse

  FROM #stockm

  INNER JOIN cte ON #stockm.productcode = cte.pcode

  WHERE(#stockm.Warehouse = 'BE');

  --SELECT * FROM #stockm;

  SELECT * FROM #fecon2;

  DROP TABLE #stockm;

  DROP TABLE #fecon2;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  January 20, 2016 at 11:55 am

  #1853252

  @dandrews: For future reference, what J Livingstone posted is a good repro script - CREATE TABLE and INSERT statements that people can simply copy/paste into SSMS and run to replicate your issue. In the future, please go the extra mile and post something like that yourself when asking questions, you will get more and quicker help.

  @j-2 Livingstone: Thanks for that effort.

  @dandrews: If J Livingstone's code does not do what you need, let us know. Give specifics - add extra INSERT statements if needed to illustrate, tell us what output you get, and what output should be generated.

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 20, 2016 at 5:38 pm

  #1853342

  dandrews 45259 (1/20/2016)

  ---

  This is the script I need to run:

  ...

  Ive removed half the entries above from both the SELECT and INSERT INTO

  Where needs to be where the sage_prdocut code matches the product code in stockm that has a record for warehouse BF and BE in stockm

  Thank you

  Try this, but if you get errors about collation incompatibility, don't forget that Sage requires a binary collation, so you may need to add COLLATE Latin1_General_BIN to the WHERE clause, directly after [SAGE_PRODUCT_CODE] but before IN.

  INSERT INTO pres.scheme.fecon2

  ([WAREHOUSE],

  [SAGE_PRODUCT_CODE] ,[PRODUCT_GROUP] ,[AQUILA_PRODUCT_CODE] ,[DESCRIPTION] ,[ANALYSIS_FIELD_A] ,[ANALYSIS_FIELD_B]

  ,[ANALYSIS_FIELD_C]

  ,[ANALYSIS_FIELD_D]

  ,[ALPHA_CODE]

  ,[GENERIC_BLEND_CODE]

  ,[END_USER_APPLICATION]

  ,[WIDTH_MM]

  ,[LENGTH_MTRS]

  ,[MICRON_MM]

  ,[COLOUR]

  ,[OPACITY]

  ,[UNIT]

  ,[CORE]

  ,[NETT_OR_GROSS_CORE]

  SELECT

  'BE',

  [SAGE_PRODUCT_CODE]

  ,[PRODUCT_GROUP]

  ,[AQUILA_PRODUCT_CODE]

  ,[DESCRIPTION]

  ,[ANALYSIS_FIELD_A]

  ,[ANALYSIS_FIELD_B]

  ,[ANALYSIS_FIELD_C]

  ,[ANALYSIS_FIELD_D]

  ,[ALPHA_CODE]

  ,[GENERIC_BLEND_CODE]

  ,[END_USER_APPLICATION]

  ,[WIDTH_MM]

  ,[LENGTH_MTRS]

  ,[MICRON_MM]

  ,[COLOUR]

  ,[OPACITY]

  ,[UNIT]

  ,[CORE]

  ,[NETT_OR_GROSS_CORE]

  FROM pres.scheme.fecon2 FE

  WHERE FE.[SAGE_PRODUCT_CODE] in (

  SELECT product

  FROM scheme.stockm

  WHERE warehouse in ('BE','BF')

  GROUP BY product

  HAVING count(*)=2

  )

  AND FE.[WAREHOUSE]='BF'

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]