i'm working with project VB6-SQL Server 2000
i have on Database Two Tables for Employers
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 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
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.