InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:24:17 GMT20http://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxNo. Not really. This is psuedo-code to describe the situation:[code="sql"]WITH MyCTE(...)INSERT INTO MyTableSELECT * FROM MyCTE AS xJOIN MyCTE as yON x.ID = Y.IDand x.Type=1JOIN MyCTE AS zON x.ID = z.IDAND z.Type=2[/code]Something like this will work fine. But if I did this:[code="sql"]WITH MyCTE(...)INSERT INTO MyTableSELECT * FROM MyCTE as XINSERT INTO MyOtherTableSELECT * FROM MyCTE aS y[/code]That will fail because the CTE only lives as long as the first statement following it's definition, regardless of how many times it's referenced within the statement. Tue, 11 Jan 2011 12:38:33 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspx[quote]Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query. [/quote]you mean it can be used only once inside an inner join and that's all the problem?Tue, 11 Jan 2011 12:33:16 GMTpeleghttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxThe CTE "belongs" to the query, not the session.IOW, if you want to persist it for the entire session you will not be able to use a CTE - you could use a table variable or temp tablejgTue, 11 Jan 2011 12:26:54 GMTgmbyhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspx[quote][b]peleg (1/11/2011)[/b][hr]"SSC Veteran" - what do you mean by that?that you can't use the same cte more then once in one session?[/quote]Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query.Tue, 11 Jan 2011 12:26:07 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspx"SSC Veteran" - what do you mean by that?that you can't use the same cte more then once in one session?Tue, 11 Jan 2011 12:20:11 GMTpeleghttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxIt looks like the CTE is being refernced in more than one query:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155076[/url]Tue, 11 Jan 2011 09:51:25 GMTLamprey13http://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxWell now I can't recreate it. Nuts. I should have kept that code I had that generated it.Tue, 11 Jan 2011 08:51:37 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspx[quote][b]Grant Fritchey (1/11/2011)[/b][hr][quote][b]Maxim Picard (1/11/2011)[/b][hr]Are you referencing cteGetNewVSOldGroup later in your script?[/quote]That may be an issue as well, but if you try to hop a database and reference a CTE, you'll get that exact error.[/quote]Hi Grant,I don't understand what you mean. How can you change database context after creating the CTE? I am trying to replicate his script with test tables and not getting the error.Maybe I need another cofee :)[code="plain"]CREATE DATABASE DB1CREATE DATABASE DB2GOUSE DB1GOCREATE TABLE dbo.TableInDB1( col1 CHAR(1))GOINSERT dbo.TableInDB1 VALUES ('1')GO--==== Switch to another contextUSE DB2GO;WITH cteTest (col1) AS( SELECT col1 FROM DB1.dbo.TableInDB1 )SELECT *FROM cteTest[/code]Tue, 11 Jan 2011 08:30:30 GMTMaxim Picardhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspx[quote][b]Maxim Picard (1/11/2011)[/b][hr]Are you referencing cteGetNewVSOldGroup later in your script?[/quote]That may be an issue as well, but if you try to hop a database and reference a CTE, you'll get that exact error.Tue, 11 Jan 2011 08:23:05 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxAre you referencing cteGetNewVSOldGroup later in your script?Tue, 11 Jan 2011 08:15:56 GMTMaxim Picardhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxIt's because you're trying to run it across databases. The CTE created in the database you're in, not the database you're running against.I'm unsure of how to resolve that. I'll have to do a bit of research.Tue, 11 Jan 2011 07:39:44 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045825-391-1.aspxi am using a CTE expression, as part of a script that copy data between diffrent db's.in the middle of the script i have the following code :[code="sql"];WITH cteGetNewVSOldGroup (oldGroupId, newGroupId) AS ( select t1.oldGroupId,t2.id from #tblGroups t1 Inner join [DB1].[dbo].Dyn_Group t2 on t1.name=t2.name where t2.CompanyRef=@newCompanyRef and t2.UserRef=@newUserId ) INSERT INTO [DB1].[dbo].[Dyn_Group2] ([GrandParent] ,[Parent] ,[Child]) select t2.newGroupId,t3.newGroupId,t4.newGroupId from #tblGroup2Group t1 inner join cteGetNewVSOldGroup as t2 on t1.GrandParent=t2.oldGroupId inner join cteGetNewVSOldGroup as t3 on t1.Parent=t3.oldGroupId inner join cteGetNewVSOldGroup as t4 on t1.Child=t4.oldGroupId[/code]i get an error : Invalid object name 'cteGetNewVSOldGroup'.any idea?thanksPelegTue, 11 Jan 2011 05:42:24 GMTpeleg