Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored procedure to split and insert values into tables Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 12:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55
I have an empty employee table and employee_details table.
The temp table which i created say it has 10 columns of which 6 are from employees and 4 from employee_details.
I have loaded some data into temp table say 10 rows.
Now the stored procedure using cursor should be created such that, it should fetch the rows one by one from temp table and insert the values into employee table(6 columns) and the rest in employee_details table(4 columns).
This is the scenario.
Here is the column names of my temp table

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,
[dt_of_birth] [datetime] NOT NULL,
[dt_of_joining] [datetime] NOT NULL,
[father_name] [char](40) NULL,
[perm_address_line_1] [char](200) NOT NULL,
[perm_address_line_2] [char](200) NULL,
[perm_city] [char](20) NOT NULL,
[perm_state] [char](12) NOT NULL,
[perm_pincode] [int] NULL,
[perm_phone_landline] [char](15) NULL,
[perm_phone_mobile] [char](15) NULL,
[present_address_line_1] [char](200) NOT NULL,
[present_address_line_2] [char](200) NULL,
[present_city] [char](20) NOT NULL,
[present_state] [char](12) NOT NULL,
[present_pincode] [int] NULL,
[marital_status] [char](1) NOT NULL,
[dt_of_marriage] [datetime] NULL,
[qualification] [char](10) NULL,
[blood_group] [char](3) NULL,
[email_id] [char](30) NULL,
[workex_current] [smallint] NULL,
[workex_past] [smallint] NULL,
[emergency_contact_name] [char](50) NULL,
[emergency_contact_telnon] [char](15) NULL,
[dept_code] [char] (7) NULL,
[div_code] [char] (7) NULL,
[branch_code] [char] (5) NULL,
[designation] [char] (10) NULL,
[from_date] [datetime] NULL,
[to_date] [datetime] NULL,
[grade_code] [char] (5) NULL,
[manager_id] [char] (7) NULL)

Here the last 4 columns belong to the employee_details table.
The stored procedure should fetch record by record from temp split and insert into employee and employee_details table.

Please help me
Post #1374653
Posted Friday, October 19, 2012 2:19 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
The stored procedure should fetch record by record from temp split and insert into employee and employee_details table.


Why do you need to do it row by row? What's the logic you're applying that can't be dealt with a set based operation?


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1374680
Posted Friday, October 19, 2012 2:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 3,115, Visits: 3,241
Hi,

Try this worked fine on my local database.


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





Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1374684
Posted Friday, October 19, 2012 2:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 3,115, Visits: 3,241
Solution with set-based statement

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

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1374686
Posted Friday, October 19, 2012 4:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55
Thanks a lot Igor....
And here in the script the values are hardcoded.
How can i fetch the values from temp table and then so the splitting?
Post #1374731
Posted Friday, October 19, 2012 5:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 3,115, Visits: 3,241
arthi.anan (10/19/2012)
Thanks a lot Igor....
And here in the script the values are hardcoded.
How can i fetch the values from temp table and then so the splitting?


You have your table with data. Remove the hard-coded lines. I've inserted them just for testing.
Of course your table has many more columns, you have to add them also.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1374749
Posted Wednesday, October 24, 2012 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55
Thanks a lot...
I got it right..

Here in this procedure i have a problem with date formats. In my data the format is DD-MM-YYYY (13-Aug-1951)
I should declare in my temp table as a string and using date convert i should be able to match it to the date format in employees table...

How do i use a convert function in this procedure and where should i declare it?
Post #1376336
Posted Wednesday, October 24, 2012 1:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 3,115, Visits: 3,241
arthi.anan (10/24/2012)
Thanks a lot...
I got it right..

Here in this procedure i have a problem with date formats. In my data the format is DD-MM-YYYY (13-Aug-1951)
I should declare in my temp table as a string and using date convert i should be able to match it to the date format in employees table...

How do i use a convert function in this procedure and where should i declare it?


You should use style 105 to convert dd-mm-yyyy string to date. I'm posting you an example.

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

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1376342
Posted Wednesday, October 24, 2012 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55
I need to convert dt_of_birth [varchar] (15) which is in the format DD-Mon-YYYY to DD/MM/YYYY
So how do i give this in the stored procedure?
Post #1376382
Posted Wednesday, October 24, 2012 3:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 3,115, Visits: 3,241
arthi.anan (10/24/2012)
I need to convert dt_of_birth [varchar] (15) which is in the format DD-Mon-YYYY to DD/MM/YYYY
So how do i give this in the stored procedure?


You can use the Date and time styles section on following link of msdn
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1376388
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse