?
Solved

want to change identity column so numbers begin with 7...

Posted on 2009-12-23
11
Medium Priority
?
225 Views
Last Modified: 2012-05-08
UPDATE orders SET orderid = 7000000 WHERE orderid = 1287015

Cannot update identity column 'orderid'.
0
Comment
Question by:rgb192
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26114845
you cannot update the indentity column
all you can do is
enable the IDENTITY_INSERT , duplicate that row with the OrderID 7000000... and delete the old one

SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, Col2, col3 , col4 )  ------------ You MUST mention all the columns here
SELECT 7000000, Col2,Col3,Col4 FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

DELETE FROM Orders WHERE orderid = 1287015

You need to check the current identity value after this
0
 

Author Comment

by:rgb192
ID: 26114944
>>You MUST mention all the columns here
should  I do all columns
which columns should i leave out
what about keys??




CREATE TABLE [dbo].[orders](
      [orderid] [int] IDENTITY(1,1) NOT NULL,
      [ordertype] [varchar](1) NOT NULL,
      [company] [varchar](50) NOT NULL,
      [statusid] [int] NOT NULL,
      [shippable] [int] NULL CONSTRAINT [DF_orders_shipppable]  DEFAULT ((1)),
      [dateordered] [datetime] NOT NULL,
      [datepaid] [datetime] NOT NULL,
      [dateneeded] [datetime] NOT NULL,
      [customernote] [text] NOT NULL,
      [BillFirstName] [varchar](100) NOT NULL,
      [BillLastName] [varchar](100) NOT NULL,
      [BillCompany] [varchar](100) NOT NULL,
      [BillAddress1] [varchar](100) NOT NULL,
      [BillAddress2] [varchar](100) NOT NULL,
      [BillCity] [varchar](100) NOT NULL,
      [BillState] [varchar](100) NOT NULL,
      [BillZip] [varchar](15) NOT NULL,
      [BillCountry] [varchar](100) NOT NULL,
      [BillPhone1] [varchar](50) NOT NULL,
      [BillPhone2] [varchar](50) NOT NULL,
      [ShipFirstName] [varchar](100) NOT NULL,
      [ShipLastName] [varchar](100) NOT NULL,
      [ShipCompany] [varchar](100) NOT NULL,
      [ShipAddress1] [varchar](100) NOT NULL,
      [ShipAddress2] [varchar](100) NOT NULL,
      [ShipCity] [varchar](100) NOT NULL,
      [ShipState] [varchar](100) NOT NULL,
      [ShipZip] [varchar](15) NOT NULL,
      [ShipCountry] [varchar](100) NOT NULL,
      [ShipPhone1] [varchar](50) NOT NULL,
      [ShipPhone2] [varchar](50) NOT NULL,
      [ShipVia] [varchar](100) NOT NULL,
      [email] [varchar](250) NOT NULL,
      [email2] [varchar](100) NOT NULL,
      [salesman] [int] NOT NULL,
      [tax] [money] NOT NULL,
      [insurance] [money] NOT NULL CONSTRAINT [DF_orders_insurance]  DEFAULT ((0)),
      [shippingcharge] [money] NOT NULL,
      [itemid] [bigint] NULL,
      [transactionid] [bigint] NULL,
      [shiptosame] [varchar](5) NULL,
      [packageid] [int] NULL CONSTRAINT [DF_orders_boxtype]  DEFAULT ((0)),
      [ntbjemail] [int] NOT NULL CONSTRAINT [DF_orders_ntbjemail]  DEFAULT ((0)),
      [apemail] [int] NOT NULL CONSTRAINT [DF_orders_apemail]  DEFAULT ((0)),
      [contactstatus] [int] NOT NULL CONSTRAINT [DF_orders_contactstatus]  DEFAULT ((1)),
 CONSTRAINT [PK__orders__0BC6C43E] PRIMARY KEY CLUSTERED
(
      [orderid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26115008
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company,  )  ------------ fill the rest, just columnNames
SELECT 7000000, Col2,Col3,Col4  ------------ fill the rest, just columnNames
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

DELETE FROM Orders WHERE orderid = 1287015

You need to check the current identity value after this
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:rgb192
ID: 26115055
do I do this with constraint values

   [packageid] [int] NULL CONSTRAINT [DF_orders_boxtype]  DEFAULT ((0)),
      [ntbjemail] [int] NOT NULL CONSTRAINT [DF_orders_ntbjemail]  DEFAULT ((0)),
      [apemail] [int] NOT NULL CONSTRAINT [DF_orders_apemail]  DEFAULT ((0)),
      [contactstatus] [int] NOT NULL CONSTRAINT [DF_orders_contactstatus]  DEFAULT ((1)),



all column names????
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26115072
just the column names as i shown in my example, you dont have to include the datatype nor the constraint
0
 

Author Comment

by:rgb192
ID: 26115129
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company)  
SELECT 7000000, OrderID, ordertype, company  
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF


Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26115168
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company)  
SELECT 7000000,  ordertype, company  ---------------------------- you dont have to specify the orderId again
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

0
 

Author Comment

by:rgb192
ID: 26115212
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company)  
SELECT 7000000,  ordertype, company
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'statusid', table 'database.dbo.orders'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26115239
please mention all you column names,  your table has more than 20 columns and you just mentioned 3 of those "OrderID, ordertype, company" here , you MUST mention  all the colunnnames
0
 

Author Comment

by:rgb192
ID: 26115479

works

will next order be
7000001
0
 

Author Closing Comment

by:rgb192
ID: 31669566
works
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question