CODEWITHSUNDEEP
sqlsql-server
Lily
Lily

Reputation: 215

Not able to insert a row in a table which has auto incremented primary key

I have a table reportFilters which has the following column names:

image

The reportFilterId is auto increment. I want to insert a row in the table with the script below:

IF OBJECT_ID(N'ReportFilters', N'U') IS NOT NULL
BEGIN
    IF NOT EXISTS (SELECT * FROM [ReportFilters]
                    WHERE ReportId IN (SELECT ReportId FROM [Reports] WHERE ReportType = 'Operational Insights Command Staff Dashboard') )
    BEGIN
        INSERT INTO [ReportFilters] Values(1, 'SelectView', 'Select Views', 13, 'Views','Views', 'SelectView', 'a', 'b', 'c' );
    END
END
GO

But I am getting the following error:

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

Can I please get help on this ? Thanks in advance.

Upvotes: 0

Views: 75

Answers (1)

D-Shih
D-Shih

Reputation: 46229

I think the problem is on inserted columns can't match with inserted data because that will instead by your table column order which is ReportFilterId instead of ReportId

So that there are 11 columns in your table but your statement only provides 10 columns.

I would use explicitly specify for inserted columns (inserted columns start from ReportId except your PK ReportFilterId column)

INSERT INTO [ReportFilters] (ReportId,ReportFilterName,ReportFilterTitle....)
 Values (1, 'SelectView', 'Select Views', 13, 'Views','Views', 'SelectView', 'a', 'b', 'c' );

Upvotes: 1

Related Questions