to generate a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

to generate a stored procedure

Author

Topic

arthiasha
Starting Member

40 Posts

Posted - 2012-10-17 : 06:30:20

Hi experts,
I have a scenario like i have a temp table which holds some columns from employee table and some columns from employee_details table. temp is a combination of both.
I need to write a store procedure using cursor fetch record by record from .csv to insert 100 records into temp table and split the columns. those columns matching in employee table, the values should be stored there and as with the remaining columns should be stored in employee_details table.
please help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-17 : 06:36:23

Post the code that you have (even if it is not working exactly right). You may not need to and should try to avoid using a cursor if at all possible. Instead, try to use set based queries - they can be simpler and more efficient in almost every case.

arthiasha
Starting Member

40 Posts

Posted - 2012-10-17 : 08:20:10

Okay...
the first step am trying to do is, i have a .csv file and i should bcp in the record into the temp table.
can u give me the bcp script to import data from .csv file to the table in my sql server?

arthiasha
Starting Member

40 Posts

Posted - 2012-10-18 : 04:59:55

This is my script. Please help me to fetch row by row from the temp table and put it into employees table and employee_details table

SET NOCOUNT ON
DECLARE @employee_id char(7),@first_name char(50),
@middle_name char(50),@last_name char(50),
@title char(5),@sex char(1),
@dt_of_birth datetime,@dt_of_joining datetime,
@father_name char(40), @perm_address_line_1 char(200),
@perm_address_line_2 char(200), @per_city char(20),
@perm_state char(12), @perm_pincode int,
@perm_phone_landline char(15), @perm_phone_mobile char(15)

DECLARE cur_emp CURSOR
FOR
select * from temp
OPEN cur_emp

Begin
Fetch next from cur_emp into @employee_id,@first_name,@middle_name,@last_name,@title,@sex,@dt_of_birth,@dt_of_joining,@father_name,@perm_address_line_1,@perm_address_line_2,@per_city,@perm_state,@perm_pincode,@perm_phone_landline,@perm_phone_mobile

WHILE @@FETCH_STATUS = 0
BEGIN

While @@ROWCOUNT ! = 0
Begin
insert into employee_details values('E003',2,'Mercy','Jen','Ms','F','Assistant','F','1989-09-09','20120-04-04', 'Hes','3rdstreet','avenue','Delhi','Delhi',234567,8334223,9034342211
fetch next from cur_emp into @employee_id,@first_name,@middle_name,@last_name,@title,@sex,@dt_of_birth,
@dt_of_joining,@father_name,@perm_address_line_1,@perm_address_line_2,@per_city,@perm_state,@perm_pincode,@perm_phone_landline,@perm_phone_mobile
End
End
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 07:18:42

To bcp in, what you would do is something like this:


bcp YourDatabase.dbo.YourTempStagingTable in C:\Temp\YourCSVFile.csv -T -c

You run bcp command from a command window (or use xp_cmdshell if you want to run it from SQL). You may also need to provide a server name if you are running the command from a different computer than where the SQL Server is installed. This page has more details on options you could use: http://msdn.microsoft.com/en-us/library/ms162802.aspx

As for the code that you posted - I didn't quite follow what you are trying to do. Are employees data and employee_details data on separate rows? Can you post sample data from your temp table and the DDL for the two tables?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-18 : 09:31:09

Follow the link in my signature for help in posting the information we need to help you.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

arthiasha
Starting Member

40 Posts

Posted - 2012-10-19 : 01:38:38

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-19 : 10:51:32

[code]
-- insert new employees
INSERT INTO employee_table
([employee_id],[first_name] ,[middle_name],[last_name]
,[title],[sex],[dt_of_birth],[dt_of_joining],[father_name]
,[perm_address_line_1],[perm_address_line_2],[perm_city]
,[perm_state] ,[perm_pincode] ,[perm_phone_landline]
,[perm_phone_mobile] ,[present_address_line_1],[present_address_line_2]
,[present_city] ,[present_state] ,[present_pincode] ,[marital_status]
,[dt_of_marriage] ,[qualification] ,[blood_group] ,[email_id]
,[workex_current] ,[workex_past] ,[emergency_contact_name]
,[emergency_contact_telnon] ,[dept_code] ,[div_code]
,[branch_code] ,[designation]
)
SELECT
[employee_id],[first_name] ,[middle_name],[last_name]
,[title],[sex],[dt_of_birth],[dt_of_joining],[father_name]
,[perm_address_line_1],[perm_address_line_2],[perm_city]
,[perm_state] ,[perm_pincode] ,[perm_phone_landline]
,[perm_phone_mobile] ,[present_address_line_1],[present_address_line_2]
,[present_city] ,[present_state] ,[present_pincode] ,[marital_status]
,[dt_of_marriage] ,[qualification] ,[blood_group] ,[email_id]
,[workex_current] ,[workex_past] ,[emergency_contact_name]
,[emergency_contact_telnon] ,[dept_code] ,[div_code]
,[branch_code] ,[designation]
FROM
dbo.temp
-- insert their details

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
dbo.temp
[/code]
I am assuming that your details table has employee_id as the foreign key.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources