locked
Insert error while migrating data from sql to mysql server RRS feed

  • Question

  • USE [LTC_Reference]
    GO
    /****** Object:  StoredProcedure [dbo].[proc_ltcmigrate_residents]    Script Date: 07/15/2012 01:48:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[proc_ltcmigrate_residents]
    AS
    BEGIN

    /*
    PersonFriendlyId is ported as NULL.
    FacilityId is determined based on the following rules in order
    a. If there is a valid facilityid in ltcperson that is taken
    b. Check the last bed activity in ltcbedschedule and choose that facility
    c. Check the last batch activity and choose that facility in batch
    d. default to 1
    */

    Insert lee_mig...ltcperson
    (
    ltcPersonID
    ,FirstName
    ,LastName
    ,PersonFriendlyID
    ,ApplicableAdmissionDate
    ,Archived
    ,ArchivedDate
    ,ArchivedByUserID
    ,ProgressNoteDays
    ,ltcOrganisationID
    ,oldPersonID
    ,CreatedBy 
    ,CreatedDate 
    )
    SELECT 
     P.PersonID
    ,FirstName
    ,LastName
    ,PersonFriendlyID  
    ,ApplicableAdmissionDate
    ,Archived
    ,ArchivedDate
    ,case when ArchivedByUserID=-1 then null else ArchivedByUserID end
    ,ProgressNoteDays
    ,COALESCE(case when P.FacilityID>0 then P.FacilityID else null end, LastActiveFacility.FacilityId, (select top 1 FacilityID from ltcbatch b where b.PersonId=P.personid and FacilityID>0 and FacilityID is not null order by BatchID desc), 1)
    ,OldPersonID
    ,isnull((select top 1 userid from ltcbatch b where b.PersonId=P.personid and b.UserID<>0 order by BatchID),1)
    ,(select top 1 DateCreated from ltcbatch b where b.PersonId=P.personid order by BatchID)
    FROM ltcPerson P
    left join
    (
       SELECT DISTINCT v.OrgID FacilityId, LBS.PersonID from ltcBedSchedule LBS inner join 
       (Select Max(ID) ID, PersonID from ltcbedschedule where PersonID>0 and BedPK>0 group by PersonID) MLBS on LBS.ID=MLBS.ID
       inner join vwBeds v on v.BedPK=LBS.BedPK
    )  LastActiveFacility on LastActiveFacility.PersonID=P.PersonID
    where exists(select * from ltcBatch b where P.personid=b.personid)


    insert lee_mig...ltcbeds
    (
    BedPK
    ,RoomID
    ,PersonID
    ,BedName
    ,Status
    ,DateFrom
    ,BedGPSX
    ,BedGPSY
    )

    select

    BedPK
    ,RoomID
    ,case when PersonID <=0 then null else PersonID end
    ,BedName
    ,Status
    ,DateFrom
    ,BedGPSX
    ,BedGPSY

    from ltcbeds

    exec Pre_ltcMig_Proc_Create_Migration_ProcessSlave  @processID=4

    END

    I am getting the following error message while running the proc.

    OLE DB provider "MSDASQL" for linked server "lee_mig" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.12]Cannot add or update a child row: a foreign key constraint fails (`ltcdb_prod`.`ltcperson`, CONSTRAINT `fk_ltcperson_ltcuser2` FOREIGN KEY (`CreatedBy`) REFERENCES `ltcuser` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION)".

    Any help would be greatly appreciated. :)

    Saturday, July 14, 2012 4:38 PM

