首次使用PDW加载数据至临时表遇语法错误及LOCATION参数疑问

Let's work through your issues one by one:

1. Fixing the "Syntax error near FROM" #

The immediate error is caused by a missing SELECT keyword in your INSERT statement. PDW (and nearly all SQL dialects) requires explicit use of SELECT when inserting data from another table. Your current code skips this critical keyword, which triggers the syntax error.

Corrected SQL Code #

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

-- Added SELECT keyword and explicit column list (best practice for clarity)
INSERT INTO #tmpMain ([PNRRecordLocator], [PNRCreateDate])
SELECT [PNRRecordLocator], [PNRCreateDate]
FROM [TKT].[TktCpn]
WHERE [CpnDepLocalDateTime] > '4/23/2018' 
  AND [CpnDepLocalDateTime] < '5/11/2018' 
  AND [CpnCurrentStatusCode] = 'USED';

Quick notes on the fix:

• I added explicit column names to both the INSERT and SELECT clauses. This avoids bugs if the source table's column order ever changes, and makes your code easier to read for others (or future you).
• If the column order in #tmpMain exactly matches the columns you're pulling from [TKT].[TktCpn], you could omit the column list—but explicit listing is always safer for maintainability.

2. Understanding the LOCATION = USER_DB Parameter #

For PDW temporary tables, here's what you need to know about this setting:

• Session-level temp tables (prefixed with #) default to LOCATION = USER_DB, so you don't need to specify this explicitly—it's optional for your #tmpMain table. This setting stores the temp table in the user database's session-isolated temporary storage.
• Global temp tables (prefixed with ##) require LOCATION = GLOBAL_TEMP_DB if you use the WITH clause, since they're shared across all sessions until the last referencing session terminates.

When should you specify it?

• Only if you want to explicitly document the storage location for clarity, or if you're creating a global temp table (where you must set it to GLOBAL_TEMP_DB). For your session-specific #tmpMain, omitting the WITH (LOCATION = USER_DB) clause will work perfectly.

内容的提问来源于stack exchange，提问作者Kamran