How to insert multiple records from fields in select record

• DBA.k

  Ten Centuries

  Points: 1105

  More actions

  February 7, 2019 at 3:31 am

  #2021348

  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

  Use Unpivot you will get the expected result