Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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;
share|improve this answer
2  
+1 clever approach –  Aaron Bertrand Oct 10 '13 at 21:11
    
Nice, thanks a lot! I've never used cross apply because I didn't understand how it could be useful (or how it differs from a join), but this is a great example. –  Dang Oct 10 '13 at 22:19
    
@Dang: CROSS APPLY is like a join, except you can use correlated parameters from the outer query. The drawback is that this construct usually generates a Nested Loops plan, which can be wildly inappropriate if you're not careful. It's okay here because the subquery resolves to a constant scan (i.e., it doesn't do any real data access). –  Jon Seigel Oct 12 '13 at 15:09
    
@JonSeigel Isn't it the same as a cross join, though? –  Dang Oct 12 '13 at 20:15
1  
@Dang: None of the JOINs allow correlated parameters. In this particular case, CROSS APPLY does act a bit like a CROSS JOIN in that it expands the result set, but no, it's not the same thing at all in general. –  Jon Seigel Oct 13 '13 at 14:22
add comment

A few other ways to do it, no real difference in performance even when I scaled up to 1 million rows:

INSERT userContact ( userId, contactType, contactInfo )
SELECT u.userId, x.contactType, x.contactInfo
FROM usersToImport u
    CROSS APPLY (
        VALUES 
        ( 'email', email ),
        ( 'telephone', telephone )
    ) x ( contactType, contactInfo )


INSERT userContact ( userId, contactType, contactInfo )
SELECT userId, contactType, contactInfo
FROM 
    ( 
    SELECT userId, email, CAST( telephone AS VARCHAR(200) ) telephone 
    FROM usersToImport 
    ) u
UNPIVOT ( contactInfo FOR contactType In ([email], [telephone]) ) upvt
share|improve this answer
    
I like the way you used values here, thanks! =] –  Dang Oct 11 '13 at 20:08
add comment

There is nothing wrong with your code, you just forgot to use semi-colons to terminate your statements.

The following works just fine:

insert userContact
select userId,
    'email',
    email
from usersToImport
union all
select userId,
    'telephone',
    telephone
from usersToImport;
------------------^

select * from usersToImport;
---------------------------^

select * from userContact;
-------------------------^

And to be quite honest, I don't think you'll find a more efficient way to turn 5 rows into 10.

share|improve this answer
    
I thought there might have been a more efficient way. Since there isn't, I guess this is it. Thanks a lot! –  Dang Oct 10 '13 at 18:56
1  
@Dang there may be, but I can't think of one off the top of my head. Most other approaches will scan the table twice, even if the query doesn't look like it will... –  Aaron Bertrand Oct 10 '13 at 19:03
    
I just realized the insert was not being executed correctly in SQL Fiddle because it was lacking a semi-colon. I wish they'd say something about that when you tried running the query. –  Dang Oct 10 '13 at 19:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.