How to insert multiple records from fields in select record

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 29, 2019 at 5:42 pm

  #381277

  I am trying to take results from multiple fields from single rows that are not null and put them in multiple rows in another table where each valid field has a record (row) of it's own.

  
DECLARE @TestTable TABLE
(
 Name1 VARCHAR(20),
 Name2 VARCHAR(20),
 Name3 VARCHAR(20)
)

  DECLARE @TestTable2 TABLE
  (
   ID int IDENTITY(1,1),
   SingleName varchar(20)
  )

  INSERT @TestTable VALUES('Tom', 'Larry', NULL)
  INSERT @TestTable VALUES('Ron', NULL, NULL)
  INSERT @TestTable VALUES('Sheryl', 'Mary', 'Mark')

  SELECT * FROM @TestTable

  INSERT @TestTable2
  (
      SingleName
  )
  SELECT ???  FROM @TestTable

  Here is what is in @TestTable
  

  What I want to end up with in @TestTable2:
  

  Thanks

• pietlinden

  SSC Guru

  Points: 63501

  More actions

  January 29, 2019 at 7:31 pm

  #2020500

  step 1: find all the names that are not null 
  step 2: union them together.
  insert the result from step 2 into your final table.
  return results from final table.

  Nothing to it, right?
  Yeah, I didn't post an answer exactly, but that would deny you the chance to try it yourself.

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 29, 2019 at 7:59 pm

  #2020505

  That's fine.
  But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:

  
DECLARE @TestTable TABLE
(
 ID INT IDENTITY(1,1),
 NameRecord INT,
 Name1 VARCHAR(20),
 Name2 VARCHAR(20),
 Name3 VARCHAR(20)
)

  DECLARE @TestTable2 TABLE
  (
   ID int IDENTITY(1,1),
   NameRecord INT,
   SingleName varchar(20)
  )

  INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
  INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
  INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

  SELECT * FROM @TestTable

  INSERT @TestTable2
  (
      NameRecord,
      SingleName
  )
  SELECT ???

  The @TestTable looks like:

  

  The results should look something like:

  

  I could do this with a cursor but would prefer not to.

  Thanks,

  Tom

• ScottPletcher

  SSC Guru

  Points: 101093

  More actions

  January 30, 2019 at 8:37 am

  #2020565

  
INSERT @TestTable2
(
    NameRecord,
    SingleName
)
SELECT ca1.*
FROM @TestTable
CROSS APPLY (
    VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord, Name3)
) AS ca1(ID, NameRecord, SingleName)
WHERE SingleName IS NOT NULL

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 30, 2019 at 8:48 am

  #2020568

  ScottPletcher - Wednesday, January 30, 2019 8:37 AM

  
  INSERT @TestTable2
  (
      NameRecord,
      SingleName
  )
  SELECT ca1.*
  FROM @TestTable
  CROSS APPLY (
      VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord, Name3)
  ) AS ca1(ID, NameRecord, SingleName)
  WHERE SingleName IS NOT NULL

  You don't need to include fields that are consistently repeated, that is, you don't need to include ID and NameRecord, because you can just use the values from the main table.

  INSERT @TestTable2
(
    NameRecord,
    SingleName
)
SELECT tt.NameRecord, ca1.SingleName
FROM @TestTable AS tt
CROSS APPLY( VALUES(Name1), (Name2), (Name3) ) ca1(SingleName)

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 30, 2019 at 10:21 am

  #2020585

  That helped a lot.

  Here is the solution that is a little closer to what I was trying to achieve

  I have 2 tables (old values and new values).  I only want to put in the record where the values have changed from the old to the new.  The issue was that there were multiple types of values (owner, depenants and policies) that had to be compared and I wanted a record for each difference.  So there could be from 0 to 3 records written out for each set of records.

  Here is the code:

  DECLARE @OldTable TABLE
(
 ID INT IDENTITY(1,1),
 NameRecord INT,
 OwnerName VARCHAR(20),
 Dependants INT,
 Policies INT
)

  DECLARE @NewTable TABLE
  (
   ID INT IDENTITY(1,1),
   NameRecord INT,
   OwnerName VARCHAR(20),
   Dependants INT,
   Policies INT
  )
  DECLARE @DiffTable TABLE
  (
   ID int IDENTITY(1,1),
   NameRecord INT,
   Differences varchar(200)
  )

  INSERT @OldTable VALUES(5, 'Tom', 3, NULL)
  INSERT @OldTable VALUES(7, 'Ron', NULL, 4)
  INSERT @OldTable VALUES(9, 'Sheryl', 2, 3)

  INSERT @NewTable VALUES(5, 'Tom', 3, 1)
  INSERT @NewTable VALUES(7, 'Ron', NULL, 4)
  INSERT @NewTable VALUES(9, 'Sheryl Ann', 3, 4)

  
  SELECT *
  FROM @OldTable o
  JOIN @NewTable n
  ON n.NameRecord = o.NameRecord

  SELECT o.NameRecord,
         ca1.value1,
         ca1.value2,
         ca1.valueType,
      CASE WHEN ca1.valueType = 1 THEN 'Owner has changed from ' + ca1.value1 + ' to ' + ca1.value2
     WHEN ca1.valueType = 2 THEN 'Dependants have changed from ' + ca1.value1 + ' to ' + ca1.value2
     WHEN ca1.valueType = 3 THEN 'Policies have changed from ' + ca1.value1 + ' to ' + ca1.value2
     END ValuesChanged
  FROM @OldTable o
  JOIN @NewTable n
      ON n.NameRecord = o.NameRecord
  CROSS APPLY
  (
      VALUES
          (ISNULL(o.OwnerName,''), ISNULL(n.OwnerName, ''), 1),
          (ISNULL(CONVERT(VARCHAR(10), o.Dependants),''), ISNULL(CONVERT(VARCHAR(10), n.Dependants),''), 2),
          (ISNULL(CONVERT(VARCHAR(10), o.Policies),''), ISNULL(CONVERT(VARCHAR(10), n.Policies),''), 3)
  ) ca1 (value1, value2, valueType)
  WHERE ca1.value1 <> ca1.value2

  INSERT @DiffTable
  (
      NameRecord,
      Differences
  )
  SELECT o.NameRecord,
      CASE WHEN ca1.valueType = 1 THEN 'Owner has changed from ' + ca1.value1 + ' to ' + ca1.value2
     WHEN ca1.valueType = 2 THEN 'Dependants have changed from ' + ca1.value1 + ' to ' + ca1.value2
     WHEN ca1.valueType = 3 THEN 'Policies have changed from ' + ca1.value1 + ' to ' + ca1.value2
     END ValuesChanged
  FROM @OldTable o
  JOIN @NewTable n
      ON n.NameRecord = o.NameRecord
  CROSS APPLY
  (
      VALUES
          (ISNULL(o.OwnerName,''), ISNULL(n.OwnerName, ''), 1),
          (ISNULL(CONVERT(VARCHAR(10), o.Dependants),''), ISNULL(CONVERT(VARCHAR(10), n.Dependants),''), 2),
          (ISNULL(CONVERT(VARCHAR(10), o.Policies),''), ISNULL(CONVERT(VARCHAR(10), n.Policies),''), 3)
  ) ca1 (value1, value2, valueType)
  WHERE ca1.value1 <> ca1.value2

  SELECT * FROM @DiffTable

  Here are the three results. The last one is the resulting table with only the differences. 
  

  Thanks for the help.

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 30, 2019 at 12:43 pm

  #2020617

  You don't need the CASE expression.

  SELECT o.NameRecord,
    ca1.value1,
    ca1.value2,
    ca1.valueType + ' has changed from ' + ca1.value1 + ' to ' + ca1.value2 AS ValuesChanged
FROM @OldTable o
JOIN @NewTable n
    ON n.NameRecord = o.NameRecord
CROSS APPLY
(
    VALUES
        (COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
        (COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
        (COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
) ca1 (value1, value2, valueType)
WHERE ca1.value1 <> ca1.value2


  The only difference in your warning message is which value has changed, but it's based on a code that you've hard-coded into your table value constructor.  Instead of hard-coding a code that's later translated into a value, hard-code the value in the first place.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 30, 2019 at 1:15 pm

  #2020623

  I do like that simplification.

  Thanks.

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 30, 2019 at 6:51 pm

  #2020651

  Another question on the above cross apply.

  Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.

  For example, if we have blank in the o.OwnerName, we know we have a new record, so we only need the first row and not the dependants or policies rows.  After they come back they are three separate records. We don't want to stop the whole record, as I do want to report that this is a new record.

  
CROSS APPLY
(
VALUES
(COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
(COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
(COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
) ca1 (value1, value2, valueType)

  Thanks.

• pietlinden

  SSC Guru

  Points: 63501

  More actions

  January 30, 2019 at 9:09 pm

  #2020652

  Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.
  Use ROW_NUMBER() to number the names that are returned and then filter for  = 1?

  CREATE TABLE #test (
  FirstName VARCHAR(20) NOT NULL,
    Letter CHAR NOT NULL
);
GO
INSERT INTO #test (FirstName, Letter) VALUES ('Al','B'),('Al','D'),('Al','A'),
('Bart','X'),('Bart','B'),('Bart','D'),('Homer','D'),('Homer','A');

  SELECT FirstName
    , Letter
FROM
(SELECT FirstName
    , Letter
    , ROW_NUMBER() OVER (PARTITION BY FirstName ORDER BY Letter) AS rn
FROM #test) x
WHERE rn = 1;

  Oh wait, you're using an old version of SQL Server... Windowing functions were introduced in either 2008R2 or 2012. So you'd have to use a Common Table Expression, I think..
  

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 30, 2019 at 11:18 pm

  #2020658

  That wasn't really what I meant but I figured out how to do it. 

  I can't stop all three from being sent from the cross apply, but I can use a case statement in the Cross Applyl and then filter on the rows in the where clause.In this case, if the o.OwnerName is blank, I know this is a new record so I set the other valueTypes to a blank so they will get filtered out.

  
CROSS APPLY
(
    VALUES
        (COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
        (COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), CASE WHEN o.OwnerName IS NULL THEN '' ELSE 'Dependants' END),
        (COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), CASE WHEN o.OwnerName IS NULL THEN '' ELSE 'Policies' END)
) ca1 (value1, value2, valueType)
WHERE valueType <> '' AND
   ca1.value1 <> ca1.value2


  This will only show rows that are different and the valueType is not blank.

  Thanks

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 31, 2019 at 9:08 am

  #2020720

  tshad - Wednesday, January 30, 2019 6:51 PM

  Another question on the above cross apply.

  Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.

  For example, if we have blank in the o.OwnerName, we know we have a new record, so we only need the first row and not the dependants or policies rows.  After they come back they are three separate records. We don't want to stop the whole record, as I do want to report that this is a new record.

  
CROSS APPLY
(
VALUES
(COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
(COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
(COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
) ca1 (value1, value2, valueType)

  Thanks.

  Just add conditions to your WHERE clause.
  AND (o.OwnerName > '' OR ca1.valueType = 'Owner')

  If the conditions are really complex, you can change from a table value constructor to a SELECT/UNION clause.

  CROSS APPLY
(
    SELECT COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'
    UNION ALL
    SELECT COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'
    WHERE o.OwnerName > ''
    UNION ALL
    SELECT COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies'
    WHERE o.OwnerName > ''
) ca1 (value1, value2, valueType)


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• tshad

  SSCertifiable

  Points: 5905

  More actions

  January 31, 2019 at 10:26 am

  #2020738

  The union does give flexibility to how the rows are handled.

  I did it like your first suggestion and just changed valueType to allow me to use the where clause to remove two of the rows.

  Thanks.

• DBA.k

  Ten Centuries

  Points: 1113

  More actions

  February 6, 2019 at 6:44 am

  #2021253

  tshad - Tuesday, January 29, 2019 7:59 PM

  That's fine.
  But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:

  
DECLARE @TestTable TABLE
(
 ID INT IDENTITY(1,1),
 NameRecord INT,
 Name1 VARCHAR(20),
 Name2 VARCHAR(20),
 Name3 VARCHAR(20)
)

  DECLARE @TestTable2 TABLE
  (
   ID int IDENTITY(1,1),
   NameRecord INT,
   SingleName varchar(20)
  )

  INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
  INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
  INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

  SELECT * FROM @TestTable

  INSERT @TestTable2
  (
      NameRecord,
      SingleName
  )
  SELECT ???

  The @TestTable looks like:

  

  The results should look something like:

  

  I could do this with a cursor but would prefer not to.

  Thanks,

  Tom

• DBA.k

  Ten Centuries

  Points: 1113

  More actions

  February 6, 2019 at 6:45 am

  #2021254

  tshad - Tuesday, January 29, 2019 7:59 PM

  That's fine.
  But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:

  
DECLARE @TestTable TABLE
(
 ID INT IDENTITY(1,1),
 NameRecord INT,
 Name1 VARCHAR(20),
 Name2 VARCHAR(20),
 Name3 VARCHAR(20)
)

  DECLARE @TestTable2 TABLE
  (
   ID int IDENTITY(1,1),
   NameRecord INT,
   SingleName varchar(20)
  )

  INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
  INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
  INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

  SELECT * FROM @TestTable

  INSERT @TestTable2
  (
      NameRecord,
      SingleName
  )
  SELECT ???

  The @TestTable looks like:

  

  The results should look something like:

  

  I could do this with a cursor but would prefer not to.

  Thanks,

  Tom

  Just Simple use UNPIVOT

  SELECT u.Namerecord, u.CNAME, u.NAME

              FROM [ANT_STAGE_MISC].[dbo].[FARMFILE] s
              unpivot
              (
               NAME
               for CNAME in (NAME1,NAME2,NAME3)
              ) u