October 19, 2012 at 12:21 am
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
October 19, 2012 at 2:19 am
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[/url]
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
October 19, 2012 at 2:36 am
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
October 19, 2012 at 2:41 am
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
October 19, 2012 at 4:49 am
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?
October 19, 2012 at 5:31 am
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
October 24, 2012 at 1:06 am
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?
October 24, 2012 at 1:47 am
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
October 24, 2012 at 3:32 am
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?
October 24, 2012 at 3:42 am
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/YYYYSo 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
October 24, 2012 at 3:51 am
declare @var varchar(20)
set @var = '24-Oct-2012'
print @var
declare @date varchar(30)
set @date = convert(date,@var,105)
print @date
SET @date=RIGHT('0'+CAST(DAY(@date) AS VARCHAR(2)),2)+'/'+RIGHT('0'+CAST(MONTH(@date) AS VARCHAR(2)),2)+'/'+CAST(YEAR(@date) AS VARCHAR(4))
print @date
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 24, 2012 at 6:15 am
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/YYYYSo how do i give this in the stored procedure?
No... NO! You should save dates and times as one of the SQL Server date datatypes. You should never store dates in a table as a formatted date. You're just begging for a world of hurt further down the line. For example, they're impossible to directly sort correctly. They're impossible to calculate anything (like age) without a conversion. And, these conversions are implicit just when you don't want them to be which will make almost all of your queries run slower index scans instead of index seeks.
Do yourself a favor... never store date related data as char data.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2012 at 7:57 am
Do yourself a favor... never store date related data as char data.
+100000000000000
There are VERY few absolutes when it comes to sql server but "NEVER store related data as char data" is one of them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply