Insert in More than one tabels

Question

Hi Dudes is it possible can i update insert in Sql Server 2005 more than one tables in one query...................? if it is possible can any one help me...?

Zoran Horvat · Answer

T-SQL does not support insertion into multiple tables with one INSERT statement. It is possible to insert into view but only if that would end up with inserting data into exactly one table. Any view consisting of columns from multiple tables is not updateable in SQL Server. However you can update data from views that join multiple tables. For example consider these tables and a view that joins them: CREATE TABLE A ( AID INT PRIMARY KEY IDENTITY(1 1) AName VARCHAR(100) ); CREATE TABLE B ( BID INT PRIMARY KEY IDENTITY(1 1) AID INT FOREIGN KEY REFERENCES A(AID) BName VARCHAR(100) ); CREATE VIEW V AS SELECT A.AID AName BID BName FROM A INNER JOIN B ON A.AID=B.AID; Here if you insert data like this: INSERT INTO A(AName) VALUES ('One'); INSERT INTO B(AID BName) VALUES (1 'Two'); You can update values in B row like this: UPDATE V SET BName='Updated' WHERE AID=1; This will work because source of the BName column is known and it can be updated by this statement. At the same time INSERT fails on the same view: INSERT INTO V(AName BName) VALUES ('One' 'Two'); This statement would require SQL Server to determine AID which was created by inserting row into A table and then to use that value as a foreign key to B table. However this statement doesn't contain sufficient information for SQL Server to understand that it is exactly the one value of AID that should be inserted into B table hence such statement is not legal. Zoran

Vulpes · Answer

To the best of my knowledge you can't insert into more than one table at a time using a single statement. However you could use a transaction to do multiple insertions and then roll it back if not all of them succeed.

Suthish Nair · Answer

Sorry you need to explain the thread with more information..