CODEWITHSUNDEEP
sqlsql-server
GabrielVa
GabrielVa

Reputation: 2388

SQL Insert Query

I have two tables, one called orders, the other, orders_archive. I have some items marked "item Complete" that I want to get into the orders table, but I'm having issue with it. Can someone point me in the right direction? My OrderNumber field is an identity seed, the numbers I'm inserting are not in the table. I don't know if that makes a difference or not. 

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'))

Upvotes: 0

Views: 95

Answers (2)

MRB
MRB

Reputation: 3946

You cant insert into an identity field

on sql server you can turn on identity insert which will allow you to insert into identity fields: SET IDENTITY_INSERT Orders_ARCHIVE ON

then turn it back on again SET IDENTITY_INSERT Orders_ARCHIVE ON

This is not a great plan though as you could create duplicate ids. If you don't need the archive id then just pull OrderNumber field off your insert statement.

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')

Your insert statement was incorrect you dont use the values clause when your doing a select.

Upvotes: 0

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Change it to this:

SET IDENTITY_INSERT Orders ON

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

SET IDENTITY_INSERT Orders OFF

Using the "VALUES" keyword prohibits the use of FROM and JOIN.

Upvotes: 2

Related Questions