March 18, 2015 at 3:56 am
I am very new to SQL and still just learning the basics I'm beginning to think what I am attempting may be a little over my head at this point but here goes. I am attempting to build a relational data base. Using the import export wizard I can manage to populate the tables but when I attempt to set primary keys within the tables it won't allow them (I understand that this is due to the fact that the Primary Key must have a NULL value for it's column).
So I start over. This time I populated all of my tables with their data and used 'Drop and Create to' in the 'Script table as' drop down menu to go in and include "NOT NULL" at the end of the statements for my primary key column in each table. after having done this in all my tables I went back to review my data and it was gone!
When I tried to re-populate the tables once again I got the "No NULL's allowed" error message.
So, I've tried setting my primary keys then importing data which results in error messages and no data imported
Then I tried populating the tables then establishing my primary keys using script to set their values to ' NOT NULL' which results in my data being dumped!
I am certain I'm missing something here, can any one give me a clue?
Thanks
ED
March 18, 2015 at 4:04 am
> Then I tried populating the tables then establishing my primary keys using script to set their values to ' NOT NULL' which results in my data being dumped!
Which script did you use?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2015 at 4:05 am
Hi Ed,
Can you post your create script of the table, which column you want as your primary key and some sample rows that are causing the problem (just examples) so we can advise on best solution.
March 18, 2015 at 12:01 pm
My apologies everyone I made a mistake in my original post.
I stated that the primary key column had to have a 'NULL' value when in actuality the primary key must be 'NOT NULL'. now in answer to your question:
I didn't really use script to create my tables (this might be the problem)
I used the object explorer to create my Database by right clicking the Database folder in the object explorer and then selecting 'New Database' from the drop down menu. I then name the database and refresh the object explorer. When the new database appears in the object explorer list I right click the new database then right click the 'Table' folder and then select 'Table' from the drop down menu. I then name the various columns I intend to use in the table using the 'Table Designer' in the big window to the right of the object explorer. The 'Table Designer' prompts me to name the table before closing the window. I repeat this process for each table I want to include in my database.
I hope I using the proper terminology here guys and gals, my sincere apologies for any confusion.
March 18, 2015 at 12:45 pm
Hello Kutang and Koen,
Here is a sample of the script generated from one of the tables. All of the scripts for the tables are all similar to this. There are 5 tables in the database.
USE [Foreclosures South Washington]
GO
/****** Object: Table [dbo].[Property Info] Script Date: 3/18/2015 11:26:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Property Info](
[Bedrooms] [nvarchar](255) NULL,
[Bathrooms] [nvarchar](255) NULL,
[Garage] [nvarchar](255) NULL,
[Square Footage] [nvarchar](255) NULL,
[Property_InfoID] [int] NULL,
[Owner_InfoID] [int] NULL,
[Foreclosure_InfoID] [int] NULL,
[Address_InfoID] [int] NULL,
[Property_AddressID] [nchar](10) NULL
) ON [PRIMARY]
GO
I would want '[Property_InfoID] [int] NULL' to be the primary key. I attempted to add "NOT NULL" to the end of the statement which results in errors when I attempt to import data to the table. If I set the primary key after I've imported the data to the table it dumps the data.
I would want the following keys to eventually serve as foreign keys:
[Owner_InfoID] [int] NULL,
[Foreclosure_InfoID] [int] NULL,
[Address_InfoID] [int] NULL,
[Property_AddressID] [nchar](10) NULL
There are 12 (twelve) rows in each table. I am not quite sure which rows could cause the problem. All of the incoming data from Excel are of 'General' data type except for 3 columns in one sheets of the Excel workbook which are set to 'Date' data type.
If I set the primary before I import data I get an error message and the table is not populated with the data.
If I wait, populate all of the tables first then attempt to set primary keys, it dumps my data from the tables.
March 18, 2015 at 1:49 pm
Kutang and Koen,
Here is a sample of the data from one of the Excel sheets I want to import to one of my tables:
AddressAddress Street Name City State Zip
1931-Nw 7th Ave Camas Wa 98607-2646 Washington 98607-2646
1123-43rd St Washougal Wa 98671-6601 Washington 98671-6601
6714-Ne Par Ln Vancouver Wa 98662-8010 Washington 98662-8010
8708-Ne Mason Dr Vancouver Wa 98662-6579 Washington 98662-6579
16417-Ne 73rd St Vancouver Wa 98682-1918 Washington 98682-1918
15013-Ne 39th St Vancouver Wa 98682-8275 Washington 98682-8275
12604-Se 27th St Vancouver Wa 98683-3831 Washington 98683-3831
4518-Nw 11th Cir Camas Wa 98607-8412 Washington 98607-8412
5623-K St Washougal Wa 98671-5122 Washington 98671-5122
414- Ne 408th Ct Washougal Wa 98671-8376 Washington 98671-8376
8106-Ne 37th Ave Vancouver Wa 98665-1156 Washington 98665-1156
43105-Ne 95th Ave Woodland Wa 98674-2729 Washington 98674-2729
March 18, 2015 at 3:14 pm
e_dorham (3/18/2015)
If I set the primary before I import data I get an error message and the table is not populated with the data.
That's because your Excel file doesn't have values for Property_InfoID, which means SSIS (the import wizard) will try to insert NULL values, which will fail.
e_dorham (3/18/2015)
If I wait, populate all of the tables first then attempt to set primary keys, it dumps my data from the tables.
Setting a primary shouldn't drop data. How exactly are you doing this?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2015 at 6:10 pm
From the table designer I right click the column I want to make the primary key then from the drop down menu that appears I select 'Set Primary Key'
March 19, 2015 at 2:21 am
e_dorham (3/18/2015)
From the table designer I right click the column I want to make the primary key then from the drop down menu that appears I select 'Set Primary Key'
I have done this a lot myself, and my data never dissappears.
Instead of saving and exiting the designer, you can also create a change script (there's a button somewhere).
Generate it and post it here, so we can take a look.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 20, 2015 at 1:48 pm
Hey Koen,
This is the script generated when I use 'Set Primary Key'
USE [Troubleshoot]
GO
/****** Object: Table [dbo].[TestTable] Script Date: 3/20/2015 12:32:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
[TesterID] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TesterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I went through and checked all the script for each table in my database and found one that was different. The line of script for the primary key was different, I don't remember how or where the modified line came from so I went to each table and amended the primary key line to look like this:
[TestID] [int] IDENTITY (1,1) NOT NULL
This seemed to do the trick! All of my data was imported with just a few minor easily fixed errors.
Thanks a lot Koen, I appreciate all your help.
BTW can you recommend a good book on SQL, There are so many out there and I like to get something that thoroughly covers all aspects of SQL and is easy to understand.
Thanks again Koen, talk to you soon!:-)
March 23, 2015 at 2:12 am
What about this one:
Microsoft SQL Server 2012 Step by Step (Step by Step Developer)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2015 at 12:48 pm
Thanks Koen, I picked up Microsoft SQL Server 2012 on Amazon. I was torn between the book you suggested (Microsoft SQL Server and Murach's SQL 2012. I decided to go with your suggestion.
Thanks again! 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply