July 6, 2009 at 3:04 pm
I have two tables, below are the two table scripts:
Create table dbo.Departments
(ID int IDENTITY(1,1) PRIMARY KEY,
Dept varchar(100))
Create table dbo.Employees
(ID int IDENTITY(1,1) PRIMARY KEY,
dept_ID INT REFERENCES Departments(ID),
Employee varchar(100),
[EmpID] [uniqueidentifier])
I have inserted the data from excel spreadsheet into dbo.Departments table.
I have the list of employees in excel which I have to insert into dbo.Employees table, I wrote SSIS to insert that data into dbo.Employees table, after inserting the employees
the two columns dept_ID and EmpID are showing as nulls when I open the table.
How can I insert the data into the two cols dept_ID and EmpID of dbo.Employees table??
Thanks!
July 6, 2009 at 3:15 pm
Hi,
For insertion of Department is stringt forward ...
Insert into Departments (Dept) values ('A')
Insert into Departments (Dept) values ('B')
Insert into Departments (Dept) values ('C')
Insert into Departments (Dept) values ('D')
select * from Departments
You will see, what you have inserted. Where as Employees ...
Insert into Employees (Dept_Id, Employee, EmpID) values (1,'Test data1', Newid())
Insert into Employees (Dept_Id, Employee, EmpID) values (2,'Test data2', Newid())
select * from Employees
Check the data. NewId() is "Creates a unique value of type uniqueidentifier"
Regards
SRI
July 6, 2009 at 3:24 pm
Since dept_ID of Employees table is a foreign key to the Departments table, what values do we insert into Employees.dept_ID field?? Thanks.
July 6, 2009 at 3:34 pm
The value should be match with Department table values. Because there is FK relationship between DEPT & EMPLOYEE tables.
July 6, 2009 at 3:41 pm
May I ask why are you using surrogate keys instead of natural keys?
Having the opportunity of seeing the original data wouldn't hurt either.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 6, 2009 at 3:43 pm
MH, if you are doing this via SSIS, you'll want to use a lookup transformation in your Employee data flow. You'll use the Department name to lookup the DepartmentID.
On another note, your Employee table has an IDENTITY ID column as well as an EmpID uniqueidentifier column. Can you explain what you are trying to do with these 2 columns?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply