Go Premium for a chance to win a PS4. Enter to Win


Import Data into SQL Database

Posted on 2013-01-30
Medium Priority
Last Modified: 2013-02-18

I am attempting an import of an Excel Spreadsheet with data to create some tables within a database. it does not not allow me to create a Primary & Foreign keys. From Management Studio, I add the key via the GUI, but when I save the changes receive an error stating "DROP/CREATE" is required?

I am somewhat of a newbie to writing SQL statements.. Can someone possibly point me in the right direction?

Question by:techguy57
  • 3
  • 3
  • 2
  • +2
LVL 12

Expert Comment

ID: 38837530
When you edit the table, like you are, there should be an icon in the toolbar at the top that is  "Generate Change Script".

I supect this will generate code that will do the following...

1. Copy existing data into a temporary table.
2. Drop your table.
3. Create the table again using your new logic, ie Primary and Foreign Keys.
4. Copy your data from the temp table in step 1 into your new table.

I think that depending on your SSMS timeout setting, if you just used the GUI you may see a timeout, so the process will fail.

What I do is Copy this change script into a new SSMS query and execute it. This will take as long as it needs.


And apply the changes to this test table, to see if this approach works.

LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 38837882
SSMS is very averse to making schema changes as of late.  It will always take the approach of the steps above, so it will just let you know that it will not do it.

The concept is that you should script your actions out as recommended above.

But the other thing is you can use Books Online to get the syntax of what you want and then execute it yourself.

You can find a Primary Key and a Foreign key in your tables and script them out to CREATE and then use them as templates to put your own tables and columns in.  That is the path I would take is to use templates.
LVL 37

Expert Comment

ID: 38838625
If you want SSMS to actually go ahead with the change by using the GUI, you can do that.  All you need to do is change the default settings: Tools > Options > Designers > Table and Database Designers > uncheck the Prevent saving changes that require table re-creation checkbox.  Now SSMS will no longer refuse to make this change.

Be aware that on large tables this might take a while. In that case it would be more interesting to take the script approach as mentioned above.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 51

Expert Comment

by:Mark Wills
ID: 38839162
When are you running into trouble with creating your Primary and Foreign keys ?

Have you been able to import the data ?

Are you importing into an existing table - or - does the import create the table (better still, how are you doing your import) ?

Did you then try to create the Primary Key ?

Where does the Foriegn key point to / cross reference ?

There can be a few reasons why you are seeing it. If a primary key then needs to be not null and unique... So, might need to double check your Data.

There is also a KB article worth reading : http://support.microsoft.com/kb/956176

Author Comment

ID: 38839354
@Tony303 I have attempted to modify the table and then "generate the script", but I shows greyed out?

How do I enable this feature? Should highlight a certain field?

@dbaduck I there any other script generator out there that allows the creation of sample syntax referred  to from the Books Online? Or is Management Studio the only option?

@mark_willis Yes, the data has imported ok.. I am importing into a newly created database with existing data within a Excel Spreadsheet. Then from that point I would like to create new table for data entry, with dependencies from each other.
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 38839630
Well, I am not sure that there are programs that just do this (generate templates), but there are templates like in CodeSmithTools.com Generator, but they are not free.

Here is a template for adding a Primary Key to a table:
 ADD CONSTRAINT [Name_Of_Constraint] PRIMARY KEY ([Column_Name])

Here is a Foreign Key
ALTER TABLE [dbo].[TableName]  WITH NOCHECK ADD  CONSTRAINT [Constraint_Name] FOREIGN KEY([ColumnName])
REFERENCES [dbo].[ReferencedTable] ([ReferenceColumnName])

ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [ConstraintName]
LVL 51

Expert Comment

by:Mark Wills
ID: 38840008
Ok, data has imported...

1) Do you have candidate columns for the Primary Key ?
2) Are they properly populated and unique ?
3) Do you want to use a surrogate PK ?
4) Do your other tables have Primary Keys ?
5) Have you cleansed the table structure ? Or do you want to create a "clean" home ?

Depending on how you have imported, the datatypes of the new table might be a bit skew, or, size might not be what you need.

Normally after an import operation (again, not sure how you have done it), you would run some kind of validation process

a) check for NULL in PK columns e.g.
select * 
from <your_table_name> 
where PK_Col1 is NULL 
or      PK_Col2 is NULL

Open in new window

b) check for uniqueness e.g.
select PK_Col1,PK_Col2, count(*) 
from <your_table_name>  
group by PK_Col1,PK_Col2
having count(*) > 1

Open in new window

If that is OK, then need to make sure that PK_Col1 and PK_Col2 are set to be NOT NULL before you can make them part of the primary key.

Can you share the table structure (right click on the table name and "script table" as  "create" to a new query window.

Author Comment

ID: 38841289
USE [User_Test_Import]

/****** Object:  Table [dbo].[EmployeeInformation$]    Script Date: 01/31/2013 14:42:17 ******/


CREATE TABLE [dbo].[EmployeeInformation$](
	[File_Number] [nvarchar](255) NULL,
	[Payroll_Name] [nvarchar](255) NULL,
	[Home_Department] [nvarchar](255) NULL,
	[Home_Department_Desc] [nvarchar](255) NULL,
	[Location] [nvarchar](255) NULL,
	[Employee_Type] [nvarchar](255) NULL,
	[Rate_Amount] [float] NULL,
	[Commission_Percentage] [float] NULL,
	[Unit_Num] [float] NULL,
	[Status] [nvarchar](255) NULL,
	[Address_Line_1] [nvarchar](255) NULL,
	[City] [nvarchar](255) NULL,
	[State] [nvarchar](255) NULL,
	[Zip_Code] [nvarchar](255) NULL,
	[Home_Phone] [nvarchar](255) NULL,
	[Hire_Date] [datetime] NULL,
	[Personal_Email] [nvarchar](255) NULL


Open in new window

LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 38842273

Well that is the fairly typical result of an import and not really the best design for a table. Most imports need to take the "easy" path when matching datatypes and deciding how to store.

So, ideally you tidy that up before you start adding additional constraints like Primary Keys.

For a starter, NVARCHAR() is a unicode datatype and uses 2 bytes per character. Unless you need unicode, then you can save some space. Similarly, FLOAT is a floating point number and would imagine that rate_amount is more like a money or decimal number. Some of these considerations can also save some potential performance issues down the track.

There is probably a fair bit to do in terms of cleaning up, and applying indexes / PK's to give you the data model you are looking for (with referential integrity etc).

I am assuming that file_number should uniquely identify a person - and looks to be the ideal candidate for a Primary Key.

You also mention that you want to create a new table. I see that you are using a TEST IMPORT DB and is pretty much the same as having a staging table assuming your real home is a different database. Do the new tables already exist in the new DB ?

The sequence of creating new tables is very important. If you are going to have foreign keys, then the target tables of foreign keys *should* exist first with their PK's in place.

So, first thing is NULL, and probably the size / datatype of the column.

you can open the table and inspect visually, or, use the table designer for most of this, but there is a set sequence that needs to be followed and a couple of little "gotcha's" in SSMS as you have already discovered (and can be overcome).

Step 1 check to see if there are NULL values
SELECT * FROM EmployeeInformation$ where File_Number IS NULL

Open in new window

Now, if that returned any values, you need to decide if they need to be there, or, if File_Number is a candidate for Primary Key (ie it is compulsory and must have a value). If not, there is another approach further below.

If you dont want any of the entries where file_number is NULL then swap the SELECT * for the word DELETE. ie
DELETE FROM EmployeeInformation$ where File_Number IS NULL -- and /*maybe*/ payroll_name is NULL

Open in new window

When importing from a spreadsheet you can end up with NULL rows, so it would not be a surprise.

OK, next step, getting rid of the NULL and making it NOT NULL, and may as well tidy up the length.
-- first lets find the longest string for file_number

SELECT max(LEN(File_Number)) FROM EmployeeInformation$ 

-- and a reasonableness check to accommodate a bit of variation unless it really is a fixed length and has a prescribed format.
-- Now, we will use that number to alter the characteristics of the column, for a start it is not needed as unicode and never will be (assumption of course)
-- lets say that the max(len(file_number)) came back as 18 and we know that in the future there will be an additional 2 characters, so, we will make our column a varchar of 20.

ALTER TABLE EmployeeInformation$ alter column File_Number varchar(20) NOT NULL 

-- Now our column is looking pretty good and is in a position to become a primary key

ALTER TABLE EmployeeInformation$ add constraint PK_EmployeeInformation Primary Key (File_Number)

-- and that is the first one done !!

Open in new window

Now, when you go to run a list of commands in a query window, simply highlight the code that you want to execute (for example the first select) and the press F5 (key board shortcut for "run").

Now, that table can be used as a target for referential integrity. But you would verify the employee based on their file_number. This is when you could also consider a surrogate key.

A surrogate is basically a data independant unique identifier and there are a few choices. I think the most popular is the humble indentity key. You might have to use this approach if there are NULLs in file_number and you do need to keep the data.

-- so instead of using file_number we have decided that a surrogate key is a better approach
-- the previous tidy ups are still relevant, just not the final step for the Primary Key.

-- If we did add the previous PK, we can also drop it
ALTER TABLE EmployeeInformation$ drop constraint PK_EmployeeInformation

-- Now let's create a surrogate PK using an integer Identity (like an auto-number column)

ALTER TABLE EmployeeInformation$ ADD EMP_ID int identity constraint PK_EmployeeInformation_ID Primary Key

Open in new window

Now, I have also noticed : [Home_Department]  and  [Home_Department_Desc] which appears as if they would normally be in a lookup type table. So, lets do that... (and show another way of generating tables)
-- first we will create a new table using select into
-- the target of the into must not already exist otherwise it fails

select identity(INT,1,1) as HD_ID,
       convert(varchar(10),Home_Department) as Home_Department,
       convert(varchar(60),isnull(Home_Department_Desc,Home_Department)) as Home_Department_Desc
into tbl_Home_Department
from EmployeeInformation$
where Home_Department is not NULL
group by Home_Department,Home_Department_Desc

-- Now we have a new table and it has a surrogate HD_ID so, we need to make that our PK
Alter table tbl_Home_Department add constraint PK_Home_Department_ID Primary Key (HD_ID)

-- Then, we can add that new column as a foreign key to our EmployeeInformation$ table

alter table EmployeeInformation$ add HD_ID int constraint FK_EmployeeInformation_HD_ID foreign key references tbl_home_department(HD_ID)

-- Right, so, now we have that new column, we need to populate

update EmployeeInformation$ 
set HD_ID = H.HD_ID
from tbl_home_department H
where EmployeeInformation$.Home_Department = H.Home_Department 
and isnull(EmployeeInformation$.Home_Department_desc,EmployeeInformation$.Home_Department) = H.Home_Department_Desc

-- and then we can drop the now redundant columns in EmployeeInformation$

alter table EmployeeInformation$ drop column Home_Department, Home_Department_Desc

-- now that you have referential integrity, you will always need to add new home_departments first, then the EmployeeInformation$

Open in new window

Right... Phew...

Looks like a lot of work, but it isnt really. You soon get used to writing T-SQL.

That last "select into" example is something you can do for any of your tables and does save a bit of time doing all those alter table commands.

Author Closing Comment

ID: 38903357
Pointed me in the right direction for editing my own script..


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question