Red Gate forums :: View topic - Snippet for INSERT INTO CursorHere WITH TABLOCK
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

Snippet for INSERT INTO CursorHere WITH TABLOCK

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
kondrich



Joined: 10 Jun 2010
Posts: 60
Location: Vienna, Austria

PostPosted: Fri Jan 24, 2014 9:12 am    Post subject: Snippet for INSERT INTO CursorHere WITH TABLOCK Reply with quote

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


rather expanding to
Code:
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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 218
Location: Red Gate Software

PostPosted: Fri Jan 24, 2014 10:43 am    Post subject: Reply with quote

I'll see what I can do about keeping the table hints with the table name Smile
Back to top
View user's profile Send private message
kondrich



Joined: 10 Jun 2010
Posts: 60
Location: Vienna, Austria

PostPosted: Fri Jan 24, 2014 10:46 am    Post subject: Reply with quote

Thanks Aaron!
_________________
Regards, Klaus
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 218
Location: Red Gate Software

PostPosted: Mon Jan 27, 2014 3:20 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
kondrich



Joined: 10 Jun 2010
Posts: 60
Location: Vienna, Austria

PostPosted: Mon Jan 27, 2014 3:34 pm    Post subject: Reply with quote

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

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

However, if the snippet is defined as
Code:
INSERT INTO $CURSOR$ WITH (TABLOCK);
(note the trailing semicolon) and I start typing ii+TAB: this leads to
Code:
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:
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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 218
Location: Red Gate Software

PostPosted: Tue Jan 28, 2014 2:29 pm    Post subject: Reply with quote

Ok, one more try with 6.3.0.70 Smile This build should keep the tablehint with the name and place the semicolon after the autogenerated code.
Back to top
View user's profile Send private message
kondrich



Joined: 10 Jun 2010
Posts: 60
Location: Vienna, Austria

PostPosted: Wed Jan 29, 2014 9:27 am    Post subject: Reply with quote

OK, 6.3.0.70 fixes that one. Thank you!
_________________
Regards, Klaus
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group