loading data into temp table on PDW

Question

 use tempdb;

CREATE TABLE #tmpMain(
     [PNRRecordLocator] char(6) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
     [PNRCreateDate] date NULL)
 WITH (LOCATION = USER_DB)

 insert into #tmpMain 
 from {TKT].[TktCpn]
 where [CpnDepLocalDateTime] > '4/23/2018' and
     [CpnDepLocalDateTime] < '5/11/2018' and
     [CpnCurrentStatusCode] = 'USED' 

so i am using a PDW server for the first time and I am having some trouble loading data into a temp table. I believe I have the correct query but I still get an error saying in correct syntax near "from".

also, does (LOCATION = USER_DB) need to be specified or is that what i enter? sorry new to PDW

Answer 1

Can you change the curly bracket { after from to a square bracket [ and then try in the Insert/Select statement?

Answer 2

You nearly have it. If you are creating the table beforehand then you need to specify the columns you are inserting

     insert into #tmpMain (PNRRecordLocator, PNRCreateDate)
     select PNRRecordLocator, PNRCreateDate
     from {TKT].[TktCpn]
     where [CpnDepLocalDateTime] > '4/23/2018' 
     and [CpnDepLocalDateTime] < '5/11/2018' 
     and [CpnCurrentStatusCode] = 'USED' 

If you arent creating the table beforehand then you can just do

     select PNRRecordLocator, PNRCreateDate
     into #tmpMain
     from {TKT].[TktCpn]
     where [CpnDepLocalDateTime] > '4/23/2018' 
     and [CpnDepLocalDateTime] < '5/11/2018' 
     and [CpnCurrentStatusCode] = 'USED'