What are the challenges you face when working across database platforms? Take the survey
Options

Snippet for INSERT INTO CursorHere WITH TABLOCK

kondrichkondrich Posts: 80 Bronze 2
edited January 29, 2014 3:27AM in SQL Prompt
Hi,

I mentioned it in my diary study yesterday: Is it possible to create a snitppet for INSERT INTO CursorHere WITH TABLOCK ?

When defining INSERT INTO $CURSOR$ WITH TABLOCK it expands to:
INSERT INTO dbo.TableName
        (ColumnName)
VALUES    (0  -- ColumnName - smallint
         ) WITH (TABLOCK)

rather expanding to
INSERT INTO dbo.TableName WITH (TABLOCK) 
        (ColumnName)
VALUES    (0  -- ColumnName - smallint
         )

Since we want to use minimally logged inserts (trace flag 610 is on), we have to use WITH (TABLOCK) very often.
Regards, Klaus

Comments

  • Options
    Aaron LAaron L Posts: 596 New member
    I'll see what I can do about keeping the table hints with the table name :)
  • Options
    kondrichkondrich Posts: 80 Bronze 2
    Thanks Aaron!
    Regards, Klaus
  • Options
    Aaron LAaron L Posts: 596 New member
    Hi Klaus,
    I've just updated our beta to 6.3.0.65 which should include this and a fix for the semicolon bug you reported. You can download it from the same URL as before.
  • Options
    kondrichkondrich Posts: 80 Bronze 2
    Aaron,

    thanks for the new version. It's partially working: It seems that we have a semicolon issue here again.
    If the ii snippet is defined as
    INSERT INTO $CURSOR$ WITH (TABLOCK)
    
    it gets correctly expanded to
    INSERT INTO TableName WITH (TABLOCK)
    		(ColumnName)
    VALUES	(0  -- ColumnName- smallint
    		 )
    
    However, if the snippet is defined as
    INSERT INTO $CURSOR$ WITH (TABLOCK);
    
    (note the trailing semicolon) and I start typing ii+TAB: this leads to
    INSERT INTO | WITH (TABLOCK);
    
    (The pipe indicates the cursor position) So everything is fine until now. But after choosing a table name, the generated code gets replaced by:
    INSERT INTO TableName
    		(ColumnName)
    VALUES	(0  -- ColumnName- smallint
    		 )
    
    ...the table hint and the trailing semicolon are missing after full generation of the code.
    Regards, Klaus
  • Options
    Aaron LAaron L Posts: 596 New member
    Ok, one more try with 6.3.0.70 :) This build should keep the tablehint with the name and place the semicolon after the autogenerated code.
  • Options
    kondrichkondrich Posts: 80 Bronze 2
    OK, 6.3.0.70 fixes that one. Thank you!
    Regards, Klaus
Sign In or Register to comment.