problems with insert

ok, here is the script i used to make my tables:

use inventory
go
CREATE TABLE Job_Title
(Job_Title_Title varchar(25) NOT NULL ,
Job_Title_EEO1_Classification varchar(25) ,
Job_Title_Job_Description Varchar(45) ,
Job_Title_Exempt_Status Varchar(15)
, PRIMARY KEY (Job_Title_Title)
);

CREATE TABLE Employee
(Employee_Emp_ID smallint NOT NULL ,
Employee_last_name varchar(15) ,
Employee_first_name varchar(15) ,
Employee_address varchar(30) ,
Employee_city varchar(15) ,
Employee_state char(2) ,
Employee_Telephone_area_code char(3) ,
Employee_Telephone_number char(8) ,
Employee_EEO1_Classification varchar(25) ,
Employee_Hire_Date varchar(8) ,
Employee_Salary int ,
Employee_Gender Varchar(1) ,
Employee_Age tinyint ,
Employee_Title varchar(25)
, PRIMARY KEY (Employee_Emp_ID)
, constraint Employee2Job_Title FOREIGN KEY (Employee_Title
) REFERENCES Job_Title
);

here is the script i am trying to use to insert my data:

use inventory
go
Insert INTO dbo.Employee Values
(1,'Edelman','Glenn','175 Bishop Lane','La Jolla','CA',619,'555-0199','Sales Workers','10/7/2003',21500,'M',64,'Cashier');
Insert INTO dbo.Employee Values
(2,'McMullen','Eric','762 Church Street','Lemon Grove','CA',619,'555-0133','Sales Workers','11/1/2002',13500,'M',20,'Bagger');
Insert INTO dbo.Employee Values
(3,'Slenj','Raj','123 Torrey Drive','North Clairmont','CA',619,'555-0123','Officials & Managers','6/1/2000',48000,'M',34,'Assistant Manager');
Insert INTO dbo.Employee Values
(4,'Broun','Erin','2045 Parkway Apt 2b','Encinitas','CA',760,'555-0100','Sales Workers','3/12/2003',10530,'F',24,'Bagger - 30 hours/wk');
Insert INTO dbo.Employee Values
(5,'Carpenter','Donald','927 Second Street','Encinitas','CA',619,'555-0154','Office/Clerical','11/1/2003',15000,'M',18,'Stocker');
Insert INTO dbo.Employee Values
(6,'Esquivez','David','10983 North Coast Highway Apt 902','Encinitas','CA',760,'555-0108','Operatives (Semi skilled)','7/25/2003',18500,'M',25,'Asst. - Butchers & Seafood Specialists');
Insert INTO dbo.Employee Values
(7,'Sharp','Nancy','10793 Montecino Road','Ramona','CA',858,'555-0135','Sales Workers','7/12/2003',21000,'F',24,'Cashier');

and here are my errors:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 3
String or binary data would be truncated.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the FOREIGN KEY constraint "Employee2Job_Title". The conflict occurred in database "Inventory", table "dbo.Job_Title", column 'Job_Title_Title'.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 9
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 11
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 13
String or binary data would be truncated.
The statement has been terminated.

HelpA couple of things to consider:

First, you have the Employee_Hire_Date defined as a varchar(8) and you are attempting to store 9 characters in it, eg 10/7/2003.

Also, it appears that you are trying to insert an Employee with an Employee_Title that does not have a corresponding row in the Job_Title table.

HTH...

Joe|||well i got every thing fixed except for:

Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the FOREIGN KEY constraint "Employee2Job_Title". The conflict occurred in database "Inventory", table "dbo.Job_Title", column 'Job_Title_Title'.
The statement has been terminated.

still working on this
|||

From the order of your errors earlier, i think this statement is causing the problem:

insert into employee
values (3,'Slenj','Raj','123 Torrey Drive','North Clairmont','CA',619,'555-0123','Officials & Managers','6/1/2000',48000,'M',34,'Assistant Manager')

And as Joe correctly suggested, its because you are trying to add a record to the employees table with a value in Employee_Title which doesn't exist in the Job_Title_Title field in the Job_title table - in this case, Assistant Manager.

To fix the error, you'll just need to add the Assistant Manager record to the Job_Title table.

HTHT!

|||Does this mean that i have to fill the Job_Title table first, before i can fill the Employee table? I didnt think that they were dependent upon each other. ok, i am at work now, will try this when i get home in a few hours. thanks!|||

Yes, the foreign key value(s) (lookup table) must be in place before the primary key row (that refers to the foreign key row) can be added--assuming you have created a PK/FK. No, the entire FK table need not be pre-populated, but that's often how it's done.

hth

|||Well, i finally got it. I did change the field size of the date field to 10, and that helped. i also loaded the jobs table first, and that took care of the key problem.

Thanks for all the help, now i must move on to the next phase, so i will probly need more help again soon but i am very happy to get this part finished.

Thanks again! :-)|||So, I have a question for you. Why did you choose a string to handle a DateTime field? Do you plan to write all of your own date validation, artihmetic and parsing routines yourself?