Alter Existing Table - Add PK and identity with transact SQL on table with and without data

I am using DTS to import tables from access into SQL 2000.  Then I need to add a primary key and change an existing column [RecNo] in the table to an identity - PK means not for replication i think and keep the original id values that come in.  Here is the table scripted after import with data. 2 questions please:

1) Can I write transact SQL to alter the table column [RecNo]  to be an identity and a primary key ( meaning not for replication ) with data in the table?  If so, how?

2) If this cannot be done would the proper procedure then be to create  the table as below first, then run the alter script to create an identity and a primary key  and then import the table data from access?   Or is there a better way to do this?  

Note: If I create the finished table with PK and identity using just transact SQL In Enterprise Mgr. the little yellow key never shows up and I have to do this manually.  Is the PK stored in SQL somewhere that is not scriptable by me?

Here is the table.

CREATE TABLE [dbo].[ARCASH01] (
      [RecNo] [int] NOT NULL ,
      [CUSTNO] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [REFNO] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PONUM] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PAIDAMT] [float] NOT NULL ,
      [DTEPAID] [smalldatetime] NOT NULL ,
      [CheckMemo] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CheckNum] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [USERID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GLACCNT] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ARSTAT] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Reverseyn] [bit] NOT NULL ,
      [CURRENT] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GlupdatedYN] [bit] NOT NULL ,
      [Batch] [int] NULL ,
      [RectAccntC] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RectAccntD] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UA_AdjustmentYN] [bit] NOT NULL ,
      [AorWOBatch] [int] NULL
) ON [PRIMARY]
GO
HometownCompAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

apirniaCommented:
http://www.experts-exchange.com/Databases/MS_Access/Q_20873350.html

If you go to designe mode you can do it manually no matter if it has data in it or not. However if there are data in that coloumn lets say 'Varchar 50'  and you try to cahamge it to 'Varchar 30' yuo will have data loss since you are reducing the field size only if you have infromation that size.

With the Alter table you might have to drop the coloumn first and then create a new one:

A. Alter a table to add a new column
This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column.

CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO

B. Alter a table to drop a column
This example modifies a table to remove a column.

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO

0
ptjcbCommented:
<<1) Can I write transact SQL to alter the table column [RecNo]  to be an identity and a primary key ( meaning not for replication ) with data in the table?  If so, how?

Altering a column for identity - the easiest way is to drop it and then add it

ALTER TABLE dbo.[ARCASH01] DROP COLUMN RecNo

ALTER TABLE [ARCASH01] ADD RecNo int Identity(1,1) NOT FOR REPLICATION

Primary Key is not a problem -

ALTER TABLE [ARCASH01] ADD CONSTRAINT PK_ARCASH01 PRIMARY KEY CLUSTERED
( RecNo)

I assume that you do not care about the numbers in the current RecNo column.
0
HometownCompAuthor Commented:
I want to keep the data in RecNo as it is important as an ID and PK to the rest of the DB.  There are many tables in the DB besies this one that the same operation must be performed so I do not want to use Enterprise Mgr. to manually do this operation.

Without dropping the column is there transact SQL that can alter the ID and the PK to the existing column RecNo that already contains the data?

If not then I guess the way is to add a new column to the table TempRecNo with ID and PK, select the data into TempRecNo, Then delete the data from RecNo, alter RecNo to make it and ID and PK, Then select the data from TempRecNo back into RecNo, and finally drop the TempRecNo column.

Or optionally, to script the DB and add the ID/Pk the the create table script and then import the data.

Thoughts?
0
ptjcbCommented:

>>Without dropping the column is there transact SQL that can alter the ID and the PK to the existing column RecNo that already contains the data?
No. The ALTER TABLE statements allows you to add, drop, or change the size of a column - but you want to add the Identity property to an existing definition and SQL complains when you do that.

>>If not then I guess the way is to add a new column to the table TempRecNo with ID and PK, select the data into TempRecNo, Then delete the data from RecNo, alter RecNo to make it and ID and PK, Then select the data from TempRecNo back into RecNo, and finally drop the TempRecNo column.

Be sure that you set the SET IDENTITY INSERT ON (and OFF) when you do that.

>>Or optionally, to script the DB and add the ID/Pk the the create table script and then import the data.

If you can - this is the easiest from my point of view - set up the database structure and then import the data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>Can I write transact SQL to alter the table column [RecNo]  to be an identity and a primary key ( meaning not for replication ) with data in the table?  If so, how? <<
The best you can do is create a temporary table and copy the data in.  Then delete the old table and rename the temporary table.  Incidentally, this is exactly what Enterprise Manager and MS Access for that matter does behind the scenes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.