| Author |
 Topic  |
|
|
skybvi
Posting Yak Master
Virgin Islands (United Kingdom)
192 Posts |
 Posted - 12/14/2011 : 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
USA
35020 Posts |
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1440 Posts |
Posted - 12/14/2011 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/14/2011 : 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
Virgin Islands (United Kingdom)
192 Posts |
Posted - 12/14/2011 : 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) |
|
|
| |
Topic |
|
|
|
T SQL erorr
