Query Examples for Editing Advance Steel databases using SQL Server Management Studio

Explore the examples of queries for editing databases using SQL Server Management Studio.

To learn how queries are created and used, see Create, save and load a query in SQL Server Management Studio.

Important: Before executing any queries on an Advance Steel database, make sure you create backup copies of the databases that will be modified, by adding them in a backup folder in a desired location on the disk.

Below are some examples of queries that execute typical editing and viewing operations needed for customizing Advance Steel databases.

Show all rows from the database table that contain a particular string (text) value for a specified column

SELECT * FROM [dbo].[table_name]
WHERE [column_name]='value1' 

*If the value is a string, it needs to be fitted between apostrophes (').

Show all rows from the database table that contain a number-type value for a specified column

SELECT * FROM [dbo].[table_name] 
WHERE [column_name]=value1

Show all rows from the database table that don't have an empty cell for a specified column

 SELECT * FROM [dbo].[table_name] 
WHERE [column_name] IS NOT NULL

Show all rows from the database table that contain particular values for 2 different columns

SELECT * FROM [dbo].[table_name]
 WHERE [column1_name]='value1' AND [column2_name]='value2'

Show all rows from the database table that either contain value1 on column1 or contain value2 on column2

SELECT * FROM [dbo].[table_name]
 WHERE [column1_name]='value1' OR [column2_name]='value2'

Show all columns from a certain database table that contain an approximate string(text) value

 SELECT * FROM [dbo].[table_name]
 WHERE [column_name] LIKE '%value1%'

*if %value% is used, it will return all rows where the specified column contains this value.

*if %value is used, it will return all rows where the specified column ends with this value.

*if value% is used, it will return all rows where the specified column starts with this value.

Modify a table name

EXEC sp_rename 'old_name','new_name';

Create a new table

 CREATE TABLE [dbo].[table_name] (
[column1] SMALLINT NOT NULL,
[column2] NVARCHAR (50) NULL,
[column3] FLOAT (53) NULL,
CONSTRAINT [PK_table_name] PRIMARY
KEY CLUSTERED ([column1] ASC),
UNIQUE NONCLUSTERED ([Key] ASC)
);

Copy column inside a table

 UPDATE [dbo].[table_name] SET [destination_column]=[source_column];

Copy columns from one table to another

 INSERT INTO destination_table ([column1_name],[column2_name],[column3_name])
SELECT [column1_name],[column2_name],[column3_name]
FROM source_table

Add values to table

 INSERT INTO destination_table ([column1_name],[column2_name],[column3_name])
VALUES (N'value1',N'value2',N'value3')

*Only if the value is Unicode, an "N" needs to be added in front.

Replace a value from a column when a specified value is set on a different column

 UPDATE [dbo].[table_name] SET [column1_name]='newtext'
WHERE [column2]='searched_value';

Delete table

 DROP TABLE table_name

Below are examples of more advanced queries that imply changing the structure of a database or a database table.

Attention: It is strongly recommended not to apply this type of changes to Advance Steel installation databases.

Delete primary key from table

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [PK_table_name]

Add columns to table

 ALTER TABLE [dbo].[table_name] ADD
 [column1_name] INT NOT NULL,
 [column2_name] NVARCHAR(50) NULL;

Add numbered column to table

 ALTER TABLE [dbo].[table_name] ADD [column1] INT identity(1,1);

Modify column type in table

ALTER TABLE [dbo].[table_name] ALTER COLUMN [column] INT;

Delete column from table

 ALTER TABLE [dbo].[table_name] DROP COLUMN [column];