Snippet for INSERT INTO CursorHere WITH TABLOCK

Provides intelligent code completion for SQL Server editors.

Moderators: Anu Deshpande, Aaron Law, David Priddle

Snippet for INSERT INTO CursorHere WITH TABLOCK

Postby kondrich » Fri Jan 24, 2014 9:12 am

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:
Code: Select all
INSERT INTO dbo.TableName
        (ColumnName)
VALUES    (0  -- ColumnName - smallint
         ) WITH (TABLOCK)


rather expanding to
Code: Select all
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
kondrich
 
Posts: 60
Joined: Thu Jun 10, 2010 10:44 am
Location: Vienna, Austria

Postby Aaron Law » Fri Jan 24, 2014 10:43 am

I'll see what I can do about keeping the table hints with the table name :)
Aaron Law
 
Posts: 261
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby kondrich » Fri Jan 24, 2014 10:46 am

Thanks Aaron!
Regards, Klaus
kondrich
 
Posts: 60
Joined: Thu Jun 10, 2010 10:44 am
Location: Vienna, Austria

Postby Aaron Law » Mon Jan 27, 2014 3:20 pm

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.
Aaron Law
 
Posts: 261
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby kondrich » Mon Jan 27, 2014 3:34 pm

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
Code: Select all
INSERT INTO $CURSOR$ WITH (TABLOCK)

it gets correctly expanded to
Code: Select all
INSERT INTO TableName WITH (TABLOCK)
      (ColumnName)
VALUES   (0  -- ColumnName- smallint
       )

However, if the snippet is defined as
Code: Select all
INSERT INTO $CURSOR$ WITH (TABLOCK);
(note the trailing semicolon) and I start typing ii+TAB: this leads to
Code: Select all
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:
Code: Select all
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
kondrich
 
Posts: 60
Joined: Thu Jun 10, 2010 10:44 am
Location: Vienna, Austria

Postby Aaron Law » Tue Jan 28, 2014 2:29 pm

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.
Aaron Law
 
Posts: 261
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby kondrich » Wed Jan 29, 2014 9:27 am

OK, 6.3.0.70 fixes that one. Thank you!
Regards, Klaus
kondrich
 
Posts: 60
Joined: Thu Jun 10, 2010 10:44 am
Location: Vienna, Austria


Return to SQL Prompt 6

Who is online

Users browsing this forum: No registered users and 1 guest