Spicker: T-SQL

Mehrere Datensäte mit einer INSERT-Anweisung anfügen:
Inserting multiple records using one insert statement:

Lösung/Solution 1:

CREATE TABLE  tblNames (Name char(10), SurName char(10));
 
INSERT INTO [dbo].[tblNames] (Name, SurName)
SELECT 'Robert', 'Mayer'
UNION ALL
SELECT 'Julian', 'Smith'
UNION ALL
SELECT 'John', 'Doe';
 
SELECT * FROM [dbo].[tblNames];
 
-- DROP TABLE [dbo].[tblNames];  -- removing table

Lösung/Solution 2:
(ab/until SQL-Server 2008)

CREATE TABLE tblNames (Name char(10), SurName char(10));
 
INSERT INTO [dbo].[tblNames] (Name, SurName)
VALUES ('Robert', 'Mayer')
    , ('Julian', 'Smith')
    , ('John', 'Doe');
 
SELECT * FROM [dbo].[tblNames];
 
-- DROP TABLE [dbo].[tblNames];  -- removing table

Beispiel für einen TRIGGER der auf INSERT oder UPDATE Ereignisse einer Tabelle reagiert:
Example for a TRIGGER responding to an INSERT or UPDATE event of a table:

CREATE TABLE tblNames (ID int, Name char(10), SurName char(10), [DateOfLastChange] [datetime]);
GO
 
-- CORE SECTION START
CREATE TRIGGER [dbo].[trgNames_OnInsertOrUpdate]
    ON  [dbo].[tblNames]
    FOR INSERT,UPDATE
AS 
UPDATE [dbo].[tblNames] SET DateOfLastChange = CURRENT_TIMESTAMP WHERE ID IN (SELECT ID FROM INSERTED)
GO
 
ENABLE TRIGGER [dbo].[trgNames_OnInsertOrUpdate] ON [dbo].[tblNames];
GO
-- CORE SECTION END
 
INSERT INTO [dbo].[tblNames] (ID, Name, SurName) VALUES (1, 'Robert', 'Mayer'), (2, 'Julian', 'Smith') , (3, 'John', 'Doe');
 
SELECT * FROM dbo.tblNames;
GO
 
WAITFOR DELAY '00:00:05';   -- sleep/wait for 3 seconds
 
UPDATE [dbo].[tblNames] SET SurName = 'Meier' WHERE ID = 1;   -- doing a change 
INSERT INTO [dbo].[tblNames] (ID, Name, SurName) VALUES (4, 'Andreas', 'Huber');   -- adding a new record
 
SELECT * FROM [dbo].[tblNames];
 
-- DROP TABLE [dbo].[tblNames];  -- removing table


Handling DATABASES:

-- Show all databases
USE master;
SELECT * FROM master.dbo.sysdatabases;
 
-- Show a specific databases
USE master;
SELECT * FROM master.dbo.sysdatabases WHERE (name like N'ASPState');

Handling TABLES:

-- Show USER TABLES of a specific databases
USE  master;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE like N'BASE TABLE' ORDER BY TABLE_NAME;
SELECT * FROM sys.tables ORDER BY name;
 
 -- Show ALL TABLES of a specific databases
USE  master;
SELECT * FROM sysobjects WHERE xtype IN (N'S', N'IT', N'U') ORDER BY xtype;
-- IT = Internal table
-- S  = System base table
-- U  = Table (user-defined)

Handling VIEWS:

-- Show all USER VIEWS of a specific databases
USE master;
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE like N'VIEW';
SELECT * FROM sys.views;
 
-- Show all ALL VIEWS of a specific databases
USE master;
SELECT * FROM sysobjects WHERE xtype = N'V' ORDER BY uid, name;

Handling COLUMNS:

-- Show all COLUMNS of a specific databases
USE ASPState;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
 
-- Show all COLUMNS in a specific table
USE ASPState;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like N'ASPStateTempSessions';
 
-- Show a specific COLUMN in a specific table
USE ASPState;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like N'ASPStateTempSessions' AND COLUMN_NAME like N'LockDate' ;

https://docs.microsoft.com/de-de/sql/relational-databases/system-catalog-views/sys-objects-transact-sql



DECLARE @Useroptions TABLE 
( 
   id smallint NOT NULL, 
   configuration varchar(25),
   status varchar(3) DEFAULT 'OFF'
);
 
INSERT INTO @Useroptions (id, configuration) VALUES (1, N'DISABLE_DEF_CNST_CHK');
INSERT INTO @Useroptions (id, configuration) VALUES (2, N'IMPLICIT_TRANSACTIONS');
INSERT INTO @Useroptions (id, configuration) VALUES (4, N'CURSOR_CLOSE_ON_COMMIT');
INSERT INTO @Useroptions (id, configuration) VALUES (8, N'ANSI_WARNINGS');
INSERT INTO @Useroptions (id, configuration) VALUES (16, N'ANSI_PADDING');
INSERT INTO @Useroptions (id, configuration) VALUES (32, N'ANSI_NULLS');
INSERT INTO @Useroptions (id, configuration) VALUES (64, N'ARITHABORT');
INSERT INTO @Useroptions (id, configuration) VALUES (128, N'ARITHIGNORE');
INSERT INTO @Useroptions (id, configuration) VALUES (256, N'QUOTED_IDENTIFIER');
INSERT INTO @Useroptions (id, configuration) VALUES (512, N'NOCOUNT');
INSERT INTO @Useroptions (id, configuration) VALUES (1024, N'ANSI_NULL_DFLT_ON');
INSERT INTO @Useroptions (id, configuration) VALUES (2048, N'ANSI_NULL_DFLT_OFF');
INSERT INTO @Useroptions (id, configuration) VALUES (4096, N'CONCAT_NULL_YIELDS_NULL');
INSERT INTO @Useroptions (id, configuration) VALUES (8192, N'NUMERIC_ROUNDABORT');
INSERT INTO @Useroptions (id, configuration) VALUES (16384, N'XACT_ABORT');
 
UPDATE @Useroptions SET status = N'ON' WHERE ( (id & @@OPTIONS) = id );
 
SELECT configuration, status FROM @Useroptions;
DECLARE @Useroptions TABLE 
( 
    id smallint NOT NULL IDENTITY(0,1) PRIMARY KEY, 
    configuration varchar(25) NOT NULL UNIQUE,
    status varchar(3) DEFAULT 'OFF'
);
 
INSERT INTO @Useroptions (configuration) VALUES (N'DISABLE_DEF_CNST_CHK'), (N'IMPLICIT_TRANSACTIONS'), (N'CURSOR_CLOSE_ON_COMMIT'), (N'ANSI_WARNINGS'), (N'ANSI_PADDING'), (N'ANSI_NULLS'), (N'ARITHABORT'), (N'ARITHIGNORE'), (N'QUOTED_IDENTIFIER'), (N'NOCOUNT'), (N'ANSI_NULL_DFLT_ON'), (N'ANSI_NULL_DFLT_OFF'), (N'CONCAT_NULL_YIELDS_NULL'), (N'NUMERIC_ROUNDABORT'), (N'XACT_ABORT');
 
UPDATE @Useroptions SET status = N'ON' WHERE ( (POWER(2,id) & @@OPTIONS) = POWER(2,id) );
 
SELECT configuration, status FROM @Useroptions;
sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License