SQL插入查询

Question

我有两个表，一个叫orders，另一个，orders_archive。我有一些项目标记为“项目完成”，我想进入orders表格，但我遇到了问题。有人能指引我朝着正确的方向吗？我的OrderNumber字段是身份种子，我插入的号码不在表格中。我不知道这是否有所作为。

Insert INTO Orders VALUES (OrderNumber, [Church Name], CustomerID, [Date], ShippingAddress, ShippingCity, ShippingState, ShippingZip, ToBeShippedBY, Freight, Terms, SpecialInstructions, 
    SalesRep, OrderRecievedBY, PaymentType, PaymentAccount, PaymentEXPDate, PaymentCCV, PaymentNameonCard, PaymentBillingAdd, PaymentBillingCity, 
    PaymentBillingState, PaymentBillingZip, PaymentsameasChurchAdd, Item1NO, Item1Desc, Item1QTY, Item1Price, Item2NO, Item2Desc, Item2QTY, Item2Price, 
    Item3NO, Item3Desc, Item3QTY, Item3Price, Item4NO, Item4Desc, Item4QTY, Item4Price, Item5NO, Item5Desc, Item5QTY, Item5Price, Item6NO, Item6Desc, 
    Item6QTY, Item6PRice, Item7NO, Item7Desc, Item7QTY, Item7Price, Item8NO, Item8Desc, Item8QTY, Item8Price, [Office Approved], EmailContact, 
    ENTERontoSERVERFILE, PreparePO, SendPOtoManufacture, CopyTaxExempt, ScanDOCS, BusinessUnit, SalesTax, Notes, OrderStatus, AuthNUM, TaxExempt, 
    ItemDamaged, MiscNotes, StainColor, Framecolor, Silvervein, [Tracking Number], Deposit, SENT_Receipt, Prepared_PO, Sent_PO, Created_File_ON_Server, 
    Copy_Tax_forms_to_acctng, ChangefromPAYNENTPENDING, ScanMISCDocs, Process_Payment, ConfirmONServer, CheckINVENTORY, EnterIntoBooks, 
    PlacedIntoFilingCabinet, GSTOCK, DONTSHIPBEFOREDATE, Vendor, Item1weight, Item2weight, Item3weight, Item4weight, Item5weight, Item6weight, Item7weight, 
    Item8weight, TruckNumber, ShipDate, DeliverDate, Customer, ActualQuote, PersonQuotedActual, ShipName, HeaterSerialNum 
FROM dbo.Orders_ARCHIVE 
WHERE (OrderStatus LIKE 'Order Complete')) 

Answer 1

你不能插入标识字段

的SQL Server，你可以打开标识插入，这将允许你插入标识字段上： SET IDENTITY_INSERT Orders_ARCHIVE ON

然后将其重新再次 SET IDENTITY_INSERT Orders_ARCHIVE ON

这不是一个伟大的计划，但你可以创建重复的ID。如果您不需要存档ID，那么只需将OrderNumber字段从您的插入语句中删除即可。

Insert INTO Orders 
SELECT OrderNumber, [Church Name], CustomerID, [Date], ShippingAddress, ShippingCity, ShippingState, ShippingZip, ToBeShippedBY, Freight, Terms, SpecialInstructions, 
    SalesRep, OrderRecievedBY, PaymentType, PaymentAccount, PaymentEXPDate, PaymentCCV, PaymentNameonCard, PaymentBillingAdd, PaymentBillingCity, 
    PaymentBillingState, PaymentBillingZip, PaymentsameasChurchAdd, Item1NO, Item1Desc, Item1QTY, Item1Price, Item2NO, Item2Desc, Item2QTY, Item2Price, 
    Item3NO, Item3Desc, Item3QTY, Item3Price, Item4NO, Item4Desc, Item4QTY, Item4Price, Item5NO, Item5Desc, Item5QTY, Item5Price, Item6NO, Item6Desc, 
    Item6QTY, Item6PRice, Item7NO, Item7Desc, Item7QTY, Item7Price, Item8NO, Item8Desc, Item8QTY, Item8Price, [Office Approved], EmailContact, 
    ENTERontoSERVERFILE, PreparePO, SendPOtoManufacture, CopyTaxExempt, ScanDOCS, BusinessUnit, SalesTax, Notes, OrderStatus, AuthNUM, TaxExempt, 
    ItemDamaged, MiscNotes, StainColor, Framecolor, Silvervein, [Tracking Number], Deposit, SENT_Receipt, Prepared_PO, Sent_PO, Created_File_ON_Server, 
    Copy_Tax_forms_to_acctng, ChangefromPAYNENTPENDING, ScanMISCDocs, Process_Payment, ConfirmONServer, CheckINVENTORY, EnterIntoBooks, 
    PlacedIntoFilingCabinet, GSTOCK, DONTSHIPBEFOREDATE, Vendor, Item1weight, Item2weight, Item3weight, Item4weight, Item5weight, Item6weight, Item7weight, 
    Item8weight, TruckNumber, ShipDate, DeliverDate, Customer, ActualQuote, PersonQuotedActual, ShipName, HeaterSerialNum 
FROM dbo.Orders_ARCHIVE 
WHERE (OrderStatus LIKE 'Order Complete') 

您的insert语句不正确，您不要在使用选项时使用values子句。

Answer 2

它改成这样：

SET IDENTITY_INSERT Orders ON 

INSERT INTO Orders 
SELECT <columns> 
FROM Orders_ARCHIVE 
WHERE OrderStatus LIKE '%Order Complete%' 

SET IDENTITY_INSERT Orders OFF 

使用“价值观”关键字禁止使用和加入。