December 9, 2008 at 7:22 am
Hi forum,
I'm trying to use populate a table variable from different tables and then update certain columns from other tables. I need to check if the record already exists or not in my table variable but SQL doesn't seem to like the idea. Here's a sample script that demonstrates the problem I'm having.
Why is this not working ? Can't I use table variables the same way I'd use a real or temp table ?
declare @tbl1 table
(
Col1 char(1)
)
insert into @tbl1
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E' union all
select 'F'
declare @tbl2 table
(
Col1 char(1)
)
insert into @tbl2
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E' union all
select 'F' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f'
insert into @tlb1 select col1 from tbl2 where not exists (select 1 from @tbl1 where col1 = @tbl2.col1)
Msg 1087, Level 15, State 2, Line 31
Must declare the table variable "@tlb1".
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@tbl2".
December 9, 2008 at 7:35 am
I did not check the logic but this fixes your syntax.
insert into @tbl1 select col1 from @tbl2 t2 where not exists (select 1 from @tbl1 where col1 = t2.col1)
December 9, 2008 at 7:35 am
You are missing standard syntax.. try
insert into @tlb1
select col1
from @tbl2
You are missing the @ on the from table to for the insert. So you need to correct that syntax similar to what i posted.
December 9, 2008 at 7:44 am
You're right on the syntax errors, I was too quick in coding my test script and didn't see it. The fixed code however gives the same problem.
declare @tbl1 table
(
Col1 char(1)
)
insert into @tbl1
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E' union all
select 'F'
declare @tbl2 table
(
Col1 char(1)
)
insert into @tbl2
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E' union all
select 'F' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f'
insert into @tbl1 select col1 from @tbl2 where not exists (select 1 from @tbl1 where col1 = @tbl2.col1)
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@tbl2".
engintoksoz' solution worked.....I don't understand why but it does so I'm happy 🙂
Thanks !
December 9, 2008 at 8:13 am
I don't think the logic works. here is one it would
insert into @tbl1
select col1 Collate Latin1_General_BIN as col1 from
(
select col1, 2 as col2 from @tbl2
union all
select col1, 1 as col1 from @tbl1
) tbl
group by col1 Collate Latin1_General_BIN having sum(col2) = 2
December 9, 2008 at 8:48 am
I believe it's getting confused in the correlated subquery. Aliasing the tablenames seems to work for me:
insert into @tbl1 select col1 from @tbl2 t2 where not exists (select 1 from @tbl1 t1 where t1.col1 = t2.col1)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply