sqlmysqlsqlsrv

insert 80 data records into your SQL database with the same ID number

I'd like to make a sql table something like

enter image description herethis.

I want to insert 80 data records in my SQL databse with the same ID number.

How can i overcome this sql database?

Solution

  • How about a row generator?

    Sample table:

    CREATE TABLE IF NOT EXISTS mytable (
        ID INT,
        SN VARCHAR(20),
        RESULT1 VARCHAR(2),
        RESULT2 VARCHAR(2),
        RESULT3 VARCHAR(2)
    );

    INSERT statement:

    INSERT INTO mytable (ID, SN, RESULT1, RESULT2, RESULT3) 
WITH RECURSIVE seq AS 
  (SELECT 'I' result, 0 AS value 
   UNION ALL 
   SELECT 'I' result, value + 1 FROM seq LIMIT 80
  )
SELECT 1, 'PKR123456789', result, result, result FROM seq;

    Check what's being inserted:

    SELECT * FROM mytable;

    Here's a db<>fiddle session, if you want to have a quick look.