[2005] how to insert into 2 tables at once?

[nudge]

Hi,



I have two relational tables in my project, and I have to insert data into both of them at one,, or since the first table was filled,, the second should be too ..
for example I have a book table, and book info table ..
in the book I would have the ISBN, name .. etc ..
book info has FK (ISBN) and other info about the book such as:description, category .....

information about the book are in a text file, I want to extract the information
and have them into two tables with a FK (ISBN) between them ..


can I insert into these two tables at once??
if yes, how ?
if no , what should I do instead ?


thanx in advance

[wild_bill]

It would probably be easier to read through your file line by line, and add records to the appropriate tables.

[nudge]

reading and other things are ok .. they are working fine ..

but the problem is when inserting in two relational tables .. I got an error

[nudge]

emmmmmmmmmmm,, anyone ?!?!

[si_the_geek]

Please do not bump your threads.

You cannot insert two tables at once... you should insert into each table in turn.

If you want to ensure that the data is written to both tables (or nothing is written at all), but the Insert statements inside a transaction.

[nudge]

Please do not bump your threads.

You cannot insert two tables at once... you should insert into each table in turn.

If you want to ensure that the data is written to both tables (or nothing is written at all), but the Insert statements inside a transaction.

ok, sorry ..

what do you mean by "Insert statements inside a transaction" ..??

thank you

[si_the_geek]

A Transaction is basically a safety net.. normally when you run multiple SQL statements, an error in one of them means you are likely to have done part of the work, but not all of it - which is awkward to either 'undo', or finish off.

When you use a transaction, it puts all of your SQL statements into a group - and ensures that if one fails, they all 'undo'.


How you would implement a transaction depends on how you are working with the database, and possibly which database system you are using.

[mendhak]

Pass your variables to a stored procedure. Have your stored procedure insert into both tables within a BEGIN TRANSACTION...COMMIT TRANSACTION block.

http://msdn2.microsoft.com/en-us/library/ms190295.aspx

[nudge]

thank you si_the_geek for your help,

[nudge]

Pass your variables to a stored procedure. Have your stored procedure insert into both tables within a BEGIN TRANSACTION...COMMIT TRANSACTION block.

http://msdn2.microsoft.com/en-us/library/ms190295.aspx

thank you very much .. it really helps!
thanks for your understanding that am new to these things

[nudge]

Hi mendhak,

is it right this way ??

USE Customer;
GO
BEGIN TRANSACTION;
GO
Insert into customerInfo (id, name, age, gender) values (123, "John", 27, "F");
GO
Insert into customerEnroll (customerId, enrolmentDate) values (id, "22-02-2007");
GO
COMMIT TRANSACTION;
GO

[mendhak]

After your inserts...

		/* Error Handling Routine */
		SELECT @ErrorNo = @@ERROR
		IF @ErrorNo <> 0 
                  BEGIN
			ROLLBACK TRANSACTION
			GOTO PROC_RETURN
		END
                COMMIT TRANSACTION

At the end,

PROC_RETURN:

RETURN @ErrorFlag

[24sharon]

not sure I understand all but sum tips;

1. you can make store procedure in the SQL SERVER, that get the data
and insert them to the tables.

2. second option is make a trigger on insert,
and when you insert to one table it run the trigger and insert the dada to the second table

3.I think your tow table have to connect by value
if this value is the auto number you can get it after the insert statement
with writing "select scope_identity() "

4. if you want to insert the 2 table and if it fail on running to cancel
then make TRANSACTION

hope i help, and sorry on my english