0

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!

Fernando LimnCaducado Snchez M
  • 1
  • Where is the data coming _from_? Regardless of how you code the loop / batch insert, the values which _are_ different need to be entered somewhere. If that's a spreadsheet, for instance, you can use a spreadsheet formula to generate SQL for you; if that's somewhere else in the database, you shouldn't be able to write SQL to transform the results without handling the individual rows; if that's some other file format, a script in PHP/Python/Perl/whatever would probably be the way to go. – IMSoP Sep 14 '22 at 10:35
  • Generate fake data in sequence. If it is text then fix text plus generated number in cte can help: https://saveriomiroddi.github.io/Generating-sequences-ranges-via-mysql-8.0-ctes/ – Meyssam Toluie Sep 14 '22 at 10:35
  • Ah sorry, I should've mentioned that. Yes, the two values that need to change are coming from the database. They are values from two columns of the same table if that makes any difference. – Fernando LimnCaducado Snchez M Sep 14 '22 at 10:38
  • Square brackets which encloses the column names are specific for SQL Server (MS SQL) and are wrong in MySQL. Check your DBMS carefully.. – Akina Sep 14 '22 at 10:40
  • I only used squared brackets to try and denote those were placeholder names, sorry. I don't know anything about SQL so I didn't know they had actual meaning in another format. – Fernando LimnCaducado Snchez M Sep 14 '22 at 10:42

1 Answers1

0

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.

Anse
  • 1,573
  • 12
  • 27