SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 T SQL erorr
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skybvi
Posting Yak Master

Virgin Islands (United Kingdom)
193 Posts

Posted - 12/14/2011 :  12:04:03  Show Profile  Reply with Quote
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

USA
36987 Posts

Posted - 12/14/2011 :  12:06:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1721 Posts

Posted - 12/14/2011 :  12:11:49  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/14/2011 :  12:19:43  Show Profile  Reply with Quote
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
Go to Top of Page

skybvi
Posting Yak Master

Virgin Islands (United Kingdom)
193 Posts

Posted - 12/14/2011 :  12:26:42  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000