SQL queries to migrate data from cck_address (5.x) to location_cck (6.x)

If you are still on cck_address and don't mind getting your hands dirty with a bit of SQL .. a set of queries that will migrate your data from cck_address 5.x tables to location_cck 6.x tables in the same database. This is for use after upgrading a site from 5.x to 6.x, with the destination Location CCK field already created and the legacy cck_address table still existing in the same database.

In my example, I am using:

Source table: content_field_address (5.x cck_address field with machine name "field_address")
Destination table: content_field_location (6.x location_cck field with machine name "field_location")

Both fields were set to limit to 1 maximum value.

** Back up your database first! You WILL lose data and this probably will NOT work the first time! **

The query will need to be modified if your field machine names are not the same as above. The content_field_location table is rebuilt completely.

SET @ num := ifnull(
  (
    SELECT
      max(lid)+ 1
    FROM
      location_instance
  ),
  0
);

INSERT INTO location_instance(nid, vid, uid, genid, lid) SELECT
  nid,
  vid,
  0 AS uid,
  concat(
    'cck:field_location:',
    cast(vid AS char)
  )AS genid,
  (@ num := @ num + 1)AS lid
FROM
  content_field_address
ORDER BY
  vid ASC;

INSERT INTO location(
  lid,
  NAME,
  street,
  additional,
  city,
  province,
  postal_code,
  country,
  latitude,
  longitude,
  SOURCE,
  is_primary
)SELECT
  b.lid,
  '' AS NAME,
  trim(a.field_address_street1),
  trim(
    concat(
      a.field_address_street2,
      ' ',
      a.field_address_apt,
      ' ',
      field_address_other
    )
  )AS additional,
  a.field_address_city,
  a.field_address_state,
  a.field_address_zip,
  lcase(trim(a.field_address_country)),
  0,
  0,
  3 AS SOURCE,
  0 AS is_primary
FROM
  content_field_address a,
  location_instance b
WHERE
  a.vid = b.vid
AND a.nid = b.nid
AND genid LIKE 'cck:field_location:%'
ORDER BY
  lid ASC;

DELETE
FROM
  content_field_location
WHERE
  field_location_lid > 0;

INSERT INTO content_field_location(vid, nid, field_location_lid) SELECT
  vid,
  nid,
  lid
FROM
  location_instance
WHERE
  genid LIKE 'cck:field_location:%'
ORDER BY
  nid ASC;

Hope someone finds this useful..

Regards,
Jason Fisher