0

I have a table with 260 rows of information (Im taking from an XML column in NAMES table) , I want to take the first names of each row of that table and insert to a new table USR_BBUSXMLTNS.

My insert statement:

begin try
    -- handle inserting the data
    insert into dbo.USR_BBUSXMLTNS
        (ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,FIRSTNAME)
    values
        (@ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
        (SELECT  CAST(REPLACE(REPLACE(CONVERT(nvarchar(max), Data), 
        '<?xml version="1.0" encoding="utf-8"?>', ''), 
        ' xmlns="urn:books.RET.XDATA"', '') AS xml).value('(//Donor/FirstName/text()) [1]', 
        'nvarchar(max)') FROM Names) )
    
end try

I want the insert statement to loop through the Names table and get the name value for each row and add it to the insert , ideally I will have the 260 names in the USR_BBUSXMLTNS table when it completes.

Ive searched the site and tried different solutions but not certain that they are designed for what I need.

when I run this I get : Subquery returned more than 1 value. This is not permitted when the subquery follows =, as there is multiple results - Can I do a loop?

Any advice would be great.

David.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
davie
  • 13
  • 3
  • Just `insert` from a `select` instead of a `values()` clause. `insert into [...] select @ID, [...] cast( [...] ) from Names;` – underscore_d Jun 24 '20 at 14:28
  • "_Ive searched the site and tried different solutions but not certain that they are designed for what I need._" Identify what all you tried and what about them made you "not certain" please – underscore_d Jun 24 '20 at 14:30

0 Answers0