Answers

  • None of those tables have foreign ken constraint defined on them.  How did you script them out?

    Make sure that option is set to true under scripting.  If you do have it set to true (Default), then it must be the migration wizard creating the FK on the MySQL side, in which case.  I am out sorry, I don't have MySQL, so I can't tell you what settings to change in the wizard :(.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    • Marked as answer by Maggie Luo Thursday, August 9, 2012 11:15 PM
    Monday, July 16, 2012 4:19 PM

All replies

  • From the error FK check is failing.  So you are trying to insert something in table ltcperson, that is making reference to table ltcuser; but the user does not exist.  Therefore the insert fails.

    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Sunday, July 15, 2012 4:40 PM
  • Hi Mohit

    Yes I understand this problem. See the thing is that I am migrating data from sql server to MySQL server. I understand there are many user in the ltcbatch table which do not exist in ltcuser table. I tried to search the row relating to it and there were 5500 rowes returned. So I need to workaround this problem. Please let me know if u need any more details. :)


    chandan13

    Monday, July 16, 2012 1:25 AM
  • I don't have MySQL to experiment with, but I would investigate two options.

    Create the missing keys even for dummy fillers in SQL Server before you migrate from SQL Server to MySQL.

    SELECT DISTINCT CreatedBy as UserID
      FROM LTCPERSON lp
    RIGHT JOIN LTCUSER u ON lp.CreatedBy = u.UserID
    WHERE u.UserID IS NULL

    The above SQL Statement should give you all missing IDs.

    After which you can insert those IDs back into the LTCUSER table.

    If the table is identity key, you can use "SET IDENTITY_INSERT Schema.TableName [ON|OFF]".

    ....

    If that is not possible, then maybe drop the FK constraint.

    ....

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Monday, July 16, 2012 1:46 AM
  • USE [LTC_Reference2]
    GO
    
    /****** Object:  Table [dbo].[ltcBatch]    Script Date: 07/16/2012 16:19:19 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[ltcBatch](
    	[BatchID] [int] IDENTITY(1,1) NOT NULL,
    	[UserID] [int] NOT NULL,
    	[PersonID] [int] NOT NULL,
    	[DateCreated] [datetime] NOT NULL,
    	[ParentID] [int] NOT NULL,
    	[FormName] [varchar](50) NOT NULL,
    	[Resolved] [bit] NOT NULL,
    	[RecordID] [int] NOT NULL,
    	[FacilityID] [int] NOT NULL,
     CONSTRAINT [PK_ltcBatch] PRIMARY KEY CLUSTERED 
    (
    	[BatchID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[ltcBatch] ADD  CONSTRAINT [DF_ltcBatch_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
    GO
    
    ALTER TABLE [dbo].[ltcBatch] ADD  CONSTRAINT [DF_ltcBatch_Resolved]  DEFAULT (0) FOR [Resolved]
    GO
    
    ALTER TABLE [dbo].[ltcBatch] ADD  CONSTRAINT [DF_ltcBatch_RecordID]  DEFAULT (0) FOR [RecordID]
    GO
    
    ALTER TABLE [dbo].[ltcBatch] ADD  CONSTRAINT [DF_ltcBatch_FacilityID]  DEFAULT ((0)) FOR [FacilityID]
    GO
    
    
    



    chandan13

    Monday, July 16, 2012 6:22 AM
  • USE [LTC_Reference2]
    GO
    
    /****** Object:  Table [dbo].[ltcPerson]    Script Date: 07/16/2012 16:23:58 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[ltcPerson](
    	[PersonID] [int] IDENTITY(1,1) NOT NULL,
    	[FirstName] [varchar](50) NOT NULL,
    	[LastName] [varchar](50) NOT NULL,
    	[PersonFriendlyID] [varchar](50) NULL,
    	[ApplicableAdmissionDate] [datetime] NULL,
    	[Archived] [bit] NOT NULL,
    	[ArchivedDate] [datetime] NULL,
    	[ArchivedByUserID] [int] NOT NULL,
    	[FacilityID] [int] NOT NULL,
    	[OldPersonID] [int] NULL,
    	[ProgressNoteDays] [int] NULL,
     CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
    	[PersonID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'CS_ChildObject1', @value=N'BO_Person_Details_Child Person_Details_UID tbl_Person_Details true
    ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ltcPerson'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'CS_ObjectMetaType1', @value=N'EditableRoot BO_Person
    ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ltcPerson'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'CS_ObjectMetaType2', @value=N'NameValueList BO_PersonNVL Person_UID LastName
    ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ltcPerson'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'CS_ObjectMetaType3', @value=N'ReadOnlyCollection BO_PersonCollectionRO tbl_Person Person_UID
    ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ltcPerson'
    GO
    
    ALTER TABLE [dbo].[ltcPerson] ADD  CONSTRAINT [DF_ltcPerson_Archived]  DEFAULT (0) FOR [Archived]
    GO
    
    ALTER TABLE [dbo].[ltcPerson] ADD  CONSTRAINT [DF_ltcPerson_ArchivedByUserID]  DEFAULT ((-1)) FOR [ArchivedByUserID]
    GO
    
    ALTER TABLE [dbo].[ltcPerson] ADD  CONSTRAINT [DF_ltcPerson_FacilityID]  DEFAULT (0) FOR [FacilityID]
    GO
    
    ALTER TABLE [dbo].[ltcPerson] ADD  CONSTRAINT [DF_ltcPerson_OldPersonID]  DEFAULT (0) FOR [OldPersonID]
    GO
    
    


    chandan13

    Monday, July 16, 2012 6:23 AM
  • USE [LTC_Reference2]
    GO
    
    /****** Object:  Table [dbo].[ltcUser]    Script Date: 07/16/2012 16:24:39 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[ltcUser](
    	[UserID] [int] IDENTITY(1,1) NOT NULL,
    	[UserName] [varchar](25) NOT NULL,
    	[FirstName] [varchar](25) NOT NULL,
    	[LastName] [varchar](25) NOT NULL,
    	[Email] [varchar](50) NULL,
    	[BusPhone] [varchar](20) NULL,
    	[Comments] [varchar](255) NULL,
    	[UserPwd] [varchar](100) NULL,
    	[ComputerName] [varchar](50) NULL,
    	[UserRoleID] [numeric](18, 0) NOT NULL,
    	[CustomMenu] [int] NULL,
    	[Active] [bit] NOT NULL,
    	[CreatedDate] [datetime] NULL,
    	[CreatedUserID] [numeric](18, 0) NULL,
    	[OldUserID] [int] NULL,
    	[Classification] [varchar](200) NULL,
    	[LastLogin] [datetime] NOT NULL,
    	[FacilityID] [int] NOT NULL,
     CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED 
    (
    	[UserID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[ltcUser] ADD  CONSTRAINT [DF_ltcUser_LastLogin]  DEFAULT (1 / 1 / 1900) FOR [LastLogin]
    GO
    
    ALTER TABLE [dbo].[ltcUser] ADD  CONSTRAINT [DF_ltcUser_FacilityID]  DEFAULT (1) FOR [FacilityID]
    GO
    
    



    chandan13

    Monday, July 16, 2012 6:24 AM
  • HI Mohit 

    Thnks for replying. I have listed the tables here. pls check through it. To find the userid which does not exist in ltc user I executed the following query.

    Select distinct UserID from ltcbatch where UserID not  in (select UserID from ltcuser)


    chandan13


    • Edited by jgd13 Monday, July 16, 2012 7:01 AM
    Monday, July 16, 2012 6:27 AM
  • None of those tables have foreign ken constraint defined on them.  How did you script them out?

    Make sure that option is set to true under scripting.  If you do have it set to true (Default), then it must be the migration wizard creating the FK on the MySQL side, in which case.  I am out sorry, I don't have MySQL, so I can't tell you what settings to change in the wizard :(.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    • Marked as answer by Maggie Luo Thursday, August 9, 2012 11:15 PM
    Monday, July 16, 2012 4:19 PM