Dev-Insider: Editing database values using SQL Server Management Studio (SSMS)

Author: Marcus

Changing store data is an essential part of running an e-commerce business. Whether it's about order of options or adding new products, the ability to customize database values is critical. importance. In this guide, we will focus on the use of SQL Server Management Studio (SSMS) to Edit store database values efficiently and accurately.

What is SQL Server Management Studio (SSMS)?

SQL Server Management Studio (SSMS) is an integrated development environment (IDE) specifically designed for managing SQL Server databases. With SSMS you can execute SQL queries, design database objects, edit tables and much more. It is a powerful tool used by developers and database administrators alike to manage the simplify database administration.

The general way to change store data is either via the backend or via a data import. In some cases, data can also be directly via SQL (Structured Query Language) can be inserted, changed or deleted. Such SQL statements can also be executed without additional software directly via the store backend (see System > Maintenance).

Generally it is advisable to create a database backup beforehand in order to be able to restore the old to be able to restore the old data status.

However, direct editing of database values using SQL Server Management Studio (SSMS) is an alternative. Using an example this way is illustrated in the following.

The task: change the order of all options of the "Color" specification attribute.

The menu commands of SSMS are in English in this example because I have installed the English version of SSMS.

First I need the ID of the specification attribute Color. The ID is the one automatically generated by the database, unchangeable primary key of a data record. I get the value either via the data table SpecificationAttribute or via the store backend under Catalog > Specification attributes > Color. In this case, the ID is 2.

Next, I search in the object explorer of SSMS below our database for the table SpecificationAttributeOption. It contains the options for all specification attributes. Right-click on the table name and select the command Edit Top 200 Rows. The number of lines that are loaded using this command can be set using the option under Tools > Options > SQL Server Object Explorer > Commands > Value for Edit Top n Rows command change. With 0 all lines or Options loaded.

Options can now be changed in the data window that has opened. However, I don't want to be the first 200 options, but those of the specification attribute Color change. To do this, I right-click in the data window and select Pane > SQL. The SQL statement for loading the options is then displayed in the upper part of the window, and this statement can be changed as required. This requires some basic SQL knowledge, but don't worry, it's not particularly complicated. The instruction currently looks like this:

SELECT TOP (200) Id, SpecificationAttributeId, Name, DisplayOrder, 
Alias, NumberValue, MediaFileId, Color
FROM SpecificationAttributeOption

Step 1:

First I remove TOP (200)because I don't want the first 200 lines.

SELECT Id, SpecificationAttributeId, Name, DisplayOrder, 
Alias, NumberValue, MediaFileId, Color
FROM SpecificationAttributeOption

Step 2:

Behind SELECT all field names of the table are comma-separated SpecificationAttributeOption are listed. These can be in the context of updates of Smartstore 5 change by all means. I am actually only interested in Name (the option value), DisplayOrder (the order) and, as a check, the field SpecificationAttributeIdwhich contains the ID of the associated specification attribute. So I remove all the others field names from the statement.

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption

Step 3:

AFTER FROM is the name of the table whose data is to be loaded. Of course it stays that way. But now I would like to load all the options (I had the Top 200 removed), but only those of the specification attribute Color. To do this, I add the following using the specification attribute ID for Color (in this case as mentioned 2) I add a so-called Where condition to load only color options.

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption
WHERE SpecificationAttributeId = 2

Step 4:

And last but not least, I would like to display the options sorted alphabetically in ascending order according to their value. I attach a corresponding Order-By statement with the desired field name.

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption
WHERE SpecificationAttributeId = 2
ORDER BY Name

For descending sorting, the ORDER BY Name DESC statement would be used. To execute the revised SQL statement I make right-click in the statement or data window and select Execute SQL.

In order to keep the options sorted alphabetically in the store, I now simply have to enter the following in the column DisplayOrder column with incrementing values. It is advisable to leave gaps between the values in order to be able to use easier to maintain the sorting for new colors.

We recommend making regular backups of the database to avoid data loss. This is also possible via the store backend possible. Document your changes so that you can retrace them later. With SSMS you have control over your store database and you can manage data optimally.

Do you have further questions about SQL Management Studio? Give us a call or send us an e-mail. Our Smartstore team will be happy to assist you personally!
https://smartstore.com/de/persoenliche-beratung/

More information about SQL Server Management Studio: