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)
Update top (1) SageCRM.dbo.CustomTable
Set CustomTable_CustomTableID = @CurrentID
Where CustomTable_CustomTableID = 1
Set @CurrentID = @CurrentID + 1
Set @I = @I + 1
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.