June 4, 2014 at 9:53 am
Hi
Sorry to ask 2 questions so fast... I'm just learning...
I want to fill a table variable with 2 differents cte. but I got this error : Invalid object name "table2"
insert into @TableVariable
select cte1.*
from cte1;
insert into @TableVariable
select cte2.*
from cte2;
thanks
June 4, 2014 at 9:59 am
And where are you using table2?
You didn't include the CTE definitions and there's a lot of missing information.
Please include the whole code to understand the problem.
June 4, 2014 at 10:02 am
Like Luis said, we'll need DDL and sample data to be able to help you here. If you have any question on what we mean by this, please see the second link in my signature.
June 4, 2014 at 10:22 am
dquirion78 (6/4/2014)
HiSorry to ask 2 questions so fast... I'm just learning...
I want to fill a table variable with 2 differents cte. but I got this error : Invalid object name "table2"
insert into @TableVariable
select cte1.*
from cte1;
insert into @TableVariable
select cte2.*
from cte2;
thanks
Aside from the missing information you have two major issue with this query.
First is you are doing an insert without explicitly naming the columns. Second is using select * as the source. Consider how painful this will be if at some point you change the table variable or the cte that populates it.
Also, do you need table variables? Generally speaking, temp tables are a better choice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 11:04 am
I know about he "*" it's just a example.
I don't use Cte2 anywhere except to insert into the table variable. Just easier to read and understand to put the long and complex select into a cte.
By the way, Cte2 use some data inside the tablevariable( to put other data inside the same tablevariable
cte2 is something like this
select TTable.field1
from tablevaritable TTable
inner join Table3 on table3.field2=ttable.field2
So I need insert into the tablevariable with cte1 before I can use the cte2.
June 4, 2014 at 11:27 am
-- impossible to do any select on cte except on real table or table variable after I did a insert into because I got (Invalid object name ) when I run the stored procedure but I can compile it without any error.
insert into @TableVariable -- WORKING
select * from cte1
select *
from cte2 ( any cte in my stored procedure) -- NOT WORKING
but I can do
select *
from @TableVariable T -- WORKING
or I can do
select *
from RealTable1 T -- WORKING
but I remove the insert into part then any select on cte work again.
June 4, 2014 at 11:36 am
Remember that CTEs will be available only for one statement and not the whole query/procedure.
Think of them as some kind of re-arranged subqueries.
June 4, 2014 at 11:44 am
Really
So I probably need a new stored procedure !
June 4, 2014 at 11:59 am
dquirion78 (6/4/2014)
ReallySo I probably need a new stored procedure !
Maybe not. If you show us what it is you are trying to do instead of hiding it behind obfuscated code we code provide you with better answers.
June 4, 2014 at 12:02 pm
dquirion78 (6/4/2014)
ReallySo I probably need a new stored procedure !
Maybe a new database or a new server. :hehe:
We can't see what's going on, so we can't really help.
June 4, 2014 at 12:09 pm
I'm not showing my code is because it's so much long and Field are in french.
Basically
I want to select cte1 union all cte2 but cte2 uses cte1 and cte1 is really long and complex.
I can use a union all BUT it's take 2 times more seconds ( 5 vs 2.5)
So I want to put cte1 in a temp table(store procedure #1)
and after use temp table on cte2 instead of cte1. (store procedure #2)
So Cte2 execution time will be faster because cte1 won't be execute 2 times.
I just hope it's faster this way.
June 4, 2014 at 12:14 pm
dquirion78 (6/4/2014)
I'm not showing my code is because it's so much long and Field are in french.Basically
I want to select cte1 union all cte2 but cte2 uses cte1 and cte1 is really long and complex.
I can use a union all BUT it's take 2 times more seconds ( 5 vs 2.5)
So I want to put cte1 in a temp table(store procedure #1)
and after use temp table on cte2 instead of cte1. (store procedure #2)
So Cte2 execution time will be faster because cte1 won't be execute 2 times.
I just hope it's faster this way.
Who cares what language the columns names are in? We can't offer much help because we can't see your screen, have no idea what your data structures are like, no idea what your query is trying to do. In short, we can't help much because you haven't given us the whole question. Maybe you need to defined both ctes at the same time?
with cte1 as (select whatever here),
cte2 as (select somestuff join to cte1 etc...),
cte3 as (select cte1 union cte2)
If you really want help with this process (which I am guessing that performance is part of issue here) you are going to have to provide us some details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 12:24 pm
cte1
as
select table2.field1, table2.field2
from table2
where...
--table3.field1 not the same value than table2.field1
cte2
as
select table3.field1, table3.field2
from table3
inner join CTE1
union table3.field2 on CTE1.FIELD2
where ...
select cte1.field1,cte1.field2
from cte1
union all
select cte2.field1,cte2.field2
from cte2
It takes too much time this way because cte1 is used 2 times. I want to use temp table for cte2 instead to use cte1.
June 4, 2014 at 12:30 pm
dquirion78 (6/4/2014)
cte1as
select table2.field1, table2.field2
from table2
where...
--table3.field1 not the same value than table2.field1
cte2
as
select table3.field1, table3.field2
from table3
inner join CTE1
union table3.field2 on CTE1.FIELD2
where ...
select cte1.field1,cte1.field2
from cte1
union all
select cte2.field1,cte2.field2
from cte2
It takes too much time this way because cte1 is used 2 times. I want to use temp table for cte2 instead to use cte1.
Use the construct I just posted.
cte1
as
select table2.field1, table2.field2
from table2
where...
--table3.field1 not the same value than table2.field1
, --NOTE the comma here, this means there is a second cte definition. BOTH of them are now available in the next statement.
cte2
as
select table3.field1, table3.field2
from table3
inner join CTE1
union table3.field2 on CTE1.FIELD2
where ...
select cte1.field1,cte1.field2
from cte1
union all
select cte2.field1,cte2.field2
from cte2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 12:31 pm
Who cares what language the columns names are in? We can't offer much help because we can't see your screen, have no idea what your data structures are like, no idea what your query is trying to do. In short, we can't help much because you haven't given us the whole question. Maybe you need to defined both ctes at the same time?
with cte1 as (select whatever here),
cte2 as (select somestuff join to cte1 etc...),
cte3 as (select cte1 union cte2)
If you really want help with this process (which I am guessing that performance is part of issue here) you are going to have to provide us some details.
Basically what I'm doing right now but it takes too much time. Cte1 is pretty long and complex, cte2 is simple but use cte1...
Cte1 takes 2.5 seconds
cte2 takes 2.6 seconds because of cte1
cte3 takes around (2.5+2.6)
so If I put cte1 inside a temp table and cte2 use the temp table then it will be way faster because ( I guess).
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply