Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: Stored procedure to split values and insert into tables

    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

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    What is the problem? If you want to mediate this through a cursor, you will need to instruct your SP to insert some values from it into employees, and some into employee details.

    You don't need a cursor to do this - it would be easier simply to write two INSERT INTO statements based on the temporary table, and split the data that way.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •