question

alfygraham-2602 avatar image
0 Votes"
alfygraham-2602 asked Yufeishao-msft edited

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

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,
@Team nvarchar(500)=null,
@Requestor nvarchar(500)=null,
@Creator nvarchar(200)=null,
@Start_date nvarchar(500)=null,
@Close_date nvarchar(500)=null,
@Status 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] = @Team and [Start_Date]=@Start_date and [Status]=@Status 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(@Team,@Requestor,@Creator,@Start_date,@Close_date,@Status,@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 @Team ))
--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 @Team,@Requestor,@Creator,@Start_Date,@Close_Date,@Status,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier_Name
while @@fetch_status = 0
begin
declare @teamcount int
set @teamcount = (select count (
) from DA_TRACKER3 where [Team] = @Team and [Start_Date]=@Start_date and [Status]=@Status and [Task_Type]=@Task_Type and [Identifier]=@Identifier and [Supplier_name]=@Supplier_name)

print @teamcount
if (@teamcount > 0)
begin
print @Team
update DA_TRACKER3 set Requestor=@Requestor,Creator=@Creator,Close_Date=@Close_date,
Comment=@Comment,Commodity=@Commodity
where [Team] = @Team and [Start_Date]=@Start_date and [Status]=@Status 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
(@Team,@Requestor,@Creator,@Start_Date,@Close_Date,@Status,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier_Name)
end
fetch next from insertinto_da_tracker3 into @Team,@Requestor,@Creator,@Start_Date,@Close_Date,@Status,@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
(@Team,@Requestor,@Creator,@Start_Date,@Close_Date,@Status,@Task_Type,@Identifier,@Comment,@Commodity,@Supplier_Name)
end
TRUNCATE TABLE DATRACKERTEST
truncate table da_tracker2
End

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Tip: next time you post code, make use of that button with zeroes and ones on. It permits you insert a code block so that it appears in a consistent way. You may note that some part of your code is in italics. This is because the asterisks in the SELECT * are taken to be markup. (This posting window uses markdown, a lightweight markup language.)

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft edited

Hi @alfygraham-2602

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.