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

[Simon Hayes]

"me" <[e-mail]> 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

[me]

Thanks for the help!

CBL
"me" <[e-mail]> 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