1

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)
Dobber2020
  • 23
  • 3

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Brilliant this works thank you, i work in sales and do not have an IT brain, I was given this task once a month to take some of the work from our busy data analysts – Dobber2020 Feb 22 '21 at 11:55