Insert a lot of data to a table, with only 1 field different per row

Question

INSERT INTO Date_Pub1 (Tranche, AssessmentId, Date_Published, Datepub)
VALUES
 ('Tranche 26','14010','2019-03-08 00:00:00.000','08 March 2019'),
 ('Tranche 26','14009','2019-03-08 00:00:00.000','08 March 2019'),
 ('Tranche 26','13648','2019-03-08 00:00:00.000','08 March 2019'),
 ('Tranche 26','14026','2019-03-08 00:00:00.000','08 March 2019');

How to make this simpler as you can see only AssessmentID changes

Answer 1

One dummy solution is change your table's default values for columns.

ALTER TABLE Date_Pub1 
ADD DEFAULT('Tranche 26') FOR Tranche
# same to the 2 other columns

Then, you can insert your records like this,

INSERT INTO Date_Pub1 (AssessmentId)
VALUES('14389')

You can get more from this post, How to set a default value for an existing column

Answer 2

This looks like a statement that's being written literally, for a one-off execution.

One way to make this simpler: Generate the rows programmatically, instead of writing them literally. For example, in Python:

assessment_ids = ['14010', '14009', '13648', '14026']
sql_statement = """\
    INSERT INTO date_pub1 (tranche, assessmentid, date_published, datepub)
    VALUES
    """ + ",\n    ".join(
        "('Tranche 26', '{}', '2019-03-08 00:00:00.000', '08 March 2019')".format(assessment_id)
        for assessment_id in assessment_ids
    ) + ";"
print(sql_statement)

produces:

INSERT INTO date_pub1 (tranche, assessmentid, date_published, datepub)
VALUES
('Tranche 26', '14010', '2019-03-08 00:00:00.000', '08 March 2019'),
('Tranche 26', '14009', '2019-03-08 00:00:00.000', '08 March 2019'),
('Tranche 26', '13648', '2019-03-08 00:00:00.000', '08 March 2019'),
('Tranche 26', '14026', '2019-03-08 00:00:00.000', '08 March 2019');