Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Need Help Inserting Data in a table that already has data

    I need to create a stored procedure that will insert data with some already exsisting data in a table. The data is in a spreadsheet, my issue is that I dont want to violate the primary key rules.

    can anyone help please

    Code:
    CREATE PROCEDURE InsertTerms
    AS
    INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
    	 [FirstName],
    	 [LastName],
    	 [SocialSecurityNumber],
    	 [DateHired],
    	 [Status],
    	 [Title],
    	 [DepartmentName],
    	[Pictures]) 
    
    SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
    FROM  EmployeeGamingLicense AS a
    WHERE a.STATUS = 'TERMINATED'
    IF @@Error <> '0'
    RETURN
    
    
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What is the primary key? My first guess would be [TM #], but I'm not sure.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Hi Pat

    How was your new years, how was your christmas???

    the primary key is TM#

  4. #4
    Join Date
    Dec 2005
    Posts
    39
    the SP doesn't have any input? i mean how are you going to read data from the xls. in any case can you try with exists clause -

    if not exists(select 1 from <table> where <pkfield> = <data>)
    insert into table values(...)

    check for existence of key values, if not existing then insert. hope this helps.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE PROCEDURE InsertTerms
    AS
    
    INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] (
       [TM #], [FirstName], [LastName]
    ,  [SocialSecurityNumber], [DateHired], [Status]
    ,  [Title], [DepartmentName],[Pictures]
       ) SELECT
       a.[TM#], a.FirstName, a.LASTNAME
    ,  a.[SSN#], a.HIREDATE, a.STATUS
    ,  a.JOBTITLE, a.[DEPT#], a.PICS
       FROM  EmployeeGamingLicense AS a
       WHERE a.STATUS = 'TERMINATED'
          AND NOT EXISTS (SELECT *
             FROM [GamingCommissiondb].[dbo].[TERMINATION] AS z
             WHERE z.[TM #] = a.[TM #])
    
    RETURN
    GO
    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking The Spreadsheet

    the spreadsheet is called DesireeTerm3 and it has the fields I need
    Employee = (TM#), REASON AND DESCRIPTION. I just need to import those fields over to the TERMINATION table without getting a primary key error does that make sense??

    Code:
     [TERMINATION] (
       [TM #], [FirstName], [LastName]
    ,  [SocialSecurityNumber], [DateHired], [Status]
    ,  [Title], [DepartmentName],[Pictures]

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    I just need to import those fields over to the TERMINATION table without getting a primary key error does that make sense??
    Yep.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Pat!!

    So tell me how are you doing??

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    So tell me how are you doing??
    I've been having significantly more fun that what any one human being ought to be allowed. Work has been more or less insane since 2005-08-01, with little prospect of things letting up before 2006-06-15. Home has been the usual insanity, one kid moved out, another broke a heel. About the same as usual, just faster than ever before.

    So how about you? You've been scarce around here lately. I figured you'd switched to Oracle or something like that.

    As a side note, did that code snippet fix your INSERT problem?

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Hows this

    ok hows this look?? This ok??

    Code:
    CREATE PROCEDURE InsertTermsReasons
    AS
    
    INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] (
       [TM #], [Reasonfortermination]
       ) SELECT
       a.[Employee], a.DESCRIPTION
       FROM  DesireeTerm3 AS a
       WHERE a.ACTION_CODE = 'TERM'
          AND NOT EXISTS (SELECT *
             FROM [GamingCommissiondb].[dbo].[TERMINATION] AS z
             WHERE z.[TM #] = a.[TM #])
    
    RETURN
    GO

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Looks lovely to me.

    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ack!!

    getting an error message thought

    Code:
    Server: Msg 207, Level 16, State 3, Procedure InsertTermsReasons, Line 4
    Invalid column name 'TM #'.

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy Ok got it now

    only one problem I executed it and zero rows were affected

    Code:
    (0 row(s) affected)
    
    Stored Procedure: Test.dbo.InsertTermsReasons
    	Return Code = 0
    HELP PLEASE???

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    HELP PLEASE???
    It is a bit tough to help with problems like this without access to the database and the spreadsheet. There are so many of the subtle issues that can "clog the works" that it is practically impossible to help remotely.

    Is it possible that you've already run the insert successfully once, so no more data exists to insert?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •