Updating table IDs in SageCRM using SQL

I just finished work on an import project into SageCRM, using SQL Server Integration services to move data from a couple of Excel Spreadsheets into the database.  One of the items I had to figure out was how to populate tableIDs for the imported records.

The primary keys in SageCRM don’t have a uniqueness constrant, so I initially just imported a “1” into the ID fields. After all the data was imported, It was just a matter of querying the custom_tables and SQL_identity tables in SageCRM to find the next usable id, then loop through the records where the table id = “1” to update each ID value.

Here’s a simple script to to just that:

DECLARE @CurrentTableID INT

Set @CurrentTableID = (SELECT bord_tableid from SageCRM.dbo.custom_tables where bord_caption = ‘CustomTable’)

DECLARE @CurrentID INT
Set @CurrentID = (SELECT Id_NextId from SageCRM.dbo.Sql_Identity where ID_TableID = @CurrentTableID)

DECLARE @RowCount INT
Set @RowCount = (SELECT COUNT(*) FROM SageCRM.dbo.CustomTable where CustomTable_CustomTableID = 1)
DECLARE @I INT
Set @I = 1
While (@I <= @RowCount)
Begin
    
    Update top (1) SageCRM.dbo.CustomTable
    Set CustomTable_CustomTableID = @CurrentID
    Where CustomTable_CustomTableID = 1
    Set @CurrentID = @CurrentID + 1
    Set @I = @I + 1
End

Update SageCRM.dbo.SQL_Identity set id_NextID = @CurrentID where ID_TableID = @CurrentTableID

Probably not the most efficent way of updating IDs in SageCRM, but it works pretty well for smaller datasets.

-Jason