Bulk/duplicated Insert Of Data?

[bkelley]

Hi SQLyog … Happy Holidays!

I am working with 50 MySQL databases, and I have a need to insert some state-specific data into the same table (employees) in each database. Each database is for one of the 50 US states. Is there a way via SQLyog that I can do a “bulk insert” into all of the tables? I have learned how to use the “import CSV data function”, and I could create 50 different scripts for that (each targeted at a different database), but that would be pretty tedious and I was wondering if there was another more efficient method. Each state database has a specific name with the state abbreviation in the name “e.g., caxxworkforce”; and the name of the table in all of the databases is the same ('employees'). I need to insert about 1000 records into each employees table for each state; one of the fields is an autoincrement field ('id').

I can provide more information if that is useful to define this task.

Thoughts?

best,

Brian

[peterlaursen]

I understand that the data to be inserted in each of the 50 databases/tables are not the same? 

What I can think of is using a scripting system to call SJA with different parameters and also call Windows system commands (copy file, rename file etc.).  Such scripting system could be 'Windows Powershell' (Vista/2008/7) or 'Windows Scripting host' (2K/2003/XP) – or just VB or a .NET language of course.

Did you consider putting all data into one table and just add one column identifying the state? That would be easier, I believe. But also generating one jobfile and generating 50 new using 'search and replace' will probably not take much time). Next generate a batch file of 50 jobs and execute the batch file.

BTW: has US now 'shrunk' to 50 states? My personal opinion: keep it going! 🙂 

(but not sure if we would be happy to get Nebraska back as a Danish province – it almost was 110 years ago!) 🙂 

[Author]

Posts