locked
SQL Statement Error RRS feed

  • Question

  • What does this mean exactly? I am having a difficult time making primary keys in 2 different tables along with a foreign key in one table.

    Msg 544, Level 16, State 1, Line 29
    Cannot insert explicit value for identity column in table 'table015' when IDENTITY_INSERT is set to OFF.

     

    Here is my SQL code:

    create database database005
    create table table005
    (
    EmployeeID int Identity Not Null
     Primary Key,
    LastName varchar(100),
    FirstName varchar(100),
    Address varchar(100),
    City varchar(100),
    State char(2),
    AreaCode char(3),
    PhoneNumber char(50),
    EEO1 varchar(100),                                                    
    HireDate date,
    Salary money,
    Gender char(1),
    Age char(5)
    )                            
    create table table006
    (
    ExemptStatus char(5),
    EEO1 varchar(100) Not Null
     Primary Key,
    EmployeeID int Not Null
     Foreign Key References table005(EmployeeID),        
    JobTitle varchar(100),            
    JobDescription varchar(max)
    )
    Insert Into table005
    (EmployeeID, LastName, FirstName, Address, City, State, AreaCode, PhoneNumber, EEO1, HireDate, Salary, Gender, Age)
    Values
    ('1', 'Slentz', 'Raj', '123 Torrey Dr.', 'North Clairmont', 'CA', '619', '555-0123', 'Officials & Managers', '6/1/2000', '$48,000.00', 'M', '34'),
    ('2', 'Broun', 'Erin', '2045 Parkway Apt.2B', 'Encinitas', 'CA', '760', '555-0100', 'Sales Worker(p)', '3/12/2003', '$10,530.00', 'F', '24'),
    ('3', 'McMullen', 'Eric', '763 Church St.', 'Lemon Grove', 'CA', '619', '555-0133', 'Sales Worker', '11/1/2002', '$13,500.00', 'M', '20'),
    ('4', 'Carpenter', 'Donald', '927 Second St.', 'Encinitas', 'CA', '619', '555-0108', 'Operatives(Semi-Skilled)', '11/1/2003', '$15,000.00', 'M', '18'),
    ('5', 'Esquivez', 'David', '10983 N. Coast Highway Apt.902', 'Encinitas', 'CA', '760', '555-0154', 'Office/Clerical', '7/25/2003', '$18,500.00', 'M', '25'),
    ('6', 'Sharp', 'Nancy', '10793 Montecino Rd', 'Ramona', 'CA', '858', '555-0135', 'Sales Workers', '7/12/2003', '$21,000.00', 'F', '24')
    Insert Into table006
    (ExemptStatus, EEO1, EmployeeID, JobTitle, JobDescription)
    Values
    ('0', 'Officials & Managers', '1', 'Assistant Manager', 'Supervises and coordinates activities of workers in department of food store. Assists store manager in daily operations of store.'),
    ('1', 'Sales Worker(p)', '2', 'Bagger - 30 hours/wk', 'Places customer orders in bags. Performs carryout duties for customers.'),
    ('1', 'Sales Worker', '3', 'Bagger', 'Places customer orders in bags. Performs carryout duties for customers.'),
    ('1', 'Office/Clerical', '4', 'Stocker', 'Stores, prices and restocks merchandise displays in store.'),
    ('1', 'Operatives(Semi-Skilled)', '5', 'Asst. Butchers & Seafood Specialists', 'Provides assistance and training to Butchers & Seafood Department.'),
    ('1', 'Office/Clerical', '6', 'Accounting Clerk', 'Computes, classifies, records, and verifies numerical data for use in maintaining accounting records.'),
    ('1', 'Technician', '', 'Computer Support Specialist', 'Installs, modifies, and makes minor repairs to personal computer hardware and software systems, and provides technical assistance and training to system users.'),
    ('0', 'Officials & Managers', '', 'Director of Finance and Accounting', 'Plans and directs the finance and accounting activities for Kudler Fine Foods.'),
    ('1', 'Craft Workers(Skilled)', '', 'Retail Assistant Bakery and Pastry', 'Obtains or prepares food items requested by customers in retail food store.')

     

     

     

    Wednesday, October 20, 2010 9:15 AM

Answers

  • An Identity property generates automaticaly seq numbers in the table and  you cannot insert into explicity

    However , if you want to keep them anyway you need to use the below BOL example

    Examples

    This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

    -- Create products table.
    CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
    GO
    -- Inserting values into products table.
    INSERT INTO products (product) VALUES ('screwdriver')
    INSERT INTO products (product) VALUES ('hammer')
    INSERT INTO products (product) VALUES ('saw')
    INSERT INTO products (product) VALUES ('shovel')
    GO
    
    -- Create a gap in the identity values.
    DELETE products 
    WHERE product = 'saw'
    GO
    
    SELECT * 
    FROM products
    GO
    
    -- Attempt to insert an explicit ID value of 3;
    -- should return a warning.
    INSERT INTO products (id, product) VALUES(3, 'garden shovel')
    GO
    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT products ON
    GO
    
    -- Attempt to insert an explicit ID value of 3
    INSERT INTO products (id, product) VALUES(3, 'garden shovel').
    GO
    
    SELECT * 
    FROM products
    GO
    -- Drop products table.
    DROP TABLE products
    GO
    

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by GURSETHI Saturday, October 23, 2010 5:01 AM
    • Marked as answer by Tom Li - MSFT Monday, November 1, 2010 8:39 AM
    Wednesday, October 20, 2010 9:20 AM

All replies

  • An Identity property generates automaticaly seq numbers in the table and  you cannot insert into explicity

    However , if you want to keep them anyway you need to use the below BOL example

    Examples

    This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

    -- Create products table.
    CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
    GO
    -- Inserting values into products table.
    INSERT INTO products (product) VALUES ('screwdriver')
    INSERT INTO products (product) VALUES ('hammer')
    INSERT INTO products (product) VALUES ('saw')
    INSERT INTO products (product) VALUES ('shovel')
    GO
    
    -- Create a gap in the identity values.
    DELETE products 
    WHERE product = 'saw'
    GO
    
    SELECT * 
    FROM products
    GO
    
    -- Attempt to insert an explicit ID value of 3;
    -- should return a warning.
    INSERT INTO products (id, product) VALUES(3, 'garden shovel')
    GO
    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT products ON
    GO
    
    -- Attempt to insert an explicit ID value of 3
    INSERT INTO products (id, product) VALUES(3, 'garden shovel').
    GO
    
    SELECT * 
    FROM products
    GO
    -- Drop products table.
    DROP TABLE products
    GO
    

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by GURSETHI Saturday, October 23, 2010 5:01 AM
    • Marked as answer by Tom Li - MSFT Monday, November 1, 2010 8:39 AM
    Wednesday, October 20, 2010 9:20 AM
  • Ok, Thank you for your help. I have another question that might not be as complex. The code I am tryin g to use is:

    SELECT LastName, COUNT(LastName)
    FROM employee2
    WHERE Salary = '$48,000.00''$10,530.00''$13,500.00''$15,000.00''$18,500.00''$21,000.00''$25,500.00''$43,000.00'
    GROUP BY Salary

    Keep getting this error:

    Msg 8120, Level 16, State 1, Line 1
    Column 'employee2.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    It is contained by an aggregate function and by the GROUP BY clause so what is the problem here? I have tried several customizations and this is the closest to correct I think but anything I do to the first line creates different or multiple different errors. What do you think? Thanks in advance!

    Saturday, October 23, 2010 6:46 AM
  • What exactly are you trying to do? Try something like:

    SELECT Salary, COUNT(*) 
    FROM employee2 
    WHERE Salary in (48000.00,10530.00,13500.00,15000.00,18500.00,21000.00,25500.00,43000.00) 
    GROUP BY Salary
    

    or

    ;with CTE(Salary, Cnt)
    as
    (
     SELECT Salary, COUNT(*) 
     FROM employee2 
     WHERE Salary in  (48000.00,10530.00,13500.00,15000.00,18500.00,21000.00,25500.00,43000.00) 
     GROUP BY Salary
    )
    select e.LastName, cte.Salary, cte.Cnt
    from Enployee2 e join CTE on 
     e.Salary = cte.Salary
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Saturday, October 23, 2010 1:59 PM
  • Hi

    Do you store Salary info as VARCHAR(n)? Why? See Dmitri's solutions


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 24, 2010 7:46 AM
  • Hi

    Do you store Salary info as VARCHAR(n)? Why? See Dmitri's solutions


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 24, 2010 7:46 AM