Insert into another table from a CTE table

Without getting into the specifics of your query...

;with cte as (
  SELECT yabba, dabba, doo
  FROM some_table) 
INSERT INTO foo (loo, nee, bin) 
SELECT yabba, dabba, doo
FROM cte

Select allOpen in new window

That's the issue. I don't know where it goes in the query I have. I've tried it in the beginning, after it, middle of it. For simple CTE, it's obvious but I don't know where it goes in mine.

I want to insert the results of the CTE I have into another table.  I don't know where the insert goes.

I'l l just break up the SQL. I'll insert the "CTE" query into a temp table and then UNION ALL with the rest of the query. It's easier than trying to figure out where the insert goes/

I want to insert the results of the CTE I have into another table.  I don't know where the insert goes.

if the requirement is really to insert, then you can try Jim's suggestion above in ID: 42228326 which should work.

as mentioned in my comment ID: 42228345, you may consider to use that multiple ctes approach in which you no need to insert records to another table, or create the temp table.

it's depends on your decision how you consider what methods to be used.

I want to insert the result into another table. I understand Jim's response but I don't know where the insert goes in my query. As I've mentioned above , I tried it in the beginning, middle, end and it's not working in my query.

I'll just break it up and not use CTE.

I want to insert the result into another table. I understand Jim's response but I don't know where the insert goes in my query. As I've mentioned above , I tried it in the beginning, middle, end and it's not working in my query.

if still using CTE, it would be something like this:

;with cte as
(
SELECT  n.c.query('.') entirequery,
       n.c.value('(//num/node())[1]','varchar(max)') ChapterNumber,
	   isnull(n.c.value('(//heading/node())[1]','varchar(max)'),'') AS ChapterName,
       n.c.value('num[1]','varchar(max)') AS 'Subchapter',
	 	stuff (' '+n.c.query('(heading)').value('.', 'varchar(max)'),1, 1, '') subchapterTitle,  

		--section num
	
		stuff (' '+n.c.query('(section[1]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum1,  
		stuff (' '+n.c.query('(section[2]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum2,
		stuff (' '+n.c.query('(section[3]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum3,
		stuff (' '+n.c.query('(section[4]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum4,
		stuff (' '+n.c.query('(section[5]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum5,
		stuff (' '+n.c.query('(section[6]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum6,
		stuff (' '+n.c.query('(section[7]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum7,
		stuff (' '+n.c.query('(section[8]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum8,
		stuff (' '+n.c.query('(section[9]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum9,
		stuff (' '+n.c.query('(section[10]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum10,
		stuff (' '+n.c.query('(section[11]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum11,
		stuff (' '+n.c.query('(section[12]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum12,
		stuff (' '+n.c.query('(section[13]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum13,
		stuff (' '+n.c.query('(section[14]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum14,
		stuff (' '+n.c.query('(section[15]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum15,
		stuff (' '+n.c.query('(section[16]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum16,
		
		--headings that come after user clicks on subchapter title
		stuff (' '+n.c.query('(section/heading)').value('heading[1]', 'varchar(max)'),1, 1, '') heading1,  --heading[1]
		stuff (' '+n.c.query('(section/heading)').value('heading[2]', 'varchar(max)'),1, 1, '') heading2,
		stuff (' '+n.c.query('(section/heading)').value('heading[3]', 'varchar(max)'),1, 1, '') heading3,
		stuff (' '+n.c.query('(section/heading)').value('heading[4]', 'varchar(max)'),1, 1, '') heading4,
		stuff (' '+n.c.query('(section/heading)').value('heading[5]', 'varchar(max)'),1, 1, '') heading5,
		stuff (' '+n.c.query('(section/heading)').value('heading[6]', 'varchar(max)'),1, 1, '') heading6,
		stuff (' '+n.c.query('(section/heading)').value('heading[7]', 'varchar(max)'),1, 1, '') heading7,
		stuff (' '+n.c.query('(section/heading)').value('heading[8]', 'varchar(max)'),1, 1, '') heading8,
		stuff (' '+n.c.query('(section/heading)').value('heading[9]', 'varchar(max)'),1, 1, '') heading9,
		stuff (' '+n.c.query('(section/heading)').value('heading[10]', 'varchar(max)'),1, 1, '') heading10,
		stuff (' '+n.c.query('(section/heading)').value('heading[11]', 'varchar(max)'),1, 1, '') heading11,
		stuff (' '+n.c.query('(section/heading)').value('heading[12]', 'varchar(max)'),1, 1, '') heading12,
		stuff (' '+n.c.query('(section/heading)').value('heading[13]', 'varchar(max)'),1, 1, '') heading13,
		stuff (' '+n.c.query('(section/heading)').value('heading[14]', 'varchar(max)'),1, 1, '') heading14,
		stuff (' '+n.c.query('(section/heading)').value('heading[15]', 'varchar(max)'),1, 1, '') heading15,
		stuff (' '+n.c.query('(section/heading)').value('heading[16]', 'varchar(max)'),1, 1, '') heading16 ,

		--notes 
		stuff (' '+n.c.query('(section[1]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note1,
		stuff (' '+n.c.query('(section[2]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note2,
		stuff (' '+n.c.query('(section[3]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note3,
		stuff (' '+n.c.query('(section[4]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note4,
		stuff (' '+n.c.query('(section[5]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note5,
		stuff (' '+n.c.query('(section[6]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note6,
		stuff (' '+n.c.query('(section[7]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note7,
		stuff (' '+n.c.query('(section[8]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '') Note8,  --+ ' ' + stuff (' '+n.c.query('(section/notes/note)').value('note[9]', 'varchar(max)'),1, 1, '')  Note8, 
		--stuff (' '+n.c.query('(section/notes/note)').value('note[9]', 'varchar(max)'),1, 1, '')  Note8_a,
		stuff (' '+n.c.query('(section[9]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note9, 
		stuff (' '+n.c.query('(section[10]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note10,
		stuff (' '+n.c.query('(section[11]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note11,

		stuff (' '+n.c.query('(section[12]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note12,
		stuff (' '+n.c.query('(section[13]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note13,
		stuff (' '+n.c.query('(section[14]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '')  Note14,
		stuff (' '+n.c.query('(section[15]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '') Note15, -- + ' ' + stuff (' '+n.c.query('(section/notes/note)').value('note[17]', 'varchar(max)'),1, 1, '')  Note15,
		stuff (' '+n.c.query('(section[16]/notes/note/p)').value('.', 'varchar(max)'),1, 1, '') Note16, --+ ' ' + stuff (' '+n.c.query('(section/notes/note)').value('note[19]', 'varchar(max)'),1, 1, '')  Note16,
		--n.c.value('.','varchar(max)') AS 'Section Note all per US Code',

		--codification section

		stuff (' '+n.c.query('(section[1]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading1,
		stuff (' '+n.c.query('(section[2]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading2,
		stuff (' '+n.c.query('(section[3]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading3,
		stuff (' '+n.c.query('(section[4]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading4,
		stuff (' '+n.c.query('(section[5]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading5,
		stuff (' '+n.c.query('(section[6]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading6,
		stuff (' '+n.c.query('(section[7]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading7,
		stuff (' '+n.c.query('(section[8]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading8,  --+ ' ' + stuff (' '+n.c.query('(section/notes/note)').value('note[9]', 'varchar(max)'),1, 1, '')  Note8, 
		--stuff (' '+n.c.query('(section/notes/note)').value('note[9]', 'varchar(max)'),1, 1, '')  Note8_a,
		stuff (' '+n.c.query('(section[9]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading9, 
		stuff (' '+n.c.query('(section[10]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading10,
		stuff (' '+n.c.query('(section[11]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading11,

		stuff (' '+n.c.query('(section[12]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading12,
		stuff (' '+n.c.query('(section[13]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading13,
		stuff (' '+n.c.query('(section[14]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading14,
		stuff (' '+n.c.query('(section[15]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading15, -- + ' ' + stuff (' '+n.c.query('(section/notes/note)').value('note[17]', 'varchar(max)'),1, 1, '')  Note15,
		stuff (' '+n.c.query('(section[16]/notes/note/heading)').value('.', 'varchar(max)'),1, 1, '')  Codificationheading16, --+ ' ' + stuff (' '+n.c.query('(section/notes/note)').value('note[19]', 'varchar(max)'),1, 1, '')  Note16,
		n.c.value('.','varchar(max)') AS SectionNoteAllPerUSCode

 FROM    #temp t 

cross   Apply XmlCol.nodes('/main/chapter/subchapter') n(c)  
)
 
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum1 as SectionNumber, --subchapterTitleFirstLineHeader,paragraph1 as paragraph, subchapterTitleFirstLine,
      heading1 as heading, Note1 as Note, Codificationheading1 as Codificationheading, SectionNoteAllPerUSCode
      from cte
union all
select entirequery, ChapterNumber, ChapterName, Subchapter, subchapterTitle,  SectionNum2 as SectionNumber,
      heading2 as heading, Note2 as Note, Codificationheading2 as Codificationheading, SectionNoteAllPerUSCode
      from cte
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle, SectionNum3 as SectionNumber,
      heading3 as heading, Note3 as Note, Codificationheading3 as Codificationheading, SectionNoteAllPerUSCode
      from cte

union all
select entirequery, ChapterNumber, ChapterName, Subchapter, subchapterTitle, SectionNum4 as SectionNumber,
      heading4 as heading, Note4 as Note, Codificationheading4 as Codificationheading, SectionNoteAllPerUSCode
      from cte
--5
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum5 as SectionNumber,
      heading5 as heading, Note5 as Note, Codificationheading5 as Codificationheading, SectionNoteAllPerUSCode
      from cte
--6
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum6 as SectionNumber,
      heading6 as heading, Note6 as Note, Codificationheading6 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--7
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle, SectionNum7 as SectionNumber,
      heading7 as heading, Note7 as Note, Codificationheading7 as Codificationheading, SectionNoteAllPerUSCode
      from cte
--8

union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum8 as SectionNumber,
      heading8 as heading, Note8 as Note, Codificationheading8 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--9

union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum9 as SectionNumber,
      heading9 as heading, Note9 as Note, Codificationheading9 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--10
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum10 as SectionNumber,
      heading10 as heading, Note10 as Note, Codificationheading10 as Codificationheading, SectionNoteAllPerUSCode
      from cte
--11
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum11 as SectionNumber,
      heading11 as heading, Note11 as Note, Codificationheading11 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--12
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum12 as SectionNumber,
      heading12 as heading, Note12 as Note, Codificationheading12 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--13
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum13 as SectionNumber,
      heading13 as heading, Note13 as Note, Codificationheading13 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--14
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle, SectionNum14 as SectionNumber,
      heading14 as heading, Note14 as Note, Codificationheading6 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--15
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum15 as SectionNumber,
      heading15 as heading, Note15 as Note, Codificationheading15 as Codificationheading, SectionNoteAllPerUSCode
      from cte

--16
union all
select entirequery, ChapterNumber, ChapterName, Subchapter,  subchapterTitle,  SectionNum16 as SectionNumber,
      heading16 as heading, Note16 as Note, Codificationheading16 as Codificationheading, SectionNoteAllPerUSCode
      from cte
)
insert into yourTable
Select * from cte

Select allOpen in new window

Do note that number of fields for the target table and the source table must be the same, or else you need to specify the field names in the Insert SQL statement.

I know I tried that as well...what you have but got an error that "cte" is not defined or not a table. I will try it again.

insert into yourTable
Select * from cte

Thanks. I'll try and post back.

Worked. Thanks.