SQL Loops to insert dummy data?

Fernando LimnCaducado Snchez M 0
Viewed 34

Part of my job requires that I insert hundreds of fields into a table each week, and I'm getting honestly tired of doing it by hand. SQL is not my forte, so I was wondering if there could be a way to do it semi-automatically?

The query I need to fulfill is:

insert into [table] ([c1][c2][c3][c4][c5][c6])
values([v1][v2][v3][v4][v5][v6]);

Only v1 and v2 need to change each loop, 3 to 6 are always the same value. Could I somehow make an array for the values of v1 and v2 and make it so the query repeats itself advancing through those arrays? Or something that would save me an hour of manually replacing hundreds of values?

Thanks!

heidisql mysql for-loop sql
1 Answers

MySQL supports a syntax for INSERT INTO ... SELECT ... queries, which may do everything you want in one query.

Example: assuming you have two columns which always get the same string value (col1 + col2) and two columns with values from somewhere else:

INSERT INTO target_table
  (col1, col2, col3, col4)
  SELECT 'foo', 'bar', id, price
  FROM source_table WHERE created_at > '2022-01-01';

That would insert the two static values "foo" and "bar" for each row, plus the values from id and price in source_table.

Anse0
Related