Tag: SQL | Page 3

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 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.

Migrating Genesis Metadata to Yoast SEO

If you install Yoast’s WordPress SEO Plugin on top of your Genesis theme, you’ll notice that all your manually typed meta descriptions (and maybe keywords) just went completely missing. Meta description is an important element for search engine to index your pages and when you already have more than hundreds of pages and posts or even ten, then re-typing is definitely not an option.

Since I’m not aware of any way to do this from the plugin, the easiest way to do this is on the database.

Again as a forewarning, before you attempt to do this, backup everything in your MySQL to make sure you can revert back if you make a mistake. The solution is quick and simple, but it’s so easy to make a mistake that will corrupt your data and possibly more.

Go to your SQL admin interface such as phpMyAdmin (or you can do command line interface if you prefer) and run these sql commands:

UPDATE wp_postmeta
SET meta_key = '_yoast_wpseo_metadesc'
WHERE meta_key = '_genesis_description';

UPDATE wp_postmeta
SET meta_key = '_yoast_wpseo_metakeywords'
WHERE meta_key = '_genesis_keywords';

Here’s how it looks on phpMyAdmin panel:
MySQL's phpMyAdmin SQL commands

You have to make sure that there aren’t any typos whatsoever on the value you need to assign on the meta_key or else you need to restore the database from the backup and start over.

These sql statements were tested on WordPress SEO version 1.4.24 and Genesis Framework 2.0.2. If you use older or newer versions of both software, you need to verify the meta_key if they’re still using the same values.

Useful Scripts for SQL Server Performance Tuning

A couple of useful scripts of sql commands to aid performance tuning in SQL Server (T-SQL).

This handy T-SQL script was from SQL Authority shows all queries that are currently running on your server.

SELECT sqltext.TEXT,
  req.session_id,
  req.status,
  req.command,
  req.cpu_time,
  req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Any running query in the result can be killed by using the KILL command followed by the session id.

KILL [session_id]

Database I/O Information

This one is taken from the book:
Professional Microsoft SQL Server 2014 Administration

-- Database IO analysis.
WITH IOFORDATABASE AS 
(
  SELECT 
    DB_NAME(VFS.database_id) AS DatabaseName
    , CASE WHEN smf.type = 1 
      THEN 'LOG_FILE' ELSE 'DATA_FILE' END
	    AS DatabaseFile_Type
    , SUM(VFS.num_of_bytes_written) AS IO_Write
    , SUM(VFS.num_of_bytes_read) AS IO_Read
    , SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) 
        AS Total_IO
    , SUM(VFS.io_stall) AS IO_STALL
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
  JOIN sys.master_files AS smf
  ON VFS.database_id = smf.database_id
  AND VFS.file_id = smf.file_id
  GROUP BY DB_NAME(VFS.database_id), smf.type
)
SELECT
  ROW_NUMBER() OVER (ORDER BY io_stall DESC) AS RowNumber
  , DatabaseName
  , DatabaseFile_Type
  , CAST(1.0 * IO_Read / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_Read_MB
  , CAST(1.0 * IO_Write / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_Write_MB
  , CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_TOTAL_MB
  , CAST(IO_STALL / 1000. AS DECIMAL(12, 2)) 
      AS IO_STALL_Seconds
  , CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10,2))
      AS IO_STALL_Pct
FROM IOFORDATABASE
ORDER BY IO_STALL_Seconds DESC

How to Get Names of All Tables in SQL Server

Here’s a list of sql commands (T-SQL) to get the names of all tables, views, sprocs and triggers in SQL Server:

-- Get all Tables
SELECT name FROM sys.tables

-- Get all Views
SELECT name FROM sys.views

-- Get all Stored Procedures
SELECT name 
  FROM sys.procedures

-- Get all Triggers
SELECT name FROM sys.triggers

If you want to see all objects within the database, use the following SQL command:

SELECT name, type, type_desc
  FROM sys.objects

Try this one to also include all column names info:

SELECT * FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id

Perform Update, Delete and Insert using Merge Statement

UPDELSERT using MERGE statement

Further Reading

How to Find a Column Name in SQL Server Database

Further Reading

Migrating Genesis Metadata to Yoast SEO

Useful Scripts for SQL Server Performance Tuning

Database I/O Information

Further Reading

How to Get Names of All Tables in SQL Server

Further Reading

Recent Posts

UPDELSERT using MERGE statement

Further Reading

Further Reading

Database I/O Information

Further Reading

Further Reading

Recent Posts

Tags