SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure to split and insert values into tables


Stored procedure to split and insert values into tables

Author
Message
arthi.anan
arthi.anan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3175 Visits: 3325
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
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10741 Visits: 5158
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,
My blog: www.igormicev.com
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10741 Visits: 5158
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,
My blog: www.igormicev.com
arthi.anan
arthi.anan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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?
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10741 Visits: 5158
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,
My blog: www.igormicev.com
arthi.anan
arthi.anan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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?
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10741 Visits: 5158
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,
My blog: www.igormicev.com
arthi.anan
arthi.anan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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?
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10741 Visits: 5158
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,
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search