473,249 Members | 1,381 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,249 software developers and data experts.

Alter Table Alter Column

me
I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity
after the records have been added or something similar.
here is a rough idea of what the stored procedure should do. (I do not know
the syntax to accomplish this can anyone help or explain this?

Thanks much,

CBL


CREATE proc dbo.pts_ImportJobs
as

/* add identity to [BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL

/* add records from text file here */

/* remove identity from BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] NOT NULL
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

here is the original table

CREATE TABLE [ItemTest] (
[BarCode Part#] [int] NOT NULL ,
[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
DEFAULT (0),
[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
DEFAULT (0),
[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT
(0),
[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode Part#]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Jul 20 '05 #1
2 21325

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...
I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity after the records have been added or something similar.
here is a rough idea of what the stored procedure should do. (I do not know the syntax to accomplish this can anyone help or explain this?

Thanks much,

CBL


CREATE proc dbo.pts_ImportJobs
as

/* add identity to [BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL

/* add records from text file here */

/* remove identity from BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] NOT NULL
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

here is the original table

CREATE TABLE [ItemTest] (
[BarCode Part#] [int] NOT NULL ,
[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
DEFAULT (0),
[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
DEFAULT (0),
[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT (0),
[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT (getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode Part#]
) ON [PRIMARY]
) ON [PRIMARY]
GO


You can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.

But there are several ways to INSERT identity values into a table which
already has the IDENTITY property - I suspect that's what you're really
looking for. For loading a text file with BULK INSERT or bcp.exe, there are
options to keep identity values when you import (KEEPIDENTITY and the -E
switch, respectively). For INSERTs from another table, you can use SET
IDENTITY_INSERT ON.

Finally, DBCC CHECKIDENT is used after you've INSERTed, to make sure that
the identity seed is consistent with the table data. See Books Online for
more details on all these commands.

Simon
Jul 20 '05 #2
me
Thanks for the help!

CBL
"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...
I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity after the records have been added or something similar.
here is a rough idea of what the stored procedure should do. (I do not know the syntax to accomplish this can anyone help or explain this?

Thanks much,

CBL


CREATE proc dbo.pts_ImportJobs
as

/* add identity to [BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL

/* add records from text file here */

/* remove identity from BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] NOT NULL
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

here is the original table

CREATE TABLE [ItemTest] (
[BarCode Part#] [int] NOT NULL ,
[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
DEFAULT (0),
[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
DEFAULT (0),
[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT (0),
[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT (getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode Part#]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dylan Nicholson | last post by:
Seems that Oracle 9.2 (using MS ODBC driver) requires extra parentheses when adding multiple columns to a table: ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)) vs ...
1
by: Lannsjo | last post by:
I need to change my primary key column type from smallint to int. I have tried: ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT; But get an error message certainly since my...
1
by: Bruce | last post by:
Hi, I want to change the datatype of an existing column from char to varbinary. When I run the "Alter Table" statement, I get the following error message - Disallowed implicit conversion...
5
by: minjie | last post by:
Is it possible to run a simple script to alter a table column in Access database from an interger to a double? I have been writing C++ programs every time we need to upgrade (modify) the Access...
4
by: Jeff Kish | last post by:
Hi. I have a database I need to supply something (I'm assuming a t-sql script.. maybe something else is better) to update customer tables with. The operations include mostly changing varchar...
2
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1,...
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get...
1
by: vasilip | last post by:
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table...
3
by: sparks | last post by:
several months ago I was trying to set the field size of text fields in a table and Lyle Fairfield was nice enought to post a way using alter column. it was surprising since it was ALTER COLUMN...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.