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

Follow JasonBussCFX on Twitter

ABOUT THE AUTHOR

Jason Buss

Jason is a senior application developer with Customer FX.

Submit a Comment

Your email address will not be published.

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!