Insert a new row in Table2 for each column of Table1

Question

Given the following table layout and data rows:

SELECT [Id]
      ,[EmailAddress]
      ,[PhoneNumber1]
      ,[PhoneNumber2]
      ,[FaxNumber]
FROM [Database].[dbo].[Table1]


1   NULL            800-222-2222    800-333-3333    800-444-4444
2   e@email.com     800-555-5555    800-777-7777    800-888-8888

I'm looking to insert a new row for each column that is not null into the following table layout:

SELECT [Id]
      ,[FkId]
      ,[Value]
FROM [Database].[dbo].[Table2]

Here's an example of what I believe the desired output to be.

1   1   800-222-2222
2   1   800-333-3333
3   1   800-444-4444
4   2   e@email.com
5   2   800-555-5555
6   2   800-777-7777
7   2   800-888-8888

Big picture, I'm looking to repeat this INSERT for every row in Table1. Figuring out how to do this for at a minimum of one row would be a good starting point.

Well, you could do it using a loop in some sort of script if you're already manipulating the data somehow programmatically with code, or you can write a SQL statement that will do it. There always seems to be a thousand ways to do one thing, just wanting to know what your needs were. — Brice, Jun 03 '16 at 20:32

Dmitriy Khaykin · Accepted Answer · 2016-06-03T20:32:05.007

Use the Union statement to get one set of data and it is as simple as an insert statement:

Insert Into Table2
Select Id, EmailAddress From Table1 
Where EmailAddress Is Not Null And EmailAddress <> ''
    Union All 
Select Id, PhoneNumber1 From Table1 
Where PhoneNumber1 Is Not Null And PhoneNumber1 <> ''
    Union All 
Select Id, PhoneNumber2 From Table1 
Where PhoneNumber2 Is Not Null And PhoneNumber2 <> ''
    Union All 
Select Id, FaxNumber From Table1 
Where FaxNumber Is Not Null And FaxNumber <> ''
Order By Id

Or use a cte or table variable if you want the code to look a little cleaner:

; With tempCte As (
    Select Id, EmailAddress As Value From Table1 Union All 
    Select Id, PhoneNumber1 As Value  From Table1 Union All 
    Select Id, PhoneNumber2 As Value  From Table1  Union All 
    Select Id, FaxNumber  As Value From Table1 
)
Insert Into Table2
Select Id, Value From tempCte
Where Value Is Not null
Order By Id

score 1 · Answer 2 · answered Jun 03 '16 at 20:36

1

Here is a quick piece of code to do it using UNPIVOT

INSERT INTO Table2
SELECT 
    u.Id,
    u.Type,
    u.Value
FROM Table1 as t
UNPIVOT
(value for Type in (EmailAddress, Phone1, Phone2, FaxNumber))
as u

answered Jun 03 '16 at 20:36

basodre

5,720
1
15
23

score 0 · Answer 3 · answered Jun 03 '16 at 20:24

Union is great for this purpose. I would recommend to add valType field to table2.

insert table2(fkid,value,valType)
select id,email, 'email'
from table1 where email is not null
union
select id,phone1, 'phone1'
from table1 where phone1 is not null
--repeat union for other values.

score 0 · Answer 4 · answered Jun 03 '16 at 20:26

I can think of a fairly straightforward way to do it using a different INSERT statement for each field you want:

INSERT INTO Table2 (FkId, Value) 
    SELECT Id, EmailAddress FROM Table1 
    WHERE EmailAddress IS NOT NULL;

Replace EmailAddress with the another column name from Table1 for each statement and that should just pull all of the columns where the column isn't null. Or you can use a UNION on all SELECT statements to do it all at once.

Insert a new row in Table2 for each column of Table1

4 Answers4