Tag: SQL Server | Page 4

Perform Update, Delete and Insert using Merge Statement

In SQL Server, a better way to perform insert, update, or delete operations on a target table based on the results of a join with a source table is by using one MERGE statement.

UPDELSERT using MERGE statement

MERGE Table2 AS tgt
USING (SELECT name, descr, updatedate from Table1) AS src
ON src.name = tgt.name
WHEN MATCHED AND src.updatedate > tgt.updatedate THEN
	UPDATE SET descr = src.descr,
			   updatedate = src.updatedate
WHEN NOT MATCHED THEN
	INSERT (name, descr, updatedate) 
	VALUES (src.name, src.descr, src.updatedate)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

It’s more efficient as you’re doing just one statement instead of three individual (UPDATE, DELETE, and INSERT) SQL queries.

More examples can be found on the next posting, Ways to Upsert a Record in SQL Server.

How to Change Default Project Folder in SSMS

There are several ways to configure SSMS to point to your own working directory as the default. Here’s the easiest to do it without registry hack. You just need to edit the Visual Studio settings file (XML format).

Open Windows Explorer, and go to (you can force type):

%USERPROFILE%\Documents\SQL Server Management Studio\Settings

Find vssettings file with the latest modification date. The file name should follow the convention below.

CurrentSettings-YYYY-MM-DD.vssettings

Make a backup of that file. It’s an XML format and it’s very easy to make a mistake, but less dangerous than messing up your registry.

Open the file with a text editor.

Change from:

<PropertyValue name="ProjectsLocation">%vsspv_visualstudio_dir%\Projects</PropertyValue>

To:

<PropertyValue name="ProjectsLocation">%USERPROFILE%\Documents\My Directory</PropertyValue>

Save the file and make sure you quit SSMS and reload it before it takes affect.

That’s all there is to it. The next time you open a file or a project, SSMS will look into your custom folder first.

How to Find a Column Name in SQL Server Database

This query was originally appeared from SQL Authority Blog and I find it really useful when you need to find a column in any tables in a database.

SELECT t.name AS "Table Name",
  SCHEMA_NAME(schema_id) AS "Schema",
  c.name AS "Column Name"
FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ColumnName%'
ORDER BY 'Schema', 'Column Name';

If you need to find all column names that are in the database, just comment out or delete the highlighted line from the SQL command above.

Using Connection Coloring in SSMS Tools Pack

From previous posting, we demonstrated a way to use custom color coding using SQL Server Management Studio’s (SSMS) Registered Servers properties. But using the feature can be very dangerous if you don’t have the discipline to follow a strict process of opening new query from the registered servers pane. For example, if you use Change Connection button, the color will stay the same as the last environment you were connected to the first time via the registered server panel. That’s where SSMS Tools Pack comes in handy. Don’t get it wrong, this SSMS add-on software is more than just a connection color coding tool, but to add on from our last posting, you can do connection color coding quite easily by using its Window Connection Coloring feature. The tool is free for SSMS 2008 or earlier. You only have to pay when use version 2012 or higher. So here are the steps to use color connection with SSMS Tools Pack:

After you installed the tool, you should see it displayed on your SSMS’ menu (SSMS Tools). Click on it and select Window Connection Coloring, then Options…
This part is self-explanatory:
1. First, you may want to increase the thickness (in pixels) of the colored bar that will be displayed. I found the default a bit too thin for my taste.
2. Unlike default SSMS custom color, with SSMS Tools Pack you can choose where you want the bar displayed on either one of the four sides of your query window.
3. Click on the color bar to pick your custom color. Click OK twice when done.

That’s all there is to it. And this is what it looks like when it’s done.

Note: What’s displayed here is a combination of SSMS’ own default custom color on the bottom bar and SSMS Tools Pack’s color bar on the top. I personally like this setup as it’s harder to miss and where the two color disagree, I know the one from SSMS Tools Pack (top bar) is the one to follow.

Download

SSMS Tools Pack

How to Use Custom Color in SSMS

With many mistakes made by developers and DBAs alike firing a query in a wrong environment, there is one easy thing that can be done to alleviate this problem. That is by using the color coding on the status bar and you can set this up in the server registration on your SQL Server Management Studio (SSMS).

This feature is so easy to overlook because many people just don’t bother to look at the second tab when editing the Properties in Server Registration (in fact, quite a few people don’t bother to use Registered Servers at all to connect to the databases).

On the Registered Servers pane on SQL Management Studio, right-click on the server you want to assign a color and select Properties…

Click on the Connection Properties tab and check on Use custom color:

Here are the color schemes I use for the example. Feel free to use any colors that suit you.
Environment (RGB values)
DEV (102, 133, 46)
STAGING (128, 128, 0)
QA (255, 128, 0)
PROD (190, 0, 0)

Do these steps for all database servers you want to color code.

Here is the final result with all windows opened.

Caveats

You need to use the registered servers in order to use the custom color.
For some reason, the custom color settings is not exportable. So you have to take note of your color settings and repeat the steps for any other computers you use.
~~If you use Change Connection button, the color will stay the same as the last one you connected to from the registered servers, which can be dangerous.~~ This can be remedied by using connection coloring in SSMS Tools Pack. This issue was fixed in Windows 8.1, however SSMS Tools Pack still gives a bit more flexibility to place the color bar on one of the four sides of SSMS query window. If you have access to Redgate SQL Prompt, this might be the best option, and you can read on how to do it here.

Perform Update, Delete and Insert using Merge Statement

UPDELSERT using MERGE statement

Further Reading

How to Change Default Project Folder in SSMS

How to Find a Column Name in SQL Server Database

Further Reading

Using Connection Coloring in SSMS Tools Pack

Download

Further Reading

How to Use Custom Color in SSMS

Caveats

Further Reading

Recent Posts

UPDELSERT using MERGE statement

Further Reading

Further Reading

Download

Further Reading

Caveats

Further Reading

Recent Posts

Tags