Adding Primary Key
-
The original table has dublicate information because it was not properly normalized. There can be several different primary key values for the same item. For example:
1 GoodBookTitle
2 GoodBookTitle
3 PracticalBookTitle
4 GoodBookTitle
5 PracticalBookTitle
6 OkBookTitle
I leave the primary key out of the query and use the group by statement to eliminate the duplicates. The Import/Export Wizard is able to save the information to the destination table however, it doesn't add a unique primary key field. If i check the box "Enable identity insert" it does not add a primary key field. If i remove the check it still does not. If i create the table in advance with a primary key field it gives me an error saying it cannot insert data with a null primary key value.
So, i can get the results into the table but i can't add a primary key to the table either while i copy the info into the destination table or afterwards.
Can anyone help me?
-
Is this a one time task or an import job?
-
The original table is in access. I use the import/export wizard. Created a query to get the unique info i need. it places it into a table that either i've already created or that is created on the fly.
If it is created on the fly there will be no PK field. I do create teh table with PK field before hand, i get errors.
-
You shouldn't get errors if you add a primary key.
You can always import the data, then add the PK...
-
but how without it telling me i can't have a null pk?
Waht i mean is that i can import hte info without a problem. I just DON"T KNOW HOW to add a PK afterward.
-
What i do is import the info. That is fine. I then go to the DB, right click on teh table, select design table. A design window comes up. I add a new column. Put in the smallint or whatever and place a primary key on it. When it tries to save and close, i get an error message.
-
I figured it out THANKS! There is a tab below that has a column listing the attributes of the Field. One says IDENTITY. After setting the PK it still says no. Once I set it to YES, it saved properly and there were no problems.
Talk about user-friendly!
-
Here's the script version :
alter dbo.TableName
add ColumnName int not null identity(1,1)
GO
much simpler... and can be reran whenever needed.
-
Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply