How to insert data in multiple tables using single query in SQL Server?

Question

I'm trying to insert data into multiple tables if it doesn't already exist. I can't seem to figure this out at all.

Table 1:

CREATE TABLE [dbo].[search_results]
(
    [company_id] [int] NULL,
    [title] [text] NULL,
    [link] [text] NULL,
    [domain] [text] NULL,
    [index] [int] NULL,
    [id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL
)

Table 2:

CREATE TABLE [dbo].[statements]
(
    [statement_link_id] [int] NULL,
    [statement_page] [text] NULL,
    [statement_text_location] [text] NULL,
    [statement_description] [text] NULL,
    [statement_description_html] [text] NULL,
    [statement] [int] NULL,
    [id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL
)

This is what I want to do:

check to see if the company_id and the link already exist in the table or not.

SELECT * 
FROM search_results 
WHERE company_id = 4 AND link = 'https://test.com';

If the data does not exist, insert it into two tables

INSERT INTO search_results (company_id, link, title, domain)   
VALUES (4, 'https://test.com', 'title', 'test.com');

and also insert the search_result last inserted id to the following table. corporate_statement value is always 1

INSERT INTO corporate_statements (statement_link_id, corporate_statement) 
VALUES (743, 1);

I'm trying this based on what I found on SO

DECLARE @result AS TABLE (id int, company_id int, link text, title text, domain text);

WITH cte AS
(
    SELECT *
    FROM (VALUES (4, 'https://test.com', null, null)) AS t(company_id, link, title, domain)
)
INSERT INTO @result 
    SELECT * 
    FROM
        (INSERT INTO dbo.search_results (company_id, link, title, domain)
         OUTPUT inserted.*
             SELECT * FROM cte 
             WHERE NOT EXISTS (SELECT * FROM dbo.[search_results] 
                               WHERE company_id = cte.company_id 
                                 AND CAST(link AS varchar(250)) = CAST(cte.link AS varchar(50))
         )) r

SELECT * FROM @result;

Even trying with a single insert statement, I get the following error:

Msg 213, Level 16, State 1, Line 8
Column name or number of supplied values does not match table definition.

As you can see, I also tried to cast it to varchar since it was throwing error when I hadn't. How can update this?

Answer 1

To me - this seems a lot cleaner, and it also will be a lot simpler to understand (and maintain!) in the future:

-- check to see if your data already exists
IF NOT EXISTS (SELECT * 
               FROM search_results 
               WHERE company_id = 4 AND link = 'https://test.com')
BEGIN TRY
  BEGIN TRANSACTION
    -- if not -> insert into the first table
    INSERT INTO search_results (company_id, link, title, domain)   
    VALUES (4, 'https://test.com', 'title', 'test.com');

    -- grab the last identity value from that previous INSERT       
    DECLARE @LastId INT;
    
    SELECT @LastId = SCOPE_IDENTITY();
    
    -- insert into the second table
    INSERT INTO corporate_statements (statement_link_id, corporate_statement) 
    VALUES (@LastId, 1);

    COMMIT;
END TRY
BEGIN CATCH
    -- in case of an error rollback the full transaction
    ROLLBACK;
END CATCH;

and you're done. Or am I missing something? I think this would be doing what you're described in the intro of your post - not necessarily what you're showing in your code...