Insert data in two relational tables simultaneously SQL Server

Question

I have two tables, first is accounts and second is client. When client signs up, then I have to insert his email and password into accounts table and his other information into client table with newly created foreign key of accounts table. Why is my this query is not running?

INSERT INTO client (account_id, name, horoscope, credit, receive_email) 
VALUES 
    (INSERT INTO accounts(email, password, user_type) 
     OUTPUT inserted.id 
     VALUES ('test@example.com', 12, 0), 
   'name', 'Libra', 0.0, 0);

What can I do in this scenario to insert data in two tables with single query?

Why isn't it running? You tell us: _what error do you get_. Never ask a question without actually explaining what you're seeing — Nick.Mc, Feb 04 '18 at 06:19
This link maybe help you: [insert-into-multiple-tables-database-normalization][1] [1]:https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — z.e, Feb 04 '18 at 06:26

PSK · Answer 1 · 2018-02-04T07:18:22.373

SQL Query you have written is not correct, you need to split it into two statements like following assuming that account_id is an identity column.

INSERT INTO [accounts] 
            (email, 
             password, 
             user_type) 
VALUES      ( 'test@example.com', 
              12, 
              0 ) 

INSERT INTO client 
            (account_id, 
             NAME, 
             horoscope, 
             credit, 
             receive_email) 
VALUES      ( SCOPE_IDENTITY(), 
              'name', 
              'Libra', 
              0.0, 
              0 )

In case account_Id is not Identity column you can write it as following (Assuming that email id in account table is unique).

INSERT INTO [accounts] 
            (email, 
             password, 
             user_type) 
VALUES      ( 'test@example.com', 
              12, 
              0 ) 

    INSERT INTO client 
                (account_id, 
                 NAME, 
                 horoscope, 
                 credit, 
                 receive_email) 
    SELECT TOP 1 account_id, 
                 'name', 
                 'Libra', 
                 0.0, 
                 0 
    FROM   [accounts] 
    WHERE  email = 'test@example.com'

Note: You also need to take care of transactions as two inserts are involved in this case. If you are not handling this from fronted, you need to take care of this at database level. To know more about transaction in SQL server visit MSDN

I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else (like `@@IDENTITY`) to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) — marc_s, Feb 04 '18 at 07:16

score 1 · Answer 2 · edited Feb 04 '18 at 08:15

1

I think you can use scope_identity() to get newly inserted ID:

DECLARE @ID INT 

INSERT INTO accounts 
( 
            email, 
            password, 
            user_type 
) 

SELECT @ID = scope_identity()

INSERT INTO client 
( 
    account_id, 
    name, 
    horoscope, 
    credit, 
    receive_email 
) 
VALUES 
(
    @ID,  
    'name', 
    'Libra', 
    0.0, 
    ''
);

edited Feb 04 '18 at 08:15

marc_s

732,580
175
1,330
1,459

answered Feb 04 '18 at 06:23

D-Shih

44,943
6
31
51

Insert data in two relational tables simultaneously SQL Server

2 Answers2