We help IT Professionals succeed at work.

How to add primary keys to database tables with identical column names

fieldb1
fieldb1 used Ask the Experts™
on
I have imported a bunch of excel spreadsheets into a SQL Server Database. Each table is named for a spreadsheet which represents one month. Now, the fields of each table are identical, being as the various tables represent different monthly data. What is the best way to add primary keys to these tables? I would like to use the Truck# field, but after adding that key to one table I get an error when altering other tables. The error is below.

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_Truck#' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

this is a bad database design. Whenever you find tables in your database with equal table structure their contents should be copied to one table with the same structure, adding a field to distinguish the data (in your example a "month" field or date field for example). You can always use a SELECT to get the data of one month.

The problem you have is not a big problem: All objects in a database must have a unique name, and indices are also objects. So simply open the index window in SQL Server Management Studio for the wanted table and rename the PK index name. Then you can add as many Truck PKs as you want (you should also avoid any spaces and special characters like "#" in field names or other names).

Cheers,

Christian

Author

Commented:
Thanks. I'm till trying to figure out whether it's even worth it to import data contained in the many excel workbooks our finance department maintains into a database. Right now it seems like more trouble than it's worth. My main concern is that users in the finance department don't know how to implement advanced excel functions to convert the data into business intelligence, and our IT department is looking at the development of an application for them. Long story short, it may be a better idea to require the users to increase their ability and leave the data in the spreadsheets.
Hi,

you could use SQL Server Integration Services which allows you to adapt importing of external sources in a GUI driven way. With this it should be possible to import the data into one table even if there is a changing structure of the original data (in limits, of course).

The better idea is to standardize the data so that it could be imported to one table - because only in this case you can do rich investigations for reports of any kind. Spreading it over different tables would always mean to use UNION SELECTs, which are slow and (for this purpose) uncomfortable as you would need to adjust queries any time a new table would be inserted into the database.

If you would only need to save the Excel data it would be easier to copy the files into a binary column, so anyone can access them, change them and save them back - but a fileserver would do a better job for this purpose. If you want to analyze the data in any way then the only way which makes sense would be to insert all the data into one table (or a structure of linked tables).

Cheers,

Christian

Author

Commented:
Still, the question remains whether this is worth the time and effort. Personally, I generally dump database data into excel for analysis, not the other way around. But my function is not financial analysis. It still seems easier to educate the users in advanced spreadsheet functions.