SQL Insert into from Multiple Tables

SQL INSERT INTO from multiple tables

You only need one INSERT:

INSERT INTO destinationTable( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, s1.id, number, s3.nationality
FROM sourceTable1 s1
INNER JOIN sourceTable2 s2 ON s2.Id = s1.Id
INNER JOIN sourceTable3 s3 ON s3.Id = s1.Id

SQL Server : insert into two tables with case

You'll need to do a couple of things to make this work. For the first part of your stored procedure you'll need to insert into the tblProduct table, then store the new ProductCode in a variable. I'm assuming that ProductCode is generated via trigger or computed column. If you're not automatically generating that, the best way to do that is by creating a computed column.

Below you create your procedure with the ProductName and ProductPrice as parameters, insert them into your tblProduct, and store the new ProductCode into a @NewProductCode temp table.

CREATE PROCEDURE dbo.InsertProduct @ProductName nvarchar(255), @ProductPrice decimal(19,4)
AS
    DECLARE @NewProductCodeTempTable table (ID int)

    INSERT INTO tblProduct (ProductName, ProductPrize) 
    OUTPUT INTO INSERTED.ProductCode @NewProductCode
    VALUES (@ProductName, @ProductPrice)

Next you'll use an IF/ELSE statement to determine whether you need to insert one, or 4 records into your tblPriceMST table.

    DECLARE @NewProductCode = SELECT ID FROM @NewProductCodeTempTable

    IF (@ProductPrice < 300)   
        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-01', @ProductPrice, 1, 'KG')
    ELSE 
    BEGIN
        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-01', @ProductPrice, 1, 'KG')

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-02', @ProductPrice*.75, 750, 'GMS')   

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-03', @ProductPrice*.5, 500, 'GMS')  

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-04', @ProductPrice*.25, 250, 'GMS')            
    END
GO

I've inferred what your rules are for inserting records for the different product weights if the price is > 300. But if you have more rules, you would add additional IF/THEN or CASE statements in the ELSE block here.

There may be a much easier way to do this if you simplify your table design but without knowing all of your requirements it would be hard to say what it should look like.

SQL Server : INSERT INTO from multiple tables without having any relationship between them

Use cross join to generate the data:

select t1.name, t2.number
from t1 cross join t2;

You can use insert or select into to put the results into another table:

insert into t3(name, number)
    select t1.name, t2.number
    from t1 cross join t2;

Ironically, I can't think of a reasonable way of doing this with exactly two queries.

Best way to insert into multiple tables in a cluster

The INSERT ALL statement can insert into multiple tables in a single statement, but I don't think it can be used to insert data selected from another table.

Yes it can. There are examples of that in the documentation. Here you can do something like:

insert all
into customer_name (customer_id, first_name, last_name)
            values (customer_id, first_name, last_name)
into customer_dob (customer_id, dob)
           values (customer_id, dob)
select customer_id, first_name, last_name, dob
from customer;

Demo using a cut-down version of your table and one dummy row:

create table customer (
  customer_id number(38),
  first_name varchar2(20),
  last_name varchar2(20),
  dob date
);

insert into customer (customer_id, first_name, last_name, dob)
values (42, 'Alex', 'Poole', date '1972-01-01');

and a cluster with two tables (though the cluster isn't really relevant):

create cluster customer_cluster (customer_id number(38));

create index customer_cluster_idx on cluster customer_cluster;

create table customer_name (
  customer_id number(38),
  first_name varchar2(20),
  last_name varchar2(20)
)
cluster customer_cluster (customer_id);

create table customer_dob (
  customer_id number(38),
  dob date
)
cluster customer_cluster (customer_id);

then you can do:

insert all
into customer_name (customer_id, first_name, last_name)
            values (customer_id, first_name, last_name)
into customer_dob (customer_id, dob)
           values (customer_id, dob)
select customer_id, first_name, last_name, dob
from customer;

2 rows inserted.

select * from customer_name;

CUSTOMER_ID FIRST_NAME           LAST_NAME           
----------- -------------------- --------------------
         42 Alex                 Poole               

select * from customer_dob;

CUSTOMER_ID DOB       
----------- ----------
         42 1972-01-01

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

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...

How to INSERT into multiple tables from one SELECT statement

You can use the OUTPUT clause to insert into the second table. If you want to insert into more than two tables, you'd have to use some other method.

Sample data

DECLARE @MainTable TABLE (Name nvarchar(50), Address nvarchar(50));
DECLARE @T1 TABLE (Name nvarchar(50), Address nvarchar(50));
DECLARE @T2 TABLE (Name nvarchar(50), Address nvarchar(50));

INSERT INTO @MainTable (Name, Address) VALUES
('Name1', 'Address1'),
('Name2', 'Address2'),
('Name3', 'Address3');

Query

INSERT INTO @T1 (Name, Address)
OUTPUT inserted.Name, inserted.Address INTO @T2 (Name, Address)
SELECT Name, Address
FROM @MainTable
;

Result

SELECT * FROM @T1;
SELECT * FROM @T2;

+-------+----------+
| Name  | Address  |
+-------+----------+
| Name1 | Address1 |
| Name2 | Address2 |
| Name3 | Address3 |
+-------+----------+

+-------+----------+
| Name  | Address  |
+-------+----------+
| Name1 | Address1 |
| Name2 | Address2 |
| Name3 | Address3 |
+-------+----------+

Execution plan

---

Related Topics