CODEWITHSUNDEEP
sqlsql-serversql-server-2008
getbruce
getbruce

Reputation: 103

Trying to insert data correctly

After receiving very good correction from fuzzy lollipop, I amended my code to create an insert statement for every row of data. This is the code that I entered:

INSERT DEPARTMENTS
(Department_Id,Department_Name,Manager_Id,Location_Id)
VALUES
('D0001','Think Tank',NULL,'L0001')
GO

INSERT DEPARTMENTS
(Department_Id,Department_Name,Manager_Id,Location_Id)
VALUES
('D0002','Creators',NULL,'L0002')
GO

INSERT DEPARTMENTS
(Department_Id,Department_Name,Manager_Id,Location_Id)
VALUES
('D0003','Marketers',NULL,'L0003')
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0001','Joe','Blow',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0002','John','Doe',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0003','Sue','Happy',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0004','Tina','Turner',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0005','Ike','Turner',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0006','Big','Bird',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0007','Speedy','Gonzales',NULL,NULL,2010/06/25,NULL,NULL)
GO

However, these were the error messages that I received:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DEPARTMEN__Locat__09DE7BCC". The conflict occurred in database "Final_Project", table "dbo.LOCATIONS", column 'Location_ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DEPARTMEN__Locat__09DE7BCC". The conflict occurred in database "Final_Project", table "dbo.LOCATIONS", column 'Location_ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DEPARTMEN__Locat__09DE7BCC". The conflict occurred in database "Final_Project", table "dbo.LOCATIONS", column 'Location_ID'.
The statement has been terminated.
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date

I won’t make the same mistake by failing to respond immediately to solutions. I did not know the checking the green checkmark meant that the answer was satisfactory.

Thank you for any help

Upvotes: 2

Views: 4165

Answers (3)

salchams
salchams

Reputation: 39

  • Is there a FOREIGN KEY constraint between Department and Location. If so, then inserting a deparment requires an existing Location. Location is linked to Department with Location_ID

  • Timestamps syntax should be as a string '12/12/2010' not without the '-signs

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838156

You have two different types of errors.

The first is that you are violating a foreign key constraint. There are three ways to solve this:

  • Find out what the correct key should be (for example by querying the LOCATIONS table) and change your foreign key to the correct value.
  • Insert the missing row in the LOCATIONS table before inserting into DEPARTMENTS.
  • Remove the constraint (this is probably a bad idea).

The second error is simpler - you have incorrectly formatted your date. It should be a string.

'2010-06-25'

The complete query:

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0001','Joe','Blow',NULL,NULL,'2010-06-25',NULL,NULL)

Upvotes: 2

devio
devio

Reputation: 37205

1) there are no records with the given LocationIDs in the Location table

2) you need to quote the date values

Upvotes: 1

Related Questions