SQL Server 2008 R2 - why does my temp table think it has an identity column?

Question

I'm trying to create a stored procedure that will be able to limit the number of records returned by using the department id. I'm trying to limit the records by joining to a temp table.

When I run the code below I get the error:

An explicit value for the identity column in table '#department' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Here's the code:

DECLARE @departmentID INT
SET @departmentID = 4

-- create temp department table --
select top 0 * into #department from PayrollDepartment

-- load temp department table
IF @departmentID < 1 OR @departmentID IS NULL
BEGIN
    INSERT INTO #department SELECT * FROM PayrollDepartment
END 
ELSE
BEGIN
    INSERT INTO #department SELECT * FROM PayrollDepartment WHERE PayrollDepartmentID =     @departmentID
END

I started with:

IF @departmentID < 1 OR @departmentID IS NULL
BEGIN
    SELECT * INTO #department FROM ApplicationEmployeeInfo..PayrollDepartment
END 
ELSE
BEGIN
    SELECT * INTO #department FROM ApplicationEmployeeInfo..PayrollDepartment WHERE PayrollDepartmentID = @departmentID
END

but I get the error:

There is already an object named '#department' in the database

I'm not a SQL expert, so maybe there's a better way to do this?

Do you need to do anything with #Department afterwards? Or are you just returning the results? — GarethD, Nov 25 '13 at 17:31
If you do a `SELECT TOP 0 * INTO ...` you've already created the table. You can't use `INTO` again. You need to a `INSERT ... SELECT` — Kermit, Nov 25 '13 at 17:32
This is for a report. I'm trying to make the stored proc "dynamic" so I can get either all employee records, or just the records for the department. I'm trying to avoid using hard coded SQL and I dont two blocks of code (or stored procs) where the only difference is a where clause. — user3033282, Nov 25 '13 at 19:41

score 5 · Accepted Answer · edited Jul 29 '20 at 18:27

Your temp table has an identity column because SELECT .. INTO replicates the table structure of your original table, which has an identity column.

I would suggest explicitly creating your temporary table, as well as listing the columns you are selecting from department. It is almost never a good idea to use SELECT * in production code, so I would suggest explicitly listing your select and insert into columns:

CREATE TABLE #Department
(   PayrollDepartmentID INT NOT NULL,
    <more columns>
);

IF @departmentID < 1 OR @departmentID IS NULL
    BEGIN
        INSERT #Department (PayrollDepartmentID, <more columns>)
        SELECT  PayrollDepartmentID, <more columns>
        FROM    PayrollDepartment;
    END
ELSE
    BEGIN
        INSERT #Department (PayrollDepartmentID, <more columns>)
        SELECT  PayrollDepartmentID, <more columns>
        FROM    PayrollDepartment
        WHERE   PayrollDepartmentID = @departmentID;
    END

It might seem like more work, and it is definitely more verbose, but it is much better practice and more robust in the long term. It also means you can avoid redundancy by only fetching the columns you actually need from PayrollDepartment

thanks for the help! The page I found with the `SELECT TOP 0 INTO` code said that the primary key needed to be added after the fact. another one suggested that using `SET IDENTITY_INSERT #department ON` would work (but it didn't, of course w/o the column list) — user3033282, Nov 25 '13 at 20:01
No problem. I don't have anything against the `SELECT .. INTO` syntax, it is very handy for ad hoc queries, but as I have said, I would take the extra 5 minutes it takes to write and create the table explicitly. — GarethD, Nov 25 '13 at 20:34

SQL Server 2008 R2 - why does my temp table think it has an identity column?

1 Answers1