Patch multiple records to SQL Server

[PVosEska]

Hi guys,

I created an app months ago, that implements the following formula:

With(
    {
        newUploadTable: DropColumns(
            Filter(
                samMeasurementsCollection,
                new = 1
            ),
            "new",
            "edit",
            "del",
            "MeasurementID",
            "fromdb"
        )
    },
    Patch(
        '[dbo].[SampleMeasurements]',
        newUploadTable
    )
);

- It creates a temporary table 'newUploadTable' where I remove some columns to mirror the Database,

- Patch all items to the database

All items get patched at the same time, so it takes seconds. Works super well.

My current issue is that i'm trying to do the same thing in another app, but can't seem to make it work. The current formula:

With(
    {
        i: DropColumns(
            AddColumns(
                Filter(
                    Fcts,
                    selected = 1
                ),
                "RoleID",
                thisRole.roleID
            ),
            "selected"
        )
    },
    Patch(
        'ate.RoleFact',
        i
    )
);

- create temp table 'i',

- remove 1 column.

- Patch to DB 

Strange enough, I keep getting an error: Server response: <Tablename> failed: Item with id '**,<record>' does not exist.

Does anyone know of any limitations with different SQL Servers or versions?

Let me know if you have any tips!

Best regards,

[StalinPonnusamy]

Hi @PVosEska 

By Looking at the error message the temp table "I" has a column called id which does not exist in the SQL table.

To troubleshoot the issue,

 on the button, click set the below code to verify the names of the columns that match the SQL table

ClearCollect(ColTempi,DropColumns(
            AddColumns(
                Filter(
                    Fcts,
                    selected = 1
                ),
                "RoleID",
                thisRole.roleID
            ),
            "selected"
        ))

Will remove the columns is not needed or not exists.

[PVosEska]

Hi @StalinPonnusamy ,

Sorry for the late response. I tried your troubleshooting test, it had the same result.

I also tried with a custom made table, where is tried patching the following table:

ClearCollect(
    tempTable,
    {
        RoleID: 1000,
        FactName: "TEST1"
    },
    {
        RoleID: 1000,
        FactName: "TEST2"
    },
    {
        RoleID: 1000,
        FactName: "TEST3"
    }
);
Patch(
    'ate.RoleFact',
    tempTable
)

Same result.

I do have to mention btw, that every row should be a unique combination. Both RoleID and FactName are both PK fields, so no duplicate combination can occur. 

This is not the case however, just something I wanted to mention.

Regards,

Peter 

[StalinPonnusamy]

For SQL Table, We need an Identity column to make a patch command works. I tried this case, Patch to Primary field is not works for me. 

But created Primary field on Identity column with the SQL like

CREATE TABLE [dbo].[RoleFact](
    [RoleFactID] [int] IDENTITY(1,1) NOT NULL,
	[RoleID] [int] NOT NULL,
	[FactName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[RoleFactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Patch command works like

ClearCollect(
    tempTable,
    {
        RoleID: 1000,
        FactName: "TEST1"
    },
    {
        RoleID: 1000,
        FactName: "TEST2"
    },
    {
        RoleID: 1000,
        FactName: "TEST3"
    }
);
ForAll(
    tempTable,
    Patch(
        RoleFact,
        Defaults(RoleFact),
        {
            RoleID: ThisRecord.RoleID,
            FactName: ThisRecord.FactName
        }
    )
)

[PVosEska · Accepted solution]

@StalinPonnusamy ,

So patching multiple items at the same time only works when there is an identity column.

I already implemented the ForAll() function around the patch, exactly like you did;

ForAll(
    myTable,
    Patch(
        mySource,
        Defaults(mySource),
        {
            myField1: myValue1,
            myField2: myValue2
        }
    )
)

This will however upload every row individually, and is not optimal for larger amounts.

Gave me an idea to create a progressbar though! The user can at least see something move while they wait 🙂

Regards and thanks for you help!

Peter

[StalinPonnusamy]

Hi @PVosEska 

I just tested this and works.

ClearCollect(
    tempTable,
    {
        RoleID: 1000,
        FactName: "TEST1"
    },
    {
        RoleID: 1000,
        FactName: "TEST2"
    },
    {
        RoleID: 1000,
        FactName: "TEST3"
    }
);
Collect(
    RoleFact,
    tempTable
)