CODEWITHSUNDEEP
sqlsql-server
gin93r
gin93r

Reputation: 1593

Can I use OUTPUT INTO to add data to a relational table with additional values?

I have two tables. One holds common data for articles, and the other holds translations for text. Something like this:

Articles Table

id | key | date

Translations Table

id | article_key | lang | title | content

key is a string and is the primary key.

article_key is a foreign key relating it to articles on the key column.

When I add a new row to the Articles, I'd like to be able to use the key that was just inserted and add a new row to the Translations Table.

I've read about OUTPUT INTO but it doesn't seem like I can add other values to the Translations table. Also I get an error about it being on either side of a relationship.

Is my only course of action to INSERT into Articles followed by an INSERT with a SELECT subquery to get the key?

Edit: Expected output would be something like:

Articles

id | key | date
---------------
1  | somekey | 2018-05-31

Article Translations

id | article_key | lang | title | content
-----------------------------------------
1  | somekey     | en   | lorem | ipsum

Upvotes: 0

Views: 56

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Well this could work based on your description:

SET NOCOUNT ON;

DECLARE @Articles TABLE (id INT NOT NULL
  , [key] VARCHAR(50) NOT NULL
  , [date] DATE NOT NULL);

DECLARE @ArticleTranslations TABLE (id INT NOT NULL
  , article_key VARCHAR(50) NOT NULL
  , lang VARCHAR(50) NOT NULL
  , title VARCHAR(50) NOT NULL
  , content VARCHAR(50) NOT NULL);

INSERT @Articles (id, [key], [date]) -- This is insert into @Articles
OUTPUT INSERTED.id, INSERTED.[key], 'en', 'lorem', 'ipsum' -- This is insert into @ArticleTranslations
INTO @ArticleTranslations (id, article_key, lang, title, content) -- This is insert into @ArticleTranslations
VALUES (1, 'somekey', GETDATE()); -- This is insert into @Articles

SELECT *
FROM @Articles;

SELECT *
FROM @ArticleTranslations;

Try this out Stack Exchange: https://data.stackexchange.com/stackoverflow/query/857925

Maybe it's not that simple as it is. So let me know whether this works or not.

Upvotes: 1

Related Questions