CODEWITHSUNDEEP
sqlsql-servert-sql
user2675269
user2675269

Reputation: 1

Error in my join query

I am working with the code below but it is returning an error -

Incorrect syntax near the keyword 'AS'

I expect the query to create a table using the data from existing tables.

create table Allactivity as
(
    select dbo.dim_client.*,dbo.dim_transaction.* 
    from dbo.dim_client,dbo.dim_transaction,dbo.fact_capital   
    where dbo.dim_client.dim_client_key=dbo.fact_capital.dim_client_key
        and dbo.dim_transaction.dim_transaction_key=dbo.fact_capital.dim_transaction_key
)

Upvotes: 0

Views: 78

Answers (2)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

If you use the select into syntax, you will need to have rights to dynamically create a table.

Another solution is to work with a DBA to create the staging table ahead of time. Then clear and load the staging table each time. Have the DBA give you or the application rights to the table. This can be db_datareader & db_datawriter which is a-lot less access than db_owner!

I did notice that you are using older syntax for joins, you should use the newer syntax for forward compatibility.

http://www.straightpathsql.com/archives/2012/11/sql-server-join-syntax-its/

--
-- DBA - Create empty new table (assumes stage schema exists)
--

select
  dc.*,
  dt.*
into
  stage.all_activity
from
  dbo.fact_capital as fc
left join
  dbo.dim_client as dc
  on fc.dim_client_key = dc.dim_client_key
left join
  dbo.dim_transaction as dt
  on fc.dim_transaction_key = dt.dim_transaction_key
where (1 = 0);

-- User/Application - clear the table
delete from stage.all_activity;

-- User/Application - add some data (+ where clause for range)
insert into
  stage.all_activity
select
  dc.*,
  dt.*
from
  dbo.fact_capital as fc
left join
  dbo.dim_client as dc
  on fc.dim_client_key = dc.dim_client_key
left join
  dbo.dim_transaction as dt
  on fc.dim_transaction_key = dt.dim_transaction_key;


Upvotes: 0

akatakritos
akatakritos

Reputation: 9858

It looks like you're using Microsoft SQL Server. T-SQL has a different syntax: SELECT INTO:

http://msdn.microsoft.com/en-us/library/ms190750.aspx

select dbo.dim_client.*,dbo.dim_transaction.* 
into Allactivity
from dbo.dim_client,dbo.dim_transaction,dbo.fact_capital   
where dbo.dim_client.dim_client_key=dbo.fact_capital.dim_client_key
    and dbo.dim_transaction.dim_transaction_key=dbo.fact_capital.dim_transaction_key

Upvotes: 2

Related Questions