CODEWITHSUNDEEP
sql-servert-sqlsql-insert
HappyCoding
HappyCoding

Reputation: 661

Insert a new row in Table2 for each column of Table1

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   [email protected]     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   [email protected]
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.

Upvotes: 1

Views: 1137

Answers (4)

basodre
basodre

Reputation: 5770

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

Upvotes: 1

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

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

Upvotes: 1

Brice
Brice

Reputation: 107

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.

Upvotes: 0

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

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.

Upvotes: 0

Related Questions