Link to home
Create AccountLog in
Avatar of fieldb1
fieldb1Flag for United States of America

asked on

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

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.
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of fieldb1

ASKER

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
Avatar of fieldb1

ASKER

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.