Sql-server – Inserting two rows from single row

insertsql serversql-server-2012t-sql

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind of contact information per row.

Here's how I'm doing it currently: http://sqlfiddle.com/#!6/c9b2e/1 (for some reason, it's not outputting anything, but the code works in SSMS).

Is there a way to do this without using two different selects? Maybe self-joining usersToImport or using case somehow.

Best Answer

As Aaron mentioned, for some reason you need to terminate each statement in Fiddle.

Anyway, for the query, you can use CROSS APPLY to expand the result set. This method scans the base table only once, and for each row applies the subquery, which in this case actually selects two rows:

INSERT INTO userContact(userId, contactType, contactInfo)
  SELECT
    ui.userId,
    c.contactType,
    c.contactInfo
    FROM usersToImport ui
    CROSS APPLY
    (
      SELECT 'email' AS contactType, ui.email AS contactInfo UNION ALL
      SELECT 'telephone', ui.telephone
    ) c;
Related Question