Update and Replace Field Value with New Value

We can use SQL to replace the value in field with a new value. There’s no need to search for those rows affecting, downloading each in turn, making the amendment and finally making the update. For the update of the given field in the table we can use one SQL […]

Verify Database Backup

I was working with a new computer system. I had made a backup of the database. But could I rely on it? To confirm the first run of a database backup I wanted to test/verify it was OK. What I didn’t want to do was to restore the database over […]

Update Row or Insert if Doesn’t Exist

When adding an entry to a database it would be nice to combine the addition with an update. For example adding a new entry if it already exists then that entry can be updated. This means putting more of the sequence within the database stored procedure. But it will save […]

Composite SQL Field with null Value Returns Blank

The intent was to join the address fields as a single returned value from a database query select. But I found that if one of these fields was null, the whole returned result was empty. I had an address, in the usual way, across a number of database fields: address; […]

Insert string into Database as Datetime

Inserting values into a database table I wished to add a date and time. For this I used a convert CONVERT(datetime, ’26/05/2014 14:21:00′) However this will fail where the day value is too high. A value greater than 12 will be taken as an invalid month. Also the day and […]

Get Table Foreign Key Details by SQL

How to view the foreign key settings within a database administered using MyLittleAdmin? Looking to find information about foreign keys between tables, via the control panel for a website I was using MyLittleAdmin to manage the associated database. I was able to view the keys associated with a particular table […]

Turn off Identity Whilst Inserting SQL Table Row

Inserting table rows including the index idents will give an error similar to: Cannot insert explicit value for identity column in table TableName when IDENTITY_INSERT is set to OFF. I was copying the contents of a database table from one installation of DotNetNuke to another. Wishing to maintain the table […]

Convert SQL Field of Type text to nvarchar(max)

To enable database tables to handle the extended character set languages I was looking to convert fields from type┬átext to ntext. Aware of the pending loss of support for the field types text and ntext I also chose to convert these fields to nvarchar(max). SQL server 2016 is removing support […]

MSSQL Table Search and Replace

Consider a database table of posts or pages. The website address has changed, perhaps from the development URL to the live website Or maybe the website’s URL is to change from example.co.uk to example.com. The tables are to be updated, ideally searching and replacing the old value with a new […]

List Large Tables

An enlarged DotNetNuke database can affect performance and also be an indication of a more serious issue. Listing table sizes can help to understand and resolve issues. I have used this piece of SQL to check whether the site log or the event log is over sized on a website. […]