Click here to monitor SSC
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
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 4849
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
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 4849
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
arthi.anan
arthi.anan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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?
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 4849
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
arthi.anan
arthi.anan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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?
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 4849
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
arthi.anan
arthi.anan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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?
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 4849
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
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