T SQL erorr - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T SQL erorr

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-14 : 12:04:03

my t sql
---------------
declare @xl1 table (Sales_Amount nvarchar (10),Department char(31),Dept_Margin int);

Declare @dt1 datetime,
@dt2 datetime

set @dt1 = dateadd(d,-7,GETDATE())
set @dt2 = dateadd(d,-1,GETDATE())

Select SUM(SLS_AMT) as Sales_Amount,
c.DEP_DESCR as Department,
--sum(b.RTL_PRC),
--sum(b.COST_CASE_PRC),
sum((b.RTL_PRC-b.COST_CASE_PRC))/sum(NULLIF(b.RTL_PRC,0))*100 as Dept_Margin

into @xl1

from DAY_PLU_SALES_1 a
join
PLU_1 b
on a.ITM_ID = b.ITM_ID

left join
DEP_1 c ON
b.STR_HIER_ID=c.STR_HIER_ID

where DT >= @dt1
AND DT <= @dt2

group by c.DEP_DESCR
order by c.DEP_DESCR

select * from @xl1

----------------

ERROR---Incorrect syntax near '@xl1'.

What's the error.

Regards,
Sushant
DBA
West Indies

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-14 : 12:06:54

You can't use a table variable for that. You'll need to switch to a regular tempoary table, the # kind.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-14 : 12:11:49

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. Your table already exists. Also, you can specify a permanent table or a temp table but you cannot specify a table variable; hence the syntax error.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-14 : 12:19:43

You can do this

declare @xl1 table (Sales_Amount nvarchar (10),Department char(31),Dept_Margin int);

Declare @dt1 datetime,
@dt2 datetime

set @dt1 = dateadd(d,-7,GETDATE())
set @dt2 = dateadd(d,-1,GETDATE())

insert into @xl1
Select SUM(SLS_AMT) as Sales_Amount,-- but your @xl1 is nvarchar!
c.DEP_DESCR as Department,
--sum(b.RTL_PRC),
--sum(b.COST_CASE_PRC),
sum((b.RTL_PRC-b.COST_CASE_PRC))/sum(NULLIF(b.RTL_PRC,0))*100 as Dept_Margin

from DAY_PLU_SALES_1 a
join
PLU_1 b
on a.ITM_ID = b.ITM_ID
left join
DEP_1 c ON
b.STR_HIER_ID=c.STR_HIER_ID
where DT >= @dt1
AND DT <= @dt2

group by c.DEP_DESCR
order by c.DEP_DESCR

select * from @xl1

Jim

Everyday I learn something that somebody else already knew

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-14 : 12:26:42

Ok, i Changed it to #xl1 and it worked nicely.

My further concern is that i want to put similarily 6 more tables
for ex
#xl1,#xl2...#xl6

And I want to see the output of all tables in an excel file in different sheets.
FOr ex
#xl1 -sheet1
#xl2 -sheet2
...
#xl6 -sheet6

I know it would require SSIS

So i would first put step 1 as t sql (to get all 6 tables)
and then SSIS package as step 2

Which tasks would give teh correct output in the excel sheets.?

Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources