CREATE TABLE [dbo].[temp](
[employee_id] [char](7) NOT NULL,
[first_name] [char](50) NOT NULL,
[middle_name] [char](50) NOT NULL,
[last_name] [char](50) NOT NULL,
[title] [char](5) NOT NULL,
[sex] [char](1) NOT NULL,
--add here more columns and extend them into the cursor
[from_date] [datetime] NULL,
[to_date] [datetime] NULL,
[grade_code] [char] (5) NULL,
[manager_id] [char] (7) NULL
)

insert into temp
values
('1','igor','dimitry','micev','eng','m','2002-10-01','2012-10-10','10','1'),
('2','tony','petar','stojanov','eng','m','2002-05-01','2012-10-10','9','1')
select * from temp

CREATE TABLE [dbo].[employee](
[employee_id] [char](7) NOT NULL,
[first_name] [char](50) NOT NULL,
[middle_name] [char](50) NOT NULL,
[last_name] [char](50) NOT NULL,
[title] [char](5) NOT NULL,
[sex] [char](1) NOT NULL,
)
CREATE TABLE [dbo].[employee_details](
[employee_id] [char](7) NOT NULL,
[from_date] [datetime] NULL,
[to_date] [datetime] NULL,
[grade_code] [char] (5) NULL,
[manager_id] [char] (7) NULL
)

go
create procedure split_and_insert
as
begin
	set nocount on
	declare myCursor cursor for
	select employee_id,first_name,middle_name,last_name,title,sex,from_date,to_date,grade_code,manager_id	
	from temp	
	open myCursor		
	declare @empid char(7)
	declare @fname char(50)
	declare @mname char(50)
	declare @lname char(50)
	declare @title char(5)
	declare @sex char(1)
	declare @from_date datetime
	declare @to_date datetime
	declare @grade char(5)
	declare @mgr_id char(7)
	
	fetch next from myCursor into
	@empid,@fname,@mname,@lname,@title,@sex,@from_date,@to_date,@grade,@mgr_id
	
	while @@fetch_status=0
	begin
		insert into employee(employee_id,first_name,middle_name,last_name,title,sex)
		values(@empid,@fname,@mname,@lname,@title,@sex)
		
		insert into employee_details(employee_id,from_date,to_date,grade_code,manager_id)
		values(@empid,@from_date,@to_date,@grade,@mgr_id)
		
		fetch next from myCursor into
		@empid,@fname,@mname,@lname,@title,@sex,@from_date,@to_date,@grade,@mgr_id
	end
	
	close myCursor
	deallocate myCursor
	return 1;
end

exec split_and_insert
select * from employee
select * from employee_details

select * from temp
		insert into employee(employee_id,first_name,middle_name,last_name,title,sex)
		select employee_id,first_name,middle_name,last_name,title,sex
		from temp
		
		insert into employee_details(employee_id,from_date,to_date,grade_code,manager_id)
		select employee_id,from_date,to_date,grade_code,manager_id
		from temp

select * from employee
select * from employee_details

declare @var varchar(20)
set @var = '24-10-2012'
print @var
declare @date date
set @date = convert(date,@var,105)
print @date

declare @var varchar(20)
set @var = '24-Oct-2012'
print @var
declare @date date
set @date = convert(date,@var,105)
print @date