Dropping Tables

To remove a table from the database, use the DROP TABLE statement. For example, to delete the student table:

DROP TABLE student;

Counting Table Columns

To determine the number of columns in a specific table, you can query the system catalog views. For instance, to count columns in the m_record_new table:

SELECT COUNT(sc.name)
FROM sys.columns AS sc
JOIN sys.objects AS so
 ON sc.object_id = so.object_id
WHERE so.name = 'm_record_new';
 

Querying and Updating Column Values

This section covers modifying and retrieving data within a specific column. For example, to append the character 'R' to all values in the matsn column of the aoi_mat_lot table and then query the updated rows:

-- Update values in the matsn column
UPDATE aoi_mat_lot
SET matsn = matsn + 'R'
WHERE matsn IN ('G32992600', 'G32632271', 'G32632115');

-- Query the updated rows
SELECT *
FROM aoi_mat_lot
WHERE matsn IN ('G32992600R', 'G32632271R', 'G32632115R');
 

Inserting Data Between Tables

Data transfer between tables can be achieved in several ways, depending on whether the table structures are identical.

Identical Table Structures

When both the source and destination tables have the same structure, you can insert all columns directly.

-- Insert all data from Info to InfoLog
INSERT INTO InfoLog SELECT * FROM Info;

-- Create a new table temptable with data from table
SELECT * INTO temptable FROM table;
 

Different Table Structures

If the table structures differ, specify the target columns in the destination table and match them with the corresponding source columns.

-- Insert specific columns from Info to InfoLog
INSERT INTO InfoLog (ID, [Name])
SELECT ID, [Name]
FROM Info;
 

Inserting Data with Duplicate Exclusion

To insert data while preventing duplicates, especially when dealing with potentially overlapping records, several methods can be employed.

Using NOT EXISTS

This method checks for the existence of a record in the destination table before inserting. This can be applied to specific conditions or across the entire table.

-- Transactional insert with duplicate check based on WO_BOM_SID for a specific MATNR
BEGIN TRANSACTION;
INSERT INTO AOI_WO_BOM
SELECT *
FROM [172.30.1.133].[Cimes5PRDDB_CN].DBO.AOI_WO_BOM AS A
WHERE NOT EXISTS (
   SELECT 1
   FROM AOI_WO_BOM AS B
   WHERE A.WO_BOM_SID = B.WO_BOM_SID
)
AND A.MATNR = '203801000060';
-- COMMIT TRANSACTION; -- Uncomment to finalize

-- Inserting data from Employees to Employees2, excluding duplicates based on 'id'
INSERT INTO Employees2 (id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58)
SELECT id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58
FROM Employees
WHERE NOT EXISTS (
   SELECT 1
   FROM Employees2
   WHERE Employees2.id = Employees.id
);
 

Using MERGE Statement (Often Most Efficient)

The MERGE statement provides a powerful and often efficient way to synchronize data between two tables, handling inserts, updates, and deletes in a single statement. Here, it's used for insertnig rows from Employees to Employees1 only if they don't already exist.

BEGIN TRANSACTION;
MERGE Employees1 AS target
USING Employees AS source
ON (target.id = source.id)
WHEN NOT MATCHED BY TARGET THEN
   INSERT (id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58)
   VALUES (source.id, source.a1, source.a2, source.a3, source.a5, source.a6, source.a7, source.a8, source.a9, source.a10, source.a11, source.a13, source.a14, source.a22, source.a29, source.a17, source.a26, source.a30, source.a33, source.a35, source.a40, source.a48, source.a49, source.a51, source.a52, source.a58);
-- COMMIT TRANSACTION; -- Uncomment to finalize
 

Transaction Control

SQL Server transactions allow you to group a series of operations into a single, atomic unit. This ensures data integrity, as all operations within the transaction are either completed successfully (committed) or none of them are applied (rolled back).

• BEGIN TRANSACTION;: Starts a new transaction.
• ROLLBACK TRANSACTION;: Reverts all changes made since the transaction began.
• COMMIT TRANSACTION;: Finalizes all changes made within the transaction.

Adding Columns to Tables

To add a new column to an existing table, use the ALTER TABLE statement with the ADD clause.

ALTER TABLE TableName
ADD ColumnName VARCHAR(20) NULL; -- Specify data type and nullability as needed
 

Adding an Auto-Incrementing ID Column

To add an integer column that automatically increments and serves as a primary key, use IDENTITY(seed, increment).

ALTER TABLE lianxi
ADD id INT PRIMARY KEY IDENTITY(1,1) NOT NULL;
 

This example sets the starting value (seed) to 1 and increments by 1 for each new row.

Querying Non-Empty Fields

To find rows where a specific field is not null and contains data, you can check for NULL and also verify the data length.

SELECT *
FROM YourTable
WHERE YourField IS NOT NULL AND DATALENGTH(YourField) <> 0;