INSERT with the same data in different tables

Question

I have two tables, one called PROJETO (id_projeto, id_tipo_projeto, descr_projeto, versao_projeto) and another called TIPO_PROJETO (id_tipo_projeto, descricao_tipo)

I would like to perform an INSERT on the two tables at the same time, so that the id_tipo_projeto field in both tables have the same value.

This is an example of the data to store in the tables...

Table PROJETO
--------------------------------------------------------------
| id_projeto        = 1                                      |
| **id_tipo_projeto = 5                                      |
| descr_projeto     = 'design will be sought for the sector' |
| versao_projeto    = 1.0                                    |
--------------------------------------------------------------

Table TIPO_PROJETO
-------------------------------------------
| **id_tipo_projeto = 5                   |
| descricao_tipo    = 'Industrial Design' |
-------------------------------------------

Could someone please help me to write such a query. I am using SQL Server.

score 1 · Accepted Answer · answered Apr 05 '11 at 00:17

This would be better done sequentially, within a transaction. It is not practical to hope to insert at precisely the same time.

DECLARE @id INT

BEGIN TRANS

    INSERT INTO [PROJETO]
        (id_projeto, descr_projeto, versao_projeto)
    VALUES
        (1,'design ... etc', 1.0)

    SET @id = SCOPE_IDENTITY()

    INSERT INTO [TIPO_PROJETO]
        (id_tipo_projeto, descricao_tipo)
    VALUES
        (@id, 'Idustrial Design')

    IF @@ERROR <> 0
        ROLLBACK TRANS

COMMIT TRANS

The first table should have an primary key identity column, the second just a primary key, which will be inserted based on the first insert's ID using SCOPE_IDENTITY.

If the second insert errors out, the transaction will be rolled back. This is just precaution and you could add error handling to the if/else statement to report that the inserts failed.

What about the field id_tipo_projeto table PROJECT? In your example code it no longer appears. — Phillip Nunes, Apr 05 '11 at 13:40

score 0 · Answer 2 · edited May 23 '17 at 11:47

0

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

edited May 23 '17 at 11:47

Community

1
1

answered Apr 05 '11 at 00:13

dgilland

2,758
1
23
17

INSERT with the same data in different tables

2 Answers2