none
copy a sql line and insert with a new primary key

    Question

  • Not sure what i am missing try to copy a line from inventory table and insert it into a temp table then reinsert it back into inventory table with a new primary auto calc key  

    CREATE table temporary_table AS SELECT * FROM inventory WHERE [reff number]= "20000";

    UPDATE temporary_table SET [reff number]=NULL;

    INSERT INTO inventory SELECT * FROM temporary_table;

    DROP TABLE temporary_table

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'AS'.

    Tuesday, July 09, 2013 12:16 PM

Answers

  • This looks like Oracle's syntax

    In SQL Server we can do

     SELECT * INTO  table temporary_table  FROM inventory WHERE [reff number]= "20000";


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by John Mendes Tuesday, July 09, 2013 12:33 PM
    Tuesday, July 09, 2013 12:21 PM
  • Select * into Temporary_table from inventory WHERE [reff number]= "20000";

    Srinivasan

    • Marked as answer by John Mendes Tuesday, July 09, 2013 12:33 PM
    Tuesday, July 09, 2013 12:21 PM
  • Start over.  You have learned some bad practices.  There is no reason to use a temp table - and programmer laziness is no excuse for not specifying both the columns you are interested in and the schema that the table is located in.  Double quotes should not be used as a string delimiter.  If the column [reff number] is defined as some type of numeric datatype (and presumably it is since you said autonumber), then you should not be using a string literal since it involves an implicit conversion.

    In short, the most effective way to do this is by simplying selecting the row during the insert.  A direct translation of your existing logic is: 

    insert inventory (col1, [reff number], ...)
    select col1, null, ... from inventory where [reff number] = 20000;

    Unfortunately, that probably will not work since you have an identity column (likely the primary key as well - a discussion for another time).  To do this, you need to specify all the columns except the identity column (no choice) in both the insert list and the select list.  Assuming [reff number] is the identity column:

    insert dbo.inventory (col1, col2, ...)  -- do not include [reff number]
    select col1, col2 ... from dbo.inventory where [reff number] = 20000;

    • Marked as answer by John Mendes Tuesday, July 09, 2013 1:24 PM
    Tuesday, July 09, 2013 12:35 PM

All replies

  • Select * into Temporary_table from inventory WHERE [reff number]= "20000";

    Srinivasan

    • Marked as answer by John Mendes Tuesday, July 09, 2013 12:33 PM
    Tuesday, July 09, 2013 12:21 PM
  • This looks like Oracle's syntax

    In SQL Server we can do

     SELECT * INTO  table temporary_table  FROM inventory WHERE [reff number]= "20000";


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by John Mendes Tuesday, July 09, 2013 12:33 PM
    Tuesday, July 09, 2013 12:21 PM
  • Thanks guys
    Tuesday, July 09, 2013 12:33 PM
  • Start over.  You have learned some bad practices.  There is no reason to use a temp table - and programmer laziness is no excuse for not specifying both the columns you are interested in and the schema that the table is located in.  Double quotes should not be used as a string delimiter.  If the column [reff number] is defined as some type of numeric datatype (and presumably it is since you said autonumber), then you should not be using a string literal since it involves an implicit conversion.

    In short, the most effective way to do this is by simplying selecting the row during the insert.  A direct translation of your existing logic is: 

    insert inventory (col1, [reff number], ...)
    select col1, null, ... from inventory where [reff number] = 20000;

    Unfortunately, that probably will not work since you have an identity column (likely the primary key as well - a discussion for another time).  To do this, you need to specify all the columns except the identity column (no choice) in both the insert list and the select list.  Assuming [reff number] is the identity column:

    insert dbo.inventory (col1, col2, ...)  -- do not include [reff number]
    select col1, col2 ... from dbo.inventory where [reff number] = 20000;

    • Marked as answer by John Mendes Tuesday, July 09, 2013 1:24 PM
    Tuesday, July 09, 2013 12:35 PM
  • Its now coping the time into the temp table but giving the errors bellow when i try to paste it back into the org table giving it a new primary key (reff number)

    SELECT * INTO   temporary_table  FROM inventory WHERE [reff number]= 20000;


    UPDATE temporary_table SET [reff number]=NULL 

    INSERT INTO inventory SELECT * FROM temporary_table;

    DROP TABLE temporary_table

    Msg 8102, Level 16, State 1, Line 4
    Cannot update identity column 'REFF NUMBER'.
    Msg 8101, Level 16, State 1, Line 6
    An explicit value for the identity column in table 'inventory' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Tuesday, July 09, 2013 12:42 PM
  • so what your saying is to select all the col names and doing it that way
    Tuesday, July 09, 2013 12:45 PM
  • thanks so much that works well 

    insert dbo.inventory (
    [PURCHASE ORDER NO]
          ,[REFNO]
          ,[COFFEE_TYP]
          ,[CAFETYPE]
          ,[NO_BGS_AVL]
          ,[MARKS]
          ,[MARKSpredecaf]
          ,[MKSblend]
          ,[NO_BGS_IN]
          ,[RPM_CRGO_N]
          ,[DATE_IN]
          ,[NET_WT]
          ,[COST]
          ,[FINAL WHSE-IN COST]
          ,[FinalWhseInCostMendes]
          ,[FinalWhseInArchive]
          ,[FINAL WHSE-IN DIFF]
          ,[FIXATION BASIS]
          ,[ASK]
          ,[POSITION]
          ,[FINAL POSITION]
          ,[V_1_O2_D_3]
          ,[NOTES]
          ,[PRINTLABEL]
          ,[consign(y/n)]
          ,[SELLER]
          ,[SELLER AGENT]
          ,[INTERMEDIARY]
          ,[PAYMENT TERMS]
          ,[PURCHASE REMARKS]
          ,[TENDERED]
          ,[TENDER DATE]
          ,[SALE DATE]
          ,[SELLER NUMBER]
          ,[WEIGHTS]
          ,[DATE PURCHASE PAID]
          ,[PURCHASE DUE DATE]
          ,[PRICE TO BE FIXED?]
          ,[DATE FIXED]
          ,[FUTURES BASIS]
          ,[DIFFRTL PRICE]
          ,[Differentl number]
          ,[PriceDescriptor]
          ,[BBH START UP]
          ,[ADJUSTED NET INSTOCK]
          ,[FORWARD PURCH]
          ,[PAID]
          ,[D/O ALLOCATION]
          ,[WAREHSE RCPT #]
          ,[B/L DATE]
          ,[NOLABELS]
          ,[EXWHS/FOB/ETC]
          ,[H-INDEX]
          ,[PROFORMA PAYMENT]
          ,[PROFORMA DATE]
          ,[TITLE DATE]
          ,[SAS]
          ,[RCD SHIP ADVICE]
          ,[SHIP COMMENTS]
          ,[MIXED CNTR?]
          ,[No SLACKS]
          ,[READY 2 SHIP MIX CNTR]
          ,[ROYAL SF REF NO]
          ,[REJECTED?]
          ,[DATE/DISPLAY ORDER]
          ,[POLABELS]
          ,[PTBF BY ROYAL]
          ,[PTBF BY SHIPPER]
          ,[APPROVED?]
          ,[APPROVAL DATE INFO]
          ,[MISC(Y/N)]
          ,[TOLL DECAF?]
          ,[NOLABELS1]
          ,[DESTINATION]
          ,[MISC HEDGE LABEL]
          ,[FAIR TRADE]
          ,[SHADE GROWN]
          ,[RAINFOREST ALLIANCE]
          ,[SMBC]
          ,[OCIA PRODUCER #]
          ,[OCIA ORIGINAL TRANS CERT#]
          ,[OCIA CERTIFIED?]
          ,[SLACK INDICATOR]
          ,[VESSEL]
          ,[T/S VESSEL]
          ,[VOYAGE NUMBER]
          ,[T/S VOYAGE NUMBER]
          ,[BILL OF LAIDING NUMBER]
          ,[BILL OF LAIDING DATE]
          ,[PORT OF LOADING ID]
          ,[CONTAINER NUMBER]
          ,[VESSEL ETA]
          ,[ARRIVAL PIER ID]
          ,[STEAMSHIP LINE ID]
          ,[CUSTOMS ENTRY NUMBER]
          ,[INBOUND WEIGHING]
          ,[BAG TARE]
          ,[FDA RELEASE DATE]
          ,[CUSTOMS RELEASE DATE]
          ,[Phytosanitary Cert?]
          ,[Shipper Weight cert?]
          ,[Cert of Origin?]
          ,[Fumigation Cert?]
          ,[Packing List?]
          ,[Draying Trucker ID]
          ,[MID]
          ,[YEAR ID]
          ,[FINAL DESTN WHSE]
          ,[HOTLIST]
          ,[FLOidno]
          ,[TEST MARKS]
          ,[FUTURESCOMPOSITEinvtry]
          ,[HEDGEadjustment]
          ,[FINAL WHSE-IN COSTadj]
          ,[RFAbuyTransaction]
          ,[WEBinvisible]
          ,[InventoryTransOrigRef]
          ,[NonSKUpricingFlag]
          ,[GrainProDate]
          ,[FOBExhseDescriptor]
          ,[FuturesNameDescriptor]
          ,[FixedPriceOptionCoop]
          ,[SKUaverageprice]
          ,[FuturesHedgeID]
          ,[WhseLocator]
          ,[PaymentTermInvID]
          ,[FOBtoExWhseValueTemp]
          ,[Last_UpdateBy]
          ,[PurchasedDIF_FIX]
          ,[shipterm]
          ,[OrgFixationBase]
          ,[orgdatefixed]
          ,[Orgdateinstore]
          ,[GammaProc]
          ,[SupplierCom]
          ,[RainforestCom]
          ,[OrganicCom]
          ,[GTCPrice]
          ,[GTCFilled]
          ,[GTCDateFilled]
          ,[GTCNotes]
          ,[GTCContract]
          ,[IntermediaryCom]
          ,[FairTradeMinYes]
          ,[FixationCurrentLevel]
          ,[ComOtherName]
          ,[ComOtherLevel]
          ,[MetricTonPrice]
          ,[MetricTonDif]
          ,[MetricTonFix]
    )

    SELECT [PURCHASE ORDER NO]
          ,[REFNO]
          ,[COFFEE_TYP]
          ,[CAFETYPE]
          ,[NO_BGS_AVL]
          ,[MARKS]
          ,[MARKSpredecaf]
          ,[MKSblend]
          ,[NO_BGS_IN]
          ,[RPM_CRGO_N]
          ,[DATE_IN]
          ,[NET_WT]
          ,[COST]
          ,[FINAL WHSE-IN COST]
          ,[FinalWhseInCostMendes]
          ,[FinalWhseInArchive]
          ,[FINAL WHSE-IN DIFF]
          ,[FIXATION BASIS]
          ,[ASK]
          ,[POSITION]
          ,[FINAL POSITION]
          ,[V_1_O2_D_3]
          ,[NOTES]
          ,[PRINTLABEL]
          ,[consign(y/n)]
          ,[SELLER]
          ,[SELLER AGENT]
          ,[INTERMEDIARY]
          ,[PAYMENT TERMS]
          ,[PURCHASE REMARKS]
          ,[TENDERED]
          ,[TENDER DATE]
          ,[SALE DATE]
          ,[SELLER NUMBER]
          ,[WEIGHTS]
          ,[DATE PURCHASE PAID]
          ,[PURCHASE DUE DATE]
          ,[PRICE TO BE FIXED?]
          ,[DATE FIXED]
          ,[FUTURES BASIS]
          ,[DIFFRTL PRICE]
          ,[Differentl number]
          ,[PriceDescriptor]
          ,[BBH START UP]
          ,[ADJUSTED NET INSTOCK]
          ,[FORWARD PURCH]
          ,[PAID]
          ,[D/O ALLOCATION]
          ,[WAREHSE RCPT #]
          ,[B/L DATE]
          ,[NOLABELS]
          ,[EXWHS/FOB/ETC]
          ,[H-INDEX]
          ,[PROFORMA PAYMENT]
          ,[PROFORMA DATE]
          ,[TITLE DATE]
          ,[SAS]
          ,[RCD SHIP ADVICE]
          ,[SHIP COMMENTS]
          ,[MIXED CNTR?]
          ,[No SLACKS]
          ,[READY 2 SHIP MIX CNTR]
          ,[ROYAL SF REF NO]
          ,[REJECTED?]
          ,[DATE/DISPLAY ORDER]
          ,[POLABELS]
          ,[PTBF BY ROYAL]
          ,[PTBF BY SHIPPER]
          ,[APPROVED?]
          ,[APPROVAL DATE INFO]
          ,[MISC(Y/N)]
          ,[TOLL DECAF?]
          ,[NOLABELS1]
          ,[DESTINATION]
          ,[MISC HEDGE LABEL]
          ,[FAIR TRADE]
          ,[SHADE GROWN]
          ,[RAINFOREST ALLIANCE]
          ,[SMBC]
          ,[OCIA PRODUCER #]
          ,[OCIA ORIGINAL TRANS CERT#]
          ,[OCIA CERTIFIED?]
          ,[SLACK INDICATOR]
          ,[VESSEL]
          ,[T/S VESSEL]
          ,[VOYAGE NUMBER]
          ,[T/S VOYAGE NUMBER]
          ,[BILL OF LAIDING NUMBER]
          ,[BILL OF LAIDING DATE]
          ,[PORT OF LOADING ID]
          ,[CONTAINER NUMBER]
          ,[VESSEL ETA]
          ,[ARRIVAL PIER ID]
          ,[STEAMSHIP LINE ID]
          ,[CUSTOMS ENTRY NUMBER]
          ,[INBOUND WEIGHING]
          ,[BAG TARE]
          ,[FDA RELEASE DATE]
          ,[CUSTOMS RELEASE DATE]
          ,[Phytosanitary Cert?]
          ,[Shipper Weight cert?]
          ,[Cert of Origin?]
          ,[Fumigation Cert?]
          ,[Packing List?]
          ,[Draying Trucker ID]
          ,[MID]
          ,[YEAR ID]
          ,[FINAL DESTN WHSE]
          ,[HOTLIST]
          ,[FLOidno]
          ,[TEST MARKS]
          ,[FUTURESCOMPOSITEinvtry]
          ,[HEDGEadjustment]
          ,[FINAL WHSE-IN COSTadj]
          ,[RFAbuyTransaction]
          ,[WEBinvisible]
          ,[InventoryTransOrigRef]
          ,[NonSKUpricingFlag]
          ,[GrainProDate]
          ,[FOBExhseDescriptor]
          ,[FuturesNameDescriptor]
          ,[FixedPriceOptionCoop]
          ,[SKUaverageprice]
          ,[FuturesHedgeID]
          ,[WhseLocator]
          ,[PaymentTermInvID]
          ,[FOBtoExWhseValueTemp]
          ,[Last_UpdateBy]
          ,[PurchasedDIF_FIX]
          ,[shipterm]
          ,[OrgFixationBase]
          ,[orgdatefixed]
          ,[Orgdateinstore]
          ,[GammaProc]
          ,[SupplierCom]
          ,[RainforestCom]
          ,[OrganicCom]
          ,[GTCPrice]
          ,[GTCFilled]
          ,[GTCDateFilled]
          ,[GTCNotes]
          ,[GTCContract]
          ,[IntermediaryCom]
          ,[FairTradeMinYes]
          ,[FixationCurrentLevel]
          ,[ComOtherName]
          ,[ComOtherLevel]
          ,[MetricTonPrice]
          ,[MetricTonDif]
          ,[MetricTonFix]
      FROM [14dec2012].[dbo].[Inventory] where [reff number]= 20000

    Tuesday, July 09, 2013 12:50 PM
  • Put this into one statement. It will also help if you learn the ISO-11179 naming rules. 

    INSERT INTO Inventory (.., ref_nbr, ..)
    SELECT .., NULL, ..
      FROM Inventory
     WHERE ref_nbr = '20000';

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 12:54 PM