Use the below scripts to add more columns in the extended tables when the columns are exhausted in the Extension table.


Supported Datatypes Unique Identifier 



Int

0CB87B97-1064-4BD5-9446-19F63CB44913

Date 

904DA674-2436-43C3-BE5E-422B46D73C84

Pickup Field

0419E788-0182-4F85-A91C-5B13FC71DE85

Varchar

0886B5C3-AC29-495B-891F-A3518080F119

Float 

18DD64F3-330A-40CE-8777-C8A0256F6DF6

Boolean 

7A31DC0A-6E6E-4284-88C1-CEF92AA54782



   

DECLARE
 @columnMetaDataId uniqueidentifier,
 @entityId varchar(50),
 @fieldType uniqueidentifier,
 @tableName varchar(50),
 @additionalColumns int,
 @totalNumberOfColumns int
set @entityId = N'Todo'  -- Entity Identifier
set @tableName = N'Todoes' -- Entity table name without schema
set @additionalColumns = 5   -- Additional number of columns
set @fieldType = '0CB87B97-1064-4BD5-9446-19F63CB44913' -- Datatype identifier for field
USE [CelloSaaSDb] -- Replace with CelloSaaS  Database Name
set @columnMetaDataId = (SELECT [EntityExtnColumnMetadata_ID] FROM [EntityExtnColumnMetadata] WHERE [EntityExtnColumnMetadata_EntityId]= @entityId AND [DataFieldType_ID] = @fieldType)
set @totalNumberOfColumns = (SELECT [EntityExtnColumnMetadata_NoOfColumn]+@additionalColumns FROM [EntityExtnColumnMetadata] WHERE [EntityExtnColumnMetadata_ID] = @columnMetaDataId)
UPDATE [EntityExtnColumnMetadata] SET [EntityExtnColumnMetadata_NoOfColumn] = @totalNumberOfColumns WHERE [EntityExtnColumnMetadata_ID] = @columnMetaDataId
--INSERT QUERY(FOR ADDITIONAL 5 ROWS)[Change the newly created column names from extn table in the following scripts]
INSERT [dbo].[EntityExtnColumnDetails] ([EntityExtnColumnDetails_EntityId], [EntityExtnColumnDetails_ColumnMetadataId], [EntityExtnColumnDetails_ColumnName], [EntityExtnColumnDetails_CreatedBy], [EntityExtnColumnDetails_CreatedOn], [EntityExtnColumnDetails_UpdatedBy], [EntityExtnColumnDetails_UpdatedOn], [EntityExtnColumnDetails_Status]) VALUES (@entityId, @columnMetaDataId, @tableName+N'Extn_Column41', N'Admin', GETDATE(), NULL, NULL, 1)
INSERT [dbo].[EntityExtnColumnDetails] ([EntityExtnColumnDetails_EntityId], [EntityExtnColumnDetails_ColumnMetadataId], [EntityExtnColumnDetails_ColumnName], [EntityExtnColumnDetails_CreatedBy], [EntityExtnColumnDetails_CreatedOn], [EntityExtnColumnDetails_UpdatedBy], [EntityExtnColumnDetails_UpdatedOn], [EntityExtnColumnDetails_Status]) VALUES (@entityId, @columnMetaDataId, @tableName+N'Extn_Column42', N'Admin', GETDATE(), NULL, NULL, 1)
INSERT [dbo].[EntityExtnColumnDetails] ([EntityExtnColumnDetails_EntityId], [EntityExtnColumnDetails_ColumnMetadataId], [EntityExtnColumnDetails_ColumnName], [EntityExtnColumnDetails_CreatedBy], [EntityExtnColumnDetails_CreatedOn], [EntityExtnColumnDetails_UpdatedBy], [EntityExtnColumnDetails_UpdatedOn], [EntityExtnColumnDetails_Status]) VALUES (@entityId, @columnMetaDataId, @tableName+N'Extn_Column43', N'Admin', GETDATE(), NULL, NULL, 1)
INSERT [dbo].[EntityExtnColumnDetails] ([EntityExtnColumnDetails_EntityId], [EntityExtnColumnDetails_ColumnMetadataId], [EntityExtnColumnDetails_ColumnName], [EntityExtnColumnDetails_CreatedBy], [EntityExtnColumnDetails_CreatedOn], [EntityExtnColumnDetails_UpdatedBy], [EntityExtnColumnDetails_UpdatedOn], [EntityExtnColumnDetails_Status]) VALUES (@entityId, @columnMetaDataId, @tableName+N'Extn_Column44', N'Admin', GETDATE(), NULL, NULL, 1)
INSERT [dbo].[EntityExtnColumnDetails] ([EntityExtnColumnDetails_EntityId], [EntityExtnColumnDetails_ColumnMetadataId], [EntityExtnColumnDetails_ColumnName], [EntityExtnColumnDetails_CreatedBy], [EntityExtnColumnDetails_CreatedOn], [EntityExtnColumnDetails_UpdatedBy], [EntityExtnColumnDetails_UpdatedOn], [EntityExtnColumnDetails_Status]) VALUES (@entityId, @columnMetaDataId, @tableName+N'Extn_Column45', N'Admin', GETDATE(), NULL, NULL, 1)