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
sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License