SQL临时表代码无法运行求助：移除中间两行可正常输出

Hey there, let's figure out why your temp table code is failing when you add the INSERT statement. First, let's recap your scenario:

Your code works fine (returns an empty single-column result) when you remove the INSERT line, but breaks when you include it. Even after switching from dbo.Products to Products as suggested, the problem persists.

Here's your original code for reference:

CREATE TABLE #ProductNames (ProductName varchar(40));
INSERT INTO #ProductNames SELECT p.ProductName FROM DBO.PRODUCTS AS p;
SELECT * FROM #ProductNames;

Common Causes & Fixes to Try #

Let's break this down step by step:

• Isolate the root query issue first
  The most likely culprit is the SELECT p.ProductName FROM DBO.PRODUCTS AS p part itself. Run this query alone in your database tool—does it return data, or throw an error? If it errors out, that's exactly why the INSERT is failing.

• Check schema & table name validity

  • If you switched to Products instead of dbo.Products, verify your current user's default schema by running SELECT SCHEMA_NAME();. If your default schema isn't dbo, Products might be pointing to a non-existent table in another schema.
  • Double-check the table name case: is your table actually named PRODUCTS (all caps)? Some databases are case-sensitive depending on collation settings—match the exact case of the table as it exists in your database.

• Verify permissions
  Make sure your user account has SELECT permissions on the dbo.PRODUCTS table (or whichever schema the table resides in). Even if you can see the table, missing SELECT access will cause the INSERT to fail silently or throw a permission error.

• Confirm the ProductName column exists
  Double-check that the PRODUCTS table actually has a column named ProductName (watch for case sensitivity here too). A typo in the column name would break the SELECT, and in turn the INSERT.

Tested Working Example (If the Isolated Query Works) #

If the standalone SELECT returns data, try adding explicit column names to the INSERT for clarity (this can also avoid unexpected issues with column order):

CREATE TABLE #ProductNames (ProductName varchar(40));
INSERT INTO #ProductNames (ProductName) -- Explicitly specify the target column
SELECT p.ProductName FROM DBO.PRODUCTS AS p;
SELECT * FROM #ProductNames;

If none of these fix it, share the exact error message you're getting—that'll help narrow things down even more!

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