-
May 20th, 2010, 06:18 AM
#1
Thread Starter
Member
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 ??
-
May 20th, 2010, 07:13 AM
#2
Re: insert into Two Tables relation 1-1
You can't. It meeds to be two statements.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
May 20th, 2010, 07:31 AM
#3
Thread Starter
Member
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??
-
May 20th, 2010, 07:45 AM
#4
Re: insert into Two Tables relation 1-1
Originally Posted by elragal_30
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
-
May 20th, 2010, 07:49 AM
#5
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
-
May 20th, 2010, 05:22 PM
#6
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|