CODEWITHSUNDEEP
sqlpostgresql
Detuned
Detuned

Reputation: 3768

Insert into a table after getting an ID from another table

Given these two example tables:

authors(
  id SERIAL PRIMARY KEY,
  name VARCHAR(60) UNIQUE,
  profession VARCHAR(30)
)

posts(
  id SERIAL PRIMARY KEY,
  text TEXT,
  author_id INT REFERENCES authors(id)
)

The authors table has all of the authors, but now that I'm trying to populate the posts table, I have a set of data that basically has a post with an author name, but I'd like to query the author_id so that I can insert into the quotes table as follows:

INSERT INTO posts(text, author_id) VALUES(
  /* 
     pseudo broken-code
     SELECT post, author FROM posts_temp (where all of the data temp resides),
     SELECT id FROM authors WHERE authors.name = author.name (obviously this is wrong, but the idea is getting the id from the authors table that matches the author name from the selection above)
  */

)

Upvotes: 5

Views: 8174

Answers (2)

Siva G
Siva G

Reputation: 98

DECLARE @InsertedIds TABLE (AID BIGINT) INSERT INTO authors (Id,name,profession) OUTPUT INSERTED.Id INTO @InsertedIds SELECT @Id,@name,@profession

INSERT INTO posts (id,text,author_id) SELECT @Id,@text,AID FROM @InsertedIds

Upvotes: -3

Patrick
Patrick

Reputation: 32384

An INSERT statement can use the rows returned by a SELECT statement as source for inserting data. So construct the appropriate SELECT statement from posts_temp and authors and then you are done:

INSERT INTO posts(text, author_id)
  SELECT pt.post, a.id
  FROM posts_temp pt
  JOIN authors a ON a.name = pt.author;

Upvotes: 9

Related Questions