Avatar of fieldb1
fieldb1
Flag 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.
Microsoft DevelopmentMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
fieldb1

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Bitsqueezer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Bitsqueezer

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
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.

Your help has saved me hundreds of hours of internet surfing.
fblack61