mysqlsqlmultiple-value

Set multiple values in SQL ***make my job easier***


I use the following SQL query in work which works fine, however sometimes I need to update 1000+ values, and doing so one at a time is a laborious process, is there a way to be able to input multiple values at once, in this instance I have names of products and I need to set a name and section to them? Thanks in advance

use [Product.Extract] 
Begin TRANSACTION

Declare @name as varchar(255)
Declare @year as int
Declare @sectionid as int
-- Set variables
set @name = '225mm insulated efe'
set @year = 2021
set @sectionid = 1
--see original values
SELECT *
FROM Normalisation.productmap
where term = @name
-- Insert values
INSERT INTO Normalisation.productmap(name, year, sectionid)
VALUES(@name,
@year,
@sectionid)

Solution

  • You can use a single insert statement with multiple rows:

    INSERT INTO Normalisation.productmap (name, year, sectionid)
        VALUES ('name1', 2020, 1),
               ('name1', 2020, 2),
               ('name1', 2020, 3),
               . . . ;
    

    If the values come from a table, you can use an insert . . . select:

    INSERT INTO Normalisation.productmap (name, year, sectionid)
        SELECT name, year, sectionid
        FROM temp_table;
    

    If the values come from an external file, then you can use LOAD DATA INFILE.