SQL Server: Is it possible to insert into two tables at the same time?

by

In one statement: No.

In one transaction: Yes

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client application with one sql string in a single function call as if it were one statement. You could also apply a trigger to one table to get the effect of a single insert. However, it’s ultimately still two statements and you probably don’t want to run the trigger for every insert.

More Related Contents:

  1. Solutions for INSERT OR UPDATE on SQL Server
  2. Inserting multiple rows in a single SQL query? [duplicate]
  3. Exporting data In SQL Server as INSERT INTO
  4. How can I INSERT data into two tables simultaneously in SQL Server?
  5. How to retrieve field names from temporary table (SQL Server 2008)
  6. How to insert Arabic characters into SQL database?
  7. Convert text to system date format [duplicate]
  8. How to get a particular preceding value of a column in SQL [closed]
  9. How to select just the row in the second table?
  10. How do I obtain a Query Execution Plan in SQL Server?
  11. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  12. Remove Identity from a column in a table
  13. efficient way to implement paging
  14. SQL Server – transactions roll back on error?
  15. T-SQL Cast versus Convert
  16. SQL Case Sensitive String Compare
  17. convert Excel Date Serial Number to Regular Date
  18. Convert Comma Separated column value to rows
  19. Partition Function COUNT() OVER possible using DISTINCT
  20. INSERT rows into multiple tables in a single query, selecting from an involved table
  21. What does sp_reset_connection do?
  22. Replace first occurrence of substring in a string in SQL
  23. How to correctly insert newline in nvarchar [duplicate]
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. SQL Server SELECT INTO @variable?
  27. Select a column if other column is null
  28. How to subtract 30 days from the current date using SQL Server
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment