Results 1 to 6 of 6

Thread: insert into Two Tables relation 1-1

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2009
    Posts
    40

    Question insert into Two Tables relation 1-1

    hello all
    i'm working with project VB6-SQL Server 2000
    i have on Database Two Tables for Employers

    Table 1
    employer

    Table2
    Perm

    Table 1> for Employer data Fields -> E_ID,Phone,Email,Usrnm,Pass,....etc
    Table 2> for Permission Fields -> E_ID,Usr_Del,Usr_Edit,Sale_man,.....etc

    and Table 1 have Primary Key "E_id"
    i create relation 1 to 1 from employer.E_id to Perms.E_id

    the E_id in both tables is same (auto Identity (1,1))

    i want when i send Command to insert new Employer to the two tables in one sql command as th field E_ID is will give auto number and i want the same number in two tables for same employers
    so i can then select employer information and Permissions and can edit/delete him-her

    Note:the one emplyer will just have one id and one record

    so what is the SQl Command that can make that ??

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: insert into Two Tables relation 1-1

    You can't. It meeds to be two statements.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2009
    Posts
    40

    Re: insert into Two Tables relation 1-1

    so you have another solution for it ?? as two statements ??
    but must the employer get the same number(auto number) in tow tables??

  4. #4
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: insert into Two Tables relation 1-1

    Quote Originally Posted by elragal_30 View Post
    so you have another solution for it ?? as two statements ??
    but must the employer get the same number(auto number) in tow tables??
    The other solution is to write a stored procedure.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: insert into Two Tables relation 1-1

    You can't write 1 statement to insert into two tables. Period that is not supported in SQL. Now you can write one statement for the first insert into employer then return the just created identity and use that to insert into table 2.

    Or write an SP that does that for you.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6

    Thread Starter
    Member
    Join Date
    Mar 2009
    Posts
    40

    Re: insert into Two Tables relation 1-1

    a lot thanks for all your help
    ok i will try solutions you tell me
    and tell you tomorrow because

    Do Untill time(now) = 7 am
    sleep
    loop

Posting Permissions

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



Click Here to Expand Forum to Full Width