Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,798

    Unanswered: Insert Statement

    Hello everyone long time no see I hope everyone is doing well.

    I have an insert statement that I need to make sure is correct. Its been awhile since I've done one. the table that I'm inserting the lost data into a table that has well over 100,000 records, the table that I'm pulling data from has 446 records i just want to make sure I insert all of them with no errors

    Code:
    SET IDENTITY_INSERT dbo.TanfActivity_tbl on
    
    INSERT INTO dbo.TanfActivity_tbl
                            (ID, EventDate, [Parent ID], PurposeofContact, TypeofContact, ReferralDate, Earnedhours, ServicesCovered, Catagoryforhours, StateCatagory, Month, 
                            Week, StateServicesCovered, TanfID, PeopleID, ScairCaseWorker, MERCodes, NameofWorkshop, EventName, EventLocation, Education, Culture, 
                            Technology, Prevention, Safety, LifeSkills, YouthLeadership, Health_Fitness, TypingSpeed, MaximumScore, PassingScore, ParticipantsScore, 
                            AbsentDate, PracticeTestMode, CompletionPercentage, TypingAccuracy, TravelTime, Absent)
    SELECT      ID, EventDate, [Parent ID], PurposeofContact, TypeofContact, ReferralDate, Earnedhours, ServicesCovered, Catagoryforhours, StateCatagory, Month, 
                            Week, StateServicesCovered, TanfID, PeopleID, ScairCaseWorker, MERCodes, NameofWorkshop, EventName, EventLocation, Education, Culture, 
                            Technology, Prevention, Safety, LifeSkills, YouthLeadership, Health_Fitness, TypingSpeed, MaximumScore, PassingScore, ParticipantsScore, 
                            AbsentDate, PracticeTestMode, CompletionPercentage, TypingAccuracy, TravelTime, Absent
    FROM          dbo.EstradaTanfActive
    
    SET IDENTITY_INSERT dbo.TanfActivity_tbl on

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Run the select portion and ensure it has all the data you expect it to have. Other than that we couldn't really tell you if it is ok, not having the structure and data type definition.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    For safety, you can wrap this in a begin transaction/rollback transaction block. Keep in mind, of course, that you will be potentially blocking other users until you hit the commit or the rollback:

    Code:
    BEGIN TRANSACTION
    
    SET IDENTITY_INSERT dbo.TanfActivity_tbl on
    
    INSERT INTO dbo.TanfActivity_tbl
                            (ID, EventDate, [Parent ID], PurposeofContact, TypeofContact, ReferralDate, Earnedhours, ServicesCovered, Catagoryforhours, StateCatagory, Month, 
                            Week, StateServicesCovered, TanfID, PeopleID, ScairCaseWorker, MERCodes, NameofWorkshop, EventName, EventLocation, Education, Culture, 
                            Technology, Prevention, Safety, LifeSkills, YouthLeadership, Health_Fitness, TypingSpeed, MaximumScore, PassingScore, ParticipantsScore, 
                            AbsentDate, PracticeTestMode, CompletionPercentage, TypingAccuracy, TravelTime, Absent)
    SELECT      ID, EventDate, [Parent ID], PurposeofContact, TypeofContact, ReferralDate, Earnedhours, ServicesCovered, Catagoryforhours, StateCatagory, Month, 
                            Week, StateServicesCovered, TanfID, PeopleID, ScairCaseWorker, MERCodes, NameofWorkshop, EventName, EventLocation, Education, Culture, 
                            Technology, Prevention, Safety, LifeSkills, YouthLeadership, Health_Fitness, TypingSpeed, MaximumScore, PassingScore, ParticipantsScore, 
                            AbsentDate, PracticeTestMode, CompletionPercentage, TypingAccuracy, TravelTime, Absent
    FROM          dbo.EstradaTanfActive
    
    SET IDENTITY_INSERT dbo.TanfActivity_tbl on
    
    -- run checks here to make sure it looks right.
    
    -- If it does not look right, uncomment and run:
    --ROLLBACK TRANSACTION
    
    -- if it does look right, uncomment and run:
    --COMMIT TRANSACTION

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,798
    HI guys thank you again I ran it but Im getting a foreign key, the only thing is I know that the table is missing 446 records

    Msg 547, Level 16, State 0, Line 5
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TanfActivity_tbl_People_tbl". The conflict occurred in database "SCAIR", table "dbo.People_tbl", column 'Parent ID'.
    The statement has been terminated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Try this, then:

    Code:
    select t.[Parent_ID]
    from TanfActivity_tbl t left join
        People_tbl p on t.[Parent ID] = p.[Parent ID]
    where p.[Parent ID] is null
    This will show you what parent IDs in the TanfActivity table do not exist in the People_tbl table (that should send Blindman around the bend ;-)) Play around with this query, and make sure that the Parent IDs match up correctly between this table, and the parent table.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,798
    thank you McCrowley that did the trick there was 4 records

Posting Permissions

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