Add unique id column - newbie

I have a table (inherited) that gets data from a DTS package. The problem i
s
that the table that the data is being read in to wasn't created with a uniqu
e
id field. Is there a way that I can add a unique id field and have it
populated?
This table has 86000 records in it already.
TIAThe following article describes how to choose and implement a primary
(unique) key
http://www.aspfaq.com/show.asp?id=2504
"ks" <ks@.discussions.microsoft.com> wrote in message
news:B4F3293A-D2C2-4C67-9772-164E5693D467@.microsoft.com...
>I have a table (inherited) that gets data from a DTS package. The problem
>is
> that the table that the data is being read in to wasn't created with a
> unique
> id field. Is there a way that I can add a unique id field and have it
> populated?
> This table has 86000 records in it already.
> TIA

Add UNIQUE constraint

Hi,
I want to add unique contraint to EXISTING column in EXISTING table.

I have ms sql 2005.

How to do that?

If you have ms SQL Express 2005, then you should probably download and install SQL Server Management Studio Express. Once that's done, it is easy - attach the database file to SQL Express, then add the unique constraint there.

Careful if you are coming from an MS Access world. In MS Access, you can specify a unique constraint that will ignore NULL values. In that case, the constrained column will be unique only if the value is not null. You can have many entries where the value is null. If that is an issue, then there is another way to do it using a Schema-bound view that selects only non-null entries from the original table and applies a unique index to the entry in the view.

You will also find an excellent reference in the SQL Books Online, which you can download and install on your machine.

Hope this helps,

Flavelle

|||I have SQL Standrard. I read books online.

I need exact steps, I cannot understand Books.

Can you please provide me with steps or procedure that would do that?|||

give me a little time, and I'll come up with a couple of sample scripts that you can run.

Flavelle

|||Ok, I'm waiting.

Just give me the simplest script, or explain how to set it in Studio.|||

fballem:

If you have ms SQL Express 2005, then you should probably download and install SQL Server Management Studio Express. Once that's done, it is easy - attach the database file to SQL Express, then add the unique constraint there.

Careful if you are coming from an MS Access world. In MS Access, you can specify a unique constraint that will ignore NULL values. In that case, the constrained column will be unique only if the value is not null. You can have many entries where the value is null. If that is an issue, then there is another way to do it using a Schema-bound view that selects only non-null entries from the original table and applies a unique index to the entry in the view.

You will also find an excellent reference in the SQL Books Online, which you can download and install on your machine.

Hope this helps,

Flavelle

In SQL Server you can create a Unique Index with the IGNORE_DUP_KEY option and duplicates will not be inserted. Unique constraints are nullable while Unique indexes are not. Try the link below for step by step of how to create Unique Constraint with the GUI in management studio. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueindex.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueconstraint.asp

|||Thanks a lot.
These articles really helped me.|||

And if you wanted to do it via script, here are two versions:

This script, applied to a table, will result in a unique index. If the column allows Null, then only one row can contain Null. Replace TestTable with the name of your table and TestColumnUnique with the name of the column:

ALTER TABLE [dbo].[TestTable]ADD CONSTRAINT [UQ_TestTable_TestColumnUnique]UNIQUE NONCLUSTERED ([TestColumnUnique]ASC)WITH (PAD_INDEX =OFF, SORT_IN_TEMPDB =OFF, IGNORE_DUP_KEY =OFF, ONLINE =OFF)ON [PRIMARY]

The following two scripts will result in a schema-bound view with an index:

The first script will create the schema bound view. Note that TestView is the name of the view, TestTable is the name of the table from which the data will be obtained and TestColumnNonNullUnique is the name of the column to which you want to apply the constraint. Note that only non-null values are included in the view:

CREATE VIEW [dbo].[TestView]
WITH SCHEMABINDING
AS
SELECT ID, TestColumnNonNullUnique
FROM dbo.TestTable
WHERE (TestColumnNonNullUniqueISNOT NULL)
GO

Once you have set up the view, then run the following script, which will setup the index on the view. Because only non-null values are included in the view, you can have multiple null entries in the original column:

CREATE UNIQUE CLUSTERED INDEX [UQ_TestView_TestColumnNonNullUnique]ON [dbo].[TestView] ([TestColumnNonNullUnique]ASC)WITH (PAD_INDEX =OFF, SORT_IN_TEMPDB =OFF, DROP_EXISTING =OFF, IGNORE_DUP_KEY =OFF, ONLINE =OFF)ON [PRIMARY]

Hope that this is the final piece to the puzzle. I wish that I could take credit for this approach, but another member of the forum was kind enough to provide me with the solution, so I am happy to pass it on.

Regards,

Flavelle

Add ID at Insert

The below code works fine, but for one reason, I need to get the ReferenceID inserted into a seperate table along with the newly created unique ID in the table Box. Any ideas how I can accomplish this...

CREATE PROCEDURE AddBox
(
@.BoxNumber nvarchar(40),
@.LocId int,
@.BoxNotes Text,
--This gets added to a different table that I'll call References, but can't go until I can get the uniqueID of what is inserted.
@.ReferenceID int
)
AS
INSERT INTO box
(
box_num,
loc_id,
box_notes,
status_id
)
VALUES
(
@.BoxNumber,
@.Locid,
@.BoxNotes,
2
)

didnt quite understand your question but check out BOL for SCOPE_IDENTITY().

hth|||Just the point in the right direction I needed, Thanks.

Once I inserted a new box file into the Box table I then was able to get the Unique ID number and then insert that into a reference table. The end code (after the first insert) for my previous post ends up looking as such:

Set @.Identity = SCOPE_IDENTITY()
Set @.Reference = @.ReferenceNumber
Insert into reference
(
reference_name,
box_id
)
VALUES
(
 @.Reference,
 @.Identity
)
GO