Need help Insert and Update in Single Statement

• abu.salim.6181

  SSC Veteran

  Points: 274

  More actions

  September 3, 2018 at 8:28 am

  #372252

  I've tables as follow

  
CREATE TABLE [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1](
    [idx] [int] IDENTITY(-2147483648,1) NOT NULL,
    [batch_Id] [uniqueidentifier] NULL,
    [group_1Digit] [char](5) NULL,
    [group_2Digit] [char](5) NULL,
    [group_3Digit] [char](5) NULL,
    [kod] [nvarchar](5) NULL,
    [year_num] [int] NULL,
    [period_num] [int] NULL,
    [chart_code] [char](5) NULL,
    [pusat_kos] [nvarchar](10) NULL,
    [close_amt] [decimal](18, 2) NULL,
CONSTRAINT [PK_crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1] PRIMARY KEY NONCLUSTERED
(
    [idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

  GO

  CREATE TABLE [dbo].[tmpaccthist_2013_2014_report_2](
    [idx] [int] IDENTITY(-2147483648,1) NOT NULL,
    [disatu_code] [nvarchar](50) NULL,
    [disatu_desc] [nvarchar](50) NULL,
    [cmpy_code] [nvarchar](50) NULL,
    [kod] [nvarchar](5) NULL,
    [state_code] [nvarchar](50) NULL,
    [state_desc] [nvarchar](500) NULL,
    [campus_cd] [nvarchar](30) NULL,
    [year_num] [int] NULL,
    [period_num] [int] NULL,
    [trnxDte] [date] NULL,
    [acct_code] [nvarchar](50) NULL,
    [group_1Digit] [char](5) NULL,
    [group_2Digit] [char](5) NULL,
    [group_3Digit] [char](5) NULL,
    [chart_code] [char](5) NULL,
    [pusat_kos] [nvarchar](10) NULL,
    [close_amt] [decimal](18, 2) NULL,
CONSTRAINT [PK_tmpaccthist_2013_2014_report_2_Insert] PRIMARY KEY CLUSTERED
(
    [idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

  GO

  I've 4 transaction as follow,
  

  Declare
@campus_cd nvarchar(300),
@cmpy_code nvarchar(500),
@year1 int,
@year2 int,
@period int,
@period2 int,
@batch_Id uniqueidentifier

  /*year1*/
insert into [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1]
(batch_Id, chart_code, close_amt, kod, year_num)
    select @batch_Id,
    [chart_code], sum([close_amt]) as close_amt, [kod], [year_num]

      from tmpaccthist_2013_2014_report_2
    where 1=1
    AND campus_cd in (
        SELECT Value FROM fn_Split(@campus_cd, ',')
    )

      AND kod in (
        SELECT Value FROM fn_Split(@cmpy_code, ',')
    )

      AND year_num = @year1 and period_num=@period

      Group by [kod], [year_num], [chart_code];

  /*year2*/

  insert into [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1]
(batch_Id, chart_code, close_amt, kod, year_num)
    select @batch_Id,
    [chart_code], sum([close_amt]) as close_amt, [kod], [year_num]

      from tmpaccthist_2013_2014_report_2
    where 1=1
    AND campus_cd in (
        SELECT Value FROM fn_Split(@campus_cd, ',')
    )

      AND kod in (
        SELECT Value FROM fn_Split(@cmpy_code, ',')
    )

      AND year_num = @year2 and period_num=@period2

      Group by [kod], [year_num], [chart_code];

      

      
        update [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1]
        set
        group_1Digit = LEFT(chart_code, 1) + '0000',
        group_2Digit = LEFT(chart_code, 2) + '000' ,
        group_3Digit = LEFT(chart_code, 3) + '00'

          where batch_Id=@batch_Id;

          
        update crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1
        set close_amt =
        CASE
        WHEN ISNULL(close_amt, 0) >= 0 THEN close_amt * -1
        WHEN ISNULL(close_amt, 0) <= 0 THEN close_amt * -1
         ELSE close_amt
        END

          where batch_Id=@batch_Id
        and [group_1Digit] in ('70000','80000');

  How to make it into Single Transaction? Please help

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  September 3, 2018 at 8:47 am

  #2004268

  Declare
@campus_cd nvarchar(300),
@cmpy_code nvarchar(500),
@year1 int,
@year2 int,
@period int,
@period2 int,
@batch_Id uniqueidentifier

  BEGIN TRY

    BEGIN TRANSACTION;
  /*year1*/
  insert into [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1]
  (batch_Id, chart_code, close_amt, kod, year_num)
   select @batch_Id,
   [chart_code], sum([close_amt]) as close_amt, [kod], [year_num]

     from tmpaccthist_2013_2014_report_2
   where 1=1
   AND campus_cd in (
    SELECT Value FROM fn_Split(@campus_cd, ',')
   )

     AND kod in (
    SELECT Value FROM fn_Split(@cmpy_code, ',')
   )

     AND year_num = @year1 and period_num=@period

     Group by [kod], [year_num], [chart_code];

    /*year2*/

    insert into [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1]
  (batch_Id, chart_code, close_amt, kod, year_num)
   select @batch_Id,
   [chart_code], sum([close_amt]) as close_amt, [kod], [year_num]

     from tmpaccthist_2013_2014_report_2
   where 1=1
   AND campus_cd in (
    SELECT Value FROM fn_Split(@campus_cd, ',')
   )

     AND kod in (
    SELECT Value FROM fn_Split(@cmpy_code, ',')
   )

     AND year_num = @year2 and period_num=@period2

     Group by [kod], [year_num], [chart_code];

   

   
    update [dbo].[crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1]
    set
    group_1Digit = LEFT(chart_code, 1) + '0000',
    group_2Digit = LEFT(chart_code, 2) + '000' ,
    group_3Digit = LEFT(chart_code, 3) + '00'

      where batch_Id=@batch_Id;

    
    update crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1
    set close_amt =
    CASE
    WHEN ISNULL(close_amt, 0) >= 0 THEN close_amt * -1
    WHEN ISNULL(close_amt, 0) <= 0 THEN close_amt * -1
     ELSE close_amt
    END

      where batch_Id=@batch_Id
    and [group_1Digit] in ('70000','80000');

    COMMIT;

  END TRY

  BEGIN CATCH
  IF @@TRANCOUNT > 0
   ROLLBACK;

    THROW;

  END CATCH

• abu.salim.6181

  SSC Veteran

  Points: 274

  More actions

  September 3, 2018 at 8:58 am

  #2004270

  Hello Sir Jonathan AC Roberts,
  I mean - How all Insert, and Update statement into SINGLE STATEMENT

  My bad said Single Transaction

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  September 3, 2018 at 9:02 am

  #2004272

  abu.salim.6181 - Monday, September 3, 2018 8:58 AM

  Hello Sir Jonathan AC Roberts,
  I mean - How all Insert, and Update statement into SINGLE STATEMENT

  My bad said Single Transaction

  You'll need to use MERGE if you want to do it in a single statement.
  https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017
  But MERGE will not perform as well as an UPDATE followed by an INSERT.
  So really it would be best for you to stick with two separate statements.

• DesNorton

  SSC-Insane

  Points: 24276

  More actions

  September 3, 2018 at 9:15 am

  #2004275

  Without sample data or an expected outcome, I believe that the following SQL statement should do the trick.

  DECLARE @campus_cd nvarchar(300)
  , @cmpy_code nvarchar(500)
  , @year1  int
  , @year2  int
  , @period  int
  , @period2 int
  , @batch_Id uniqueidentifier;

  INSERT INTO dbo.crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1 ( batch_Id, chart_code, close_amt, kod, year_num, group_1Digit, group_2Digit, group_3Digit )
SELECT @batch_Id
  , chart_code
  , close_amt  = CASE WHEN LEFT(chart_code, 1) IN ('7', '8') THEN SUM(close_amt) * -1 ELSE SUM(close_amt) END
  , kod
  , year_num
  , group_1Digit = LEFT(chart_code, 1) + '0000'
  , group_2Digit = LEFT(chart_code, 2) + '000'
  , group_3Digit = LEFT(chart_code, 3) + '00'
FROM tmpaccthist_2013_2014_report_2
WHERE 1 = 1
AND campus_cd IN ( SELECT Value FROM fn_Split(@campus_cd, ',') )
AND kod   IN ( SELECT Value FROM fn_Split(@cmpy_code, ',') )
AND ( ( year_num = @year1 AND period_num = @period ) /*year1*/
   OR ( year_num = @year2 AND period_num = @period2 ) /*year2*/
  )
GROUP BY kod, year_num, chart_code;

  ---

  How to post data/code on a forum to get the best help.

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 3, 2018 at 8:03 pm

  #2004291

  abu.salim.6181 - Monday, September 3, 2018 8:58 AM

  Hello Sir Jonathan AC Roberts,
  I mean - How all Insert, and Update statement into SINGLE STATEMENT

  WHY?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• abu.salim.6181

  SSC Veteran

  Points: 274

  More actions

  September 3, 2018 at 8:09 pm

  #2004292

  Hello Jeff,

  I want to reduce deadlock. That's why I want to make it short @ Single Statement

• abu.salim.6181

  SSC Veteran

  Points: 274

  More actions

  September 3, 2018 at 8:11 pm

  #2004293

  DesNorton - Monday, September 3, 2018 9:15 AM

  Without sample data or an expected outcome, I believe that the following SQL statement should do the trick.

  DECLARE @campus_cd nvarchar(300)
  , @cmpy_code nvarchar(500)
  , @year1  int
  , @year2  int
  , @period  int
  , @period2 int
  , @batch_Id uniqueidentifier;

  INSERT INTO dbo.crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1 ( batch_Id, chart_code, close_amt, kod, year_num, group_1Digit, group_2Digit, group_3Digit )
SELECT @batch_Id
  , chart_code
  , close_amt  = CASE WHEN LEFT(chart_code, 1) IN ('7', '8') THEN SUM(close_amt) * -1 ELSE SUM(close_amt) END
  , kod
  , year_num
  , group_1Digit = LEFT(chart_code, 1) + '0000'
  , group_2Digit = LEFT(chart_code, 2) + '000'
  , group_3Digit = LEFT(chart_code, 3) + '00'
FROM tmpaccthist_2013_2014_report_2
WHERE 1 = 1
AND campus_cd IN ( SELECT Value FROM fn_Split(@campus_cd, ',') )
AND kod   IN ( SELECT Value FROM fn_Split(@cmpy_code, ',') )
AND ( ( year_num = @year1 AND period_num = @period ) /*year1*/
   OR ( year_num = @year2 AND period_num = @period2 ) /*year2*/
  )
GROUP BY kod, year_num, chart_code;

  Hello DesNortan,

  
close_amt  = CASE WHEN LEFT(chart_code, 1) IN ('7', '8') THEN SUM(close_amt) * -1 ELSE SUM(close_amt) END


  update crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1
        set close_amt =
        CASE
        WHEN ISNULL(close_amt, 0) >= 0 THEN close_amt * -1
        WHEN ISNULL(close_amt, 0) <= 0 THEN close_amt * -1
         ELSE close_amt
        END

          where batch_Id=@batch_Id
        and [group_1Digit] in ('70000','80000');


  It's  enough?

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 3, 2018 at 8:31 pm

  #2004295

  abu.salim.6181 - Monday, September 3, 2018 8:09 PM

  Hello Jeff,

  I want to reduce deadlock. That's why I want to make it short @ Single Statement

  Step 1 would be to make the code more efficient.  So start off by posting the code for the fn_Split function you're using.  If it's the one I'm thinking of, it's a performance challenged mTVF that has a While Loop in it which makes it far worse than any scalar function that you might imagine.

  Also, Des Norton is spot on.  There is no need for a separate UPDATE.  It looks like it can all be done during the INSERT.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• abu.salim.6181

  SSC Veteran

  Points: 274

  More actions

  September 3, 2018 at 8:59 pm

  #2004296

  Here my function

  CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
( 
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN

  DECLARE @index int
SET @index = -1

  WHILE (LEN(@text) > 0)
BEGIN
  SET @index = CHARINDEX(@delimiter , @text)
  IF (@index = 0) AND (LEN(@text) > 0)
  BEGIN
   INSERT INTO @Strings VALUES (@text)
    BREAK
  END
  IF (@index > 1)
  BEGIN
   INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
   SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
  ELSE
  SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
RETURN
END
GO

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 3, 2018 at 9:41 pm

  #2004297

  abu.salim.6181 - Monday, September 3, 2018 8:59 PM

  Here my function

  CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
( 
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN

  DECLARE @index int
SET @index = -1

  WHILE (LEN(@text) > 0)
BEGIN
  SET @index = CHARINDEX(@delimiter , @text)
  IF (@index = 0) AND (LEN(@text) > 0)
  BEGIN
   INSERT INTO @Strings VALUES (@text)
    BREAK
  END
  IF (@index > 1)
  BEGIN
   INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
   SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
  ELSE
  SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
RETURN
END
GO

  That's the one I was thinking of.  It's a "nibbler" splitter.  Please see the following article for more information on splitters and how slow many of them are, including the one above, and one that works much better.  Then, since you're using 2012, see a simple change to that splitter that doubles its speed in the link that follows that.
  http://www.sqlservercentral.com/articles/Tally+Table/72993/
  http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• DesNorton

  SSC-Insane

  Points: 24276

  More actions

  September 3, 2018 at 10:32 pm

  #2004300

  abu.salim.6181 - Monday, September 3, 2018 8:11 PM

  DesNorton - Monday, September 3, 2018 9:15 AM

  Without sample data or an expected outcome, I believe that the following SQL statement should do the trick.

  DECLARE @campus_cd nvarchar(300)
  , @cmpy_code nvarchar(500)
  , @year1  int
  , @year2  int
  , @period  int
  , @period2 int
  , @batch_Id uniqueidentifier;

  INSERT INTO dbo.crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1 ( batch_Id, chart_code, close_amt, kod, year_num, group_1Digit, group_2Digit, group_3Digit )
SELECT @batch_Id
  , chart_code
  , close_amt  = CASE WHEN LEFT(chart_code, 1) IN ('7', '8') THEN SUM(close_amt) * -1 ELSE SUM(close_amt) END
  , kod
  , year_num
  , group_1Digit = LEFT(chart_code, 1) + '0000'
  , group_2Digit = LEFT(chart_code, 2) + '000'
  , group_3Digit = LEFT(chart_code, 3) + '00'
FROM tmpaccthist_2013_2014_report_2
WHERE 1 = 1
AND campus_cd IN ( SELECT Value FROM fn_Split(@campus_cd, ',') )
AND kod   IN ( SELECT Value FROM fn_Split(@cmpy_code, ',') )
AND ( ( year_num = @year1 AND period_num = @period ) /*year1*/
   OR ( year_num = @year2 AND period_num = @period2 ) /*year2*/
  )
GROUP BY kod, year_num, chart_code;

  Hello DesNortan,

  
close_amt  = CASE WHEN LEFT(chart_code, 1) IN ('7', '8') THEN SUM(close_amt) * -1 ELSE SUM(close_amt) END


  update crpt_NotaPenyataKewangan_LEVEL_1_Vertical_1
        set close_amt =
        CASE
        WHEN ISNULL(close_amt, 0) >= 0 THEN close_amt * -1
        WHEN ISNULL(close_amt, 0) <= 0 THEN close_amt * -1
         ELSE close_amt
        END

          where batch_Id=@batch_Id
        and [group_1Digit] in ('70000','80000');


  It's  enough?

  If you look at the logic of your CASE statement, it says that ALWAYS use close_amt * -1 where [group_1Digit] in ('70000','80000').
  BUT that is applied AFTER the original select, so it is actually SUM(close_amt) * -1 when the group by is applied.
  Also group_1Digitis calculated based on the 1st character of chart_code, so LEFT(chart_code, 1) IN ('7', '8') is the same as  [group_1Digit] in ('70000','80000').

  ---

  How to post data/code on a forum to get the best help.