====== MSSQL - sqlcmd ======

===== Example Environment =====

^Description^Value^
|Hostname / Container Name|mssqlsrv|
|MSSQL User|sa|
|MSSQL User sa Password|jeWzuQ7ZRRmH|
|MSSQL Port|1433|


===== Connect =====

<code>
sqlcmd -S "mssqlsrv,1433" -U "sa" -P "jeWzuQ7ZRRmH" -y 30 -Y 30
</code>

===== Run as Docker Container =====

Microsoft provides the [[https://hub.docker.com/r/microsoft/mssql-tools|mcr.microsoft.com/mssql-tools]] Docker image which contains sqlcmd.

Pull the image
<code bash>
docker pull mcr.microsoft.com/mssql-tools
</code>

Create a container with this image and enter the shell.
<code ->
docker run -it mcr.microsoft.com/mssql-tools
</code>

In the container you can use the sqlcmd
<code ->
root@server:~# docker run -it mcr.microsoft.com/mssql-tools
root@192e24c47a64:/# sqlcmd -S "mssqlsrv,1433" -U "sa" -P "jeWzuQ7ZRRmH" -y 30 -Y 30
1> 
</code>

Use the ''--net'' parameter to make sure, sqlcmd can connect to your MSSQL container.
<code ->
docker run -it --net container:mssqlsrv mcr.microsoft.com/mssql-tools
</code>

You can also create the container and connect with sqlcmd in a single command
<code bash>
docker run -it --net container:mssqlsrv mcr.microsoft.com/mssql-tools /opt/mssql-tools/bin/sqlcmd -S "mssqlsrv,1433" -U "sa" -P "jeWzuQ7ZRRmH" -y 30 -Y 30
1>
</code>


===== Queries =====

With the keyword ''GO'' on a single line, a line ending is indicated.

==== List Databases ====

<code sql>
SELECT name, database_id, create_date FROM sys.databases;
GO
</code>

<code ->
1> SELECT name, database_id, create_date FROM sys.databases;
2> GO
name                         database_id create_date
---------------------------- ----------- -----------------------
master                                 1 2003-04-08 09:13:36.390
tempdb                                 2 2024-08-14 08:28:02.960
model                                  3 2003-04-08 09:13:36.390
msdb                                   4 2024-07-10 14:43:42.733
Customers                              5 2024-07-31 07:10:39.220

(5 rows affected)
1>
</code>

==== Create a Database with a Table ====

=== Create Database ===

Switch to the master database context and create a new database. [[https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver16&tabs=sqlpool|CREATE DATABASE]]

<code sql>
USE master;
GO
CREATE DATABASE Company;
GO
</code>

=== Switch to Database Context ===

Switch to the database context with [[https://learn.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql?view=sql-server-ver16|USE]].

<code sql>
USE Company;
GO
</code>

=== Create Table ===

Create a basic table. [[https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16|CREATE TABLE]]

<code sql>
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
);
GO
</code>

=== Add Records ===

Add two records to the table. [[https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16|INSERT]]

<code sql>
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2024-08-01');
GO
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Jane', 'Doe', '2024-08-18');
GO
</code>

==== Delete Table ====

[[https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-ver16|DROP TABLE]]

<code sql>
DROP TABLE Employees;
GO;
</code>

==== Delete Database ====

[[https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver16|DROP DATABASE]]

<code sql>
DROP DATABASE Company;
GO
</code>

==== Show Server Version ====

<code sql>
SELECT @@VERSION As 'Server Version';
GO
</code>

<code ->
1> SELECT @@VERSION As 'Server Version';
2> GO
Server Version                
------------------------------
Microsoft SQL Server 2022 (RTM

(1 rows affected)
</code>

===== Links =====

  * [[https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-scripting-variables?view=sql-server-ver16#sqlcmd-scripting-variables|sqlcmd scripting variables]]
  * [[https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16|Collation and Unicode support]]