How do I insert data only if it matches a certain condition from one table to another table in Rails?

Question

I'm somewhat of a begginer to SQL and migrations in general, so it's possible my approach is completely wrong here, but bear with me ;)

I have a Company model that has attributes for SMTP mailing configuration (like smtp_address and smtp_port), which are optional.

However, I now need my User model to be able to have an SMTP configuration as well, so I created a SMTPConfiguration model with attributes address, port, and a polymorphic association to smtpable. This model will belong either to the User or Company model

What I'd like to do now is migrate the data I already have in the companies table over to the SMTP configurations table, which sounds pretty straightforward, except I want to create a new SMTPConfiguration ONLY if the Company actually has the smtp_address and smtp_port set. If smtp_address and smtp_port are not present, the Company should not have a belonging SMTPConfiguration.

This would be fairly easy to do using Ruby conditions and ActiveRecord, but from what I've read inserting rows individually with ActiveRecord can be reaaally long for large databases, so it is a much better idea to use raw SQL. Turns out I suck at SQL :( .

I've searched on StackOverFlow quite a bit, and using this and this, I came up with something like

execute <<-SQL
  INSERT INTO smtp_configurations (address, port, smtpable_id)
  SELECT (c.smtp_address, c.smtp_port, c.id)
  FROM companies c
  WHERE c.smtp_address <> NULL
SQL

This wouldn't work since it doesn't set created_at, updated_at and smtpable_type, but to be honest I'm out of ideas. Could the activerecord-import gem be of any use?

I am using Rails 5.1.4 and Postgres 10.3

Answer 1

Try next code in your migration:

created_at = Time.current.to_s(:db)
insert_clause = <<-SQL
  INSERT INTO smtp_configurations(
    smtpable_id,
    smtpable_type,
    address,
    port,
    created_at,
    updated_at
  )
  SELECT
    c.id,
    'Company',
    c.smtp_address,
    c.smtp_port,
    '#{created_at}',
    '#{created_at}'
  FROM companies c
  WHERE (c.smtp_address IS NOT NULL) AND (c.port IS NOT NULL)
SQL

execute(insert_clause)

UPDATED: Changed code for plain Rails.