Solved

Import Data into SQL Database

Posted on 2013-01-30
10
584 Views
Last Modified: 2013-02-18
Hi,

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?

Thanks,
tg
0
Comment
Question by:techguy57
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 12

Expert Comment

by:Tony303
Comment Utility
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.

IF YOU GO WITH THIS APPROACH...

COPY YOUR TABLE TO A TEST TABLE or DATABASE FIRST,
And apply the changes to this test table, to see if this approach works.

****ALWAYS THINK....WHAT IS MY FALLBACK POSITION****
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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.
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
0
 

Author Comment

by:techguy57
Comment Utility
@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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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:
ALTER TABLE [TableName]
 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])
GO

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

Expert Comment

by:Mark Wills
Comment Utility
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.
0
 

Author Comment

by:techguy57
Comment Utility
USE [User_Test_Import]
GO

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

SET QUOTED_IDENTIFIER ON
GO

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
) ON [PRIMARY]

GO

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
Right....

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

Author Closing Comment

by:techguy57
Comment Utility
Pointed me in the right direction for editing my own script..

Thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now