Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-04-14
5
Medium Priority
?
5,227 Views
Last Modified: 2008-11-06
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
0
Comment
Question by:HometownComp
5 Comments
 
LVL 9

Assisted Solution

by:apirnia
apirnia earned 200 total points
ID: 16454590
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16454641
<<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
 

Author Comment

by:HometownComp
ID: 16455394
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
 
LVL 27

Accepted Solution

by:
ptjcb earned 900 total points
ID: 16455759

>>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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 900 total points
ID: 16456113
>>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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

580 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