CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
AdzzzUK
AdzzzUK

Reputation: 298

INSERT INTO, but retain blank columns?

In a stored procedure I'm creating...

    IF OBJECT_ID('tempdb..##SUBOSummary') IS NOT NULL
        /*Then it exists*/
        DROP TABLE ##SUBOSummary
    CREATE TABLE  ##SUBOSummary
        (
            --RowID int not null identity(1,1) primary key,
            RowNum int IDENTITY(1,1) NOT NULL,
            Dept nvarchar(25) NOT NULL,
            Last24 int,
            WTD int,
            MoTD int,
            YTD int
        )

I then want to add a series of data from another view, and I'll then fill the rest of the data rows in seperately:

INSERT INTO ##SUBOSummary SELECT DISTINCT vw_EmployeeDepartment.Dept FROM vw_EmployeeDepartment

I'm getting the following error:

Column name or number of supplied values does not match table definition.

I'm hazarding a guess that this is because I'm only populating a single field by the insert statement.

Is there a better way to acheive this?

Upvotes: 2

Views: 691

Answers (1)

Thom A
Thom A

Reputation: 96038

The error, in truth, is quite clear here:

Column name or number of supplied values does not match table definition.

In your INSERT statement you're implying you want to insert values into all the columns (as you're not specifying which one), yet you're only supplying one column in your SELECT, so where does that value go? SQL Server has no idea.

You need to supply your column(s) in your INSERT clause:

INSERT INTO ##SUBOSummary (Dept)
SELECT DISTINCT vw_EmployeeDepartment.Dept
FROM vw_EmployeeDepartment;

On a different note, however, don't use a Global Temporary Table, they often perform very poorly.

Upvotes: 4

Related Questions