Hello friends I had created a stored procedure to upload data from power apps to sql server,Here the data is not passing into da_tracker2 table from temporary table split ,Is their any way which I can Insert the data into da_tracker2

Question

USE [AUTPROC]
GO
/****** Object: StoredProcedure [dbo].[sp_datracker_storedproc] Script Date: 9/17/2021 3:08:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_datracker_storedproc]
@Task_No int,
@tree nvarchar(500)=null,
@Requestor nvarchar(500)=null,
@Creator nvarchar(200)=null,
@START _date nvarchar(500)=null,
@Lucas Santos _date nvarchar(500)=null,
@Sta nvarchar(500)=null,
@Task_Type nvarchar(500)=null,
@Identifier nvarchar(500)=null,
@Comment nvarchar(max)=null,
@Commodity nvarchar(500)=null,
@Supplier _name nvarchar(500)=null
AS
BEGIN
SET NOCOUNT ON
--begin

--IF NOT EXISTS (SELECT * FROM DAtrackertest WHERE [Team] = @tree and [Start_Date]=@START _date and [Status]=@Sta and [Task_Type]=@Task_Type and [Identifier]=@Identifier and [Supplier_name]=@Supplier _name)
INSERT INTO DAtrackertest
(Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name)
VALUES(@tree ,@Requestor,@Creator,@START _date,@Lucas Santos _date,@Sta ,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier _name)

declare @datrackertestcount int
set @datrackertestcount = (select count (*) from DAtrackertest)
if (@datrackertestcount > 1)
begin

DECLARE @delim VARCHAR(4000) = char(10);
WITH split AS (
select
CONVERT(nvarchar(max), CONCAT(Team, @delim)) AS Team,
CONVERT(nvarchar(max), CONCAT(Requestor, @delim)) AS Requestor,
CONVERT(nvarchar(max), CONCAT(Creator, @delim)) AS Creator,
CONVERT(nvarchar(max), CONCAT(Start_Date, @delim)) AS Start_Date,
CONVERT(nvarchar(max), CONCAT(Close_Date, @delim)) AS Close_Date,
CONVERT(nvarchar(max), CONCAT(Status, @delim)) AS Status,
CONVERT(nvarchar(max), CONCAT(Task_Type, @delim)) AS Task_Type,
CONVERT(nvarchar(max), CONCAT(Identifier, @delim)) AS Identifier,
CONVERT(nvarchar(max), CONCAT(Comment, @delim)) AS Comment,
CONVERT(nvarchar(max), CONCAT(Commodity, @delim)) AS Commodity,
CONVERT(nvarchar(max), CONCAT(Supplier_Name, @delim)) AS Supplier_Name,
1 AS inicioteam
,COALESCE(NULLIF(CHARINDEX(@delim, Team, 1), 0), LEN(Team)) AS fimTeam
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Team, 1, COALESCE(NULLIF(CHARINDEX(@delim, Team, 1), 0), LEN(Team)) - 1)))) AS vteam
,1 AS inicioRequestor
,COALESCE(NULLIF(CHARINDEX(@delim, Requestor, 1), 0), LEN(Requestor)) AS fimRequestor
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Requestor, 1, COALESCE(NULLIF(CHARINDEX(@delim, Requestor, 1), 0), LEN(Requestor)) - 1)))) AS vRequestor
,1 AS inicioCreator
,COALESCE(NULLIF(CHARINDEX(@delim, Creator, 1), 0), LEN(Creator)) AS fimCreator
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Creator, 1, COALESCE(NULLIF(CHARINDEX(@delim, Creator, 1), 0), LEN(Creator)) - 1)))) AS vCreator
,1 AS inicioStart_Date
,COALESCE(NULLIF(CHARINDEX(@delim, Start_Date, 1), 0), LEN(Start_Date)) AS fimStart_Date
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Start_Date, 1, COALESCE(NULLIF(CHARINDEX(@delim, Start_Date, 1), 0), LEN(Start_Date)) - 1)))) AS vStart_Date
,1 AS inicioClose_Date
,COALESCE(NULLIF(CHARINDEX(@delim, Close_Date, 1), 0), LEN(Close_Date)) AS fimClose_Date
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Close_Date, 1, COALESCE(NULLIF(CHARINDEX(@delim, Close_Date, 1), 0), LEN(Close_Date)) - 1)))) AS vClose_Date
,1 AS inicioStatus
,COALESCE(NULLIF(CHARINDEX(@delim, Status, 1), 0), LEN(Status)) AS fimStatus
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Status, 1, COALESCE(NULLIF(CHARINDEX(@delim, Status, 1), 0), LEN(Status)) - 1)))) AS vStatus
,1 AS inicioTask_Type
,COALESCE(NULLIF(CHARINDEX(@delim, Task_Type, 1), 0), LEN(Task_Type)) AS fimTask_Type
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Task_Type, 1, COALESCE(NULLIF(CHARINDEX(@delim, Task_Type, 1), 0), LEN(Task_Type)) - 1)))) AS vTask_Type
,1 AS inicioIdentifier
,COALESCE(NULLIF(CHARINDEX(@delim, Identifier, 1), 0), LEN(Identifier)) AS fimIdentifier
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Identifier, 1, COALESCE(NULLIF(CHARINDEX(@delim, Identifier, 1), 0), LEN(Identifier)) - 1)))) AS vIdentifier
,1 AS inicioComment
,COALESCE(NULLIF(CHARINDEX(@delim, Comment, 1), 0), LEN(Comment)) AS fimComment
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Comment, 1, COALESCE(NULLIF(CHARINDEX(@delim, Comment, 1), 0), LEN(Comment)) - 1)))) AS vComment
,1 AS inicioCommodity
,COALESCE(NULLIF(CHARINDEX(@delim, Commodity, 1), 0), LEN(Commodity)) AS fimCommodity
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Commodity, 1, COALESCE(NULLIF(CHARINDEX(@delim, Commodity, 1), 0), LEN(Commodity)) - 1)))) AS vCommodity
,1 AS inicioSupplier_Name
,COALESCE(NULLIF(CHARINDEX(@delim, Supplier_Name, 1), 0), LEN(Supplier_Name)) AS fimSupplier_Name
,CONVERT(nvarchar(max), RTRIM(LTRIM(SUBSTRING(Supplier_Name, 1, COALESCE(NULLIF(CHARINDEX(@delim, Supplier_Name, 1), 0), LEN(Supplier_Name)) - 1)))) AS vSupplier_Name
From DAtrackertest
WHERE LEN(Team) > 0
AND LEN(Requestor) > 0
AND LEN(Creator) > 0
AND LEN(Start_Date) > 0
AND LEN(Close_Date) > 0
AND LEN(Status) > 0
AND LEN(Task_Type) > 0
AND LEN(Comment) > 0
AND LEN(Identifier) > 0
AND LEN(Commodity) > 0
AND LEN(Supplier_Name) > 0
UNION ALL
SELECT
CONVERT(nvarchar(max), Team) AS Team,
CONVERT(nvarchar(max), Requestor) AS Requestor,
CONVERT(nvarchar(max), Creator) AS Creator,
CONVERT(nvarchar(max), Start_Date) AS Start_Date,
CONVERT(nvarchar(max), Close_Date) AS Close_Date,
CONVERT(nvarchar(max), Status) AS Status,
CONVERT(nvarchar(max), Task_Type) AS Task_Type,
CONVERT(nvarchar(max), Identifier) AS Identifier,
CONVERT(nvarchar(max), Comment) AS Comment,
CONVERT(nvarchar(max), Commodity) AS Commodity,
CONVERT(nvarchar(max), Supplier_Name) AS Supplier_Name,
CONVERT(nvarchar(max), fimTeam) + 1 AS inicioTeam
,COALESCE(NULLIF(CHARINDEX(@delim, Team, fimTeam + 1), 0), LEN(Team)) AS fimTeam
,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Team, fimTeam + 1, COALESCE(NULLIF(CHARINDEX(@delim, Team, fimTeam + 1), 0), LEN(Team))-fimTeam-1)))) AS vTeam,
CONVERT(nvarchar(max), fimRequestor) + 1 AS inicioRequestor
,COALESCE(NULLIF(CHARINDEX(@delim, Requestor, fimRequestor + 1), 0), LEN(Requestor)) AS fimRequestor
,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Requestor, fimRequestor + 1, COALESCE(NULLIF(CHARINDEX(@delim, Requestor, fimRequestor + 1), 0), LEN(Requestor))-fimRequestor-1)))) AS vRequestor,
CONVERT(nvarchar(max), fimCreator) + 1 AS inicioCreator
,COALESCE(NULLIF(CHARINDEX(@delim, Creator, fimCreator + 1), 0), LEN(Creator)) AS fimCreator
,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Creator, fimCreator + 1, COALESCE(NULLIF(CHARINDEX(@delim, Creator, fimCreator + 1), 0), LEN(Creator))-fimCreator-1)))) AS vCreator,
CONVERT(nvarchar(max), fimStart_Date) + 1 AS inicioStart_Date
,COALESCE(NULLIF(CHARINDEX(@delim, Start_Date, fimStart_Date + 1), 0), LEN(Start_Date)) AS fimStart_Date
,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Start_Date, fimStart_Date + 1, COALESCE(NULLIF(CHARINDEX(@delim, Start_Date, fimStart_Date + 1), 0), LEN(Start_Date))-fimStart_Date-1)))) AS vStart_Date,
CONVERT(nvarchar(max), fimClose_Date) + 1 AS inicioClose_Date
,COALESCE(NULLIF(CHARINDEX(@delim, Close_Date, fimClose_Date + 1), 0), LEN(Close_Date)) AS fimClose_Date
,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Close_Date, fimClose_Date + 1, COALESCE(NULLIF(CHARINDEX(@delim, Close_Date, fimClose_Date + 1), 0), LEN(Close_Date))-fimClose_Date-1)))) AS vClose_Date,

   CONVERT(nvarchar(max), fimStatus) + 1 AS inicioStatus  
    ,COALESCE(NULLIF(CHARINDEX(@delim, Status, fimStatus + 1), 0), LEN(Status)) AS fimStatus  
    ,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Status, fimStatus + 1, COALESCE(NULLIF(CHARINDEX(@delim, Status, fimStatus + 1), 0), LEN(Status))-fimStatus-1)))) AS vStatus,  
	  
	CONVERT(nvarchar(max), fimTask_Type) + 1 AS inicioTask_Type  
    ,COALESCE(NULLIF(CHARINDEX(@delim, Task_Type, fimTask_Type + 1), 0), LEN(Task_Type)) AS fimTask_Type  
    ,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Task_Type, fimTask_Type + 1, COALESCE(NULLIF(CHARINDEX(@delim, Task_Type, fimTask_Type + 1), 0), LEN(Task_Type))-fimTask_Type-1)))) AS vTask_Type,  
	CONVERT(nvarchar(max), fimIdentifier) + 1 AS inicioIdentifier  
    ,COALESCE(NULLIF(CHARINDEX(@delim, Identifier, fimIdentifier + 1), 0), LEN(Identifier)) AS fimIdentifier  
    ,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Identifier, fimIdentifier + 1, COALESCE(NULLIF(CHARINDEX(@delim, Identifier, fimIdentifier + 1), 0), LEN(Identifier))-fimIdentifier-1)))) AS vIdentifier,  
	CONVERT(nvarchar(max), fimComment) + 1 AS inicioComment  
    ,COALESCE(NULLIF(CHARINDEX(@delim, Comment, fimComment + 1), 0), LEN(Comment)) AS fimComment  
    ,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Comment, fimComment + 1, COALESCE(NULLIF(CHARINDEX(@delim, Comment, fimComment + 1), 0), LEN(Comment))-fimComment-1)))) AS vComment,  
	  
	CONVERT(nvarchar(max), fimCommodity) + 1 AS inicioCommodity  
    ,COALESCE(NULLIF(CHARINDEX(@delim, Commodity, fimCommodity + 1), 0), LEN(Commodity)) AS fimCommodity  
    ,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Commodity, fimCommodity + 1, COALESCE(NULLIF(CHARINDEX(@delim, Commodity, fimCommodity + 1), 0), LEN(Commodity))-fimCommodity-1)))) AS vCommodity,  
	  
	CONVERT(nvarchar(max), fimSupplier_Name) + 1 AS inicioSupplier_Name  
    ,COALESCE(NULLIF(CHARINDEX(@delim, Supplier_Name, fimSupplier_Name + 1), 0), LEN(Supplier_Name)) AS fimSupplier_Name  
    ,CONVERT(NVARCHAR(max), RTRIM(LTRIM(SUBSTRING(Supplier_Name, fimSupplier_Name + 1, COALESCE(NULLIF(CHARINDEX(@delim, Supplier_Name, fimSupplier_Name + 1), 0), LEN(Supplier_Name))-fimSupplier_Name-1)))) AS vSupplier_Name  
	  
	 FROM split  
	 WHERE fimTeam < LEN(Team)  
    AND fimRequestor < LEN(Requestor)  
	AND fimCreator < LEN(Creator)  
	AND fimStart_Date < LEN(Start_Date)  
	AND fimClose_Date < LEN(Close_Date)  
	AND fimStatus < LEN(Status)  
	AND fimTask_Type < LEN(Task_Type)  
	AND fimIdentifier < LEN(Identifier)  
	AND fimComment < LEN(Comment)  
	AND fimCommodity < LEN(Commodity)  
	AND fimSupplier_Name < LEN(Supplier_Name)  
	)  
	Insert into DA_TRACKER2 (Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name)  
	SELECT vTeam,vRequestor,VCreator,VStart_Date,vClose_Date,vStatus,vTask_Type,vIdentifier,vComment,vCommodity,vSupplier_Name FROM split  

ORDER BY vComment

OPTION(MAXRECURSION 32767)
--if not exists (select * from DA_TRACKER3 where Team=(select team from DAtrackertest where Team like @tree ))
--begin
--insert into DA_TRACKER3 (Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name)
--select Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name from DA_TRACKER2
--end
declare insertinto_da_tracker3 cursor for
select Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name from DA_TRACKER2
open insertinto_da_tracker3
fetch next from insertinto_da_tracker3 into @tree ,@Requestor,@Creator,@START _Date,@Lucas Santos _Date,@Sta ,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier _Name
while @@Fetch _status = 0
begin
declare @teamcount int
set @teamcount = (select count (*) from DA_TRACKER3 where [Team] = @tree and [Start_Date]=@START _date and [Status]=@Sta and [Task_Type]=@Task_Type and [Identifier]=@Identifier and [Supplier_name]=@Supplier _name)

print @teamcount
if (@teamcount > 0)
begin
print @tree
update DA_TRACKER3 set Requestor=@Requestor,Creator=@Creator,Close_Date=@Lucas Santos _date,
Comment=@Comment,Commodity=@Commodity
where [Team] = @tree and [Start_Date]=@START _date and [Status]=@Sta and [Task_Type]=@Task_Type and [Identifier]=@Identifier and [Supplier_name]=@Supplier _name

end
else
begin
insert into DA_TRACKER3 (Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name)
values
(@tree ,@Requestor,@Creator,@START _Date,@Lucas Santos _Date,@Sta ,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier _Name)
end
fetch next from insertinto_da_tracker3 into @tree ,@Requestor,@Creator,@START _Date,@Lucas Santos _Date,@Sta ,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier _Name
end
close insertinto_da_tracker3
deallocate insertinto_da_tracker3
end
else
begin
insert into DA_TRACKER3 (Team,Requestor,Creator,Start_Date,Close_Date,Status,Task_Type,Identifier,Comment,Commodity,Supplier_Name)
values
(@tree ,@Requestor,@Creator,@START _Date,@Lucas Santos _Date,@Sta ,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier _Name)
end
TRUNCATE TABLE DATRACKERTEST
truncate table da_tracker2
End

Answer 1

It don't make sense to write the complete question in the post subject.

not passing into da_tracker2

Why do you think so / when do you check the content? After the stored procedure was executed? If so, no surprise because of

truncate table da_tracker2

at the end of the SP.

Answer 2

Hi @alfygraham ，

or you can try to use SSMS or any other SQL Server tool to upload data：https://powerapps.microsoft.com/en-us/blog/upload-files-from-powerapps-to-sql-server/

and truncate will remove all rows, if your error happen before