Tworzenie tabeli w pamięci T-SQL

Jednym z wymagań dotyczących projektu było rozszerzenie istniejącej tablicy ’employee’ o jedną komunę: salary. Każdy istniejący rekord musi posiadać pewną wartość w nowej kolumnie. Został podesłany plik csv w którym zapisane były informacje z wartościami do nowej kolumny. Po stronie klienta można tylko odpalić wcześniej przygotowany skrypt. Skrypt powinien na początku stworzyć kopię aktualnej tabeli, a później wpisać wartości do nowej kolumny.

Na początku tabela ’employee’ po stronie klienta wygląda tak:

id	name	surname	PESEL
1	Jan	Kowalski	28092418406
2	Zbigniew	Kręcina	47101816477
3	John	Smith	55020101734
4	Albert	Nowak	21060309285

Plik dostarczony przez klienta:

name,surname,PESEL,salary
Jan,Kowalski,28092418406,5000
Zbigniew,Kr©cina,47101816477,2500
John,Smith,55020101734,4100
Albert,Nowak,21060309285,1800

Jak można rozwiązać ten problem?

1. Stworzyć kopię tabeli
2. Stworzyć tabelę w pamięci z dwoma kolumnami: PESEL oraz salary
3. Uzupełnić tą tabelę tymczasową danymi
4. Dodać do aktualnej tabelę nową kolumnę
5. Uzupełnić tabelę ’employee’ nowymi danymi.

Rozwiązanie:

1. Kopia będzie zapisana z informacją o aktualnej dacie i godzinie.

   DECLARE @currentDataAndHour CHAR(12)
   SELECT @currentDataAndHour = CONVERT(VARCHAR(24),GETDATE(),112) + CONVERT(varchar(2), DATEPART(hh,GETDATE())) +  CONVERT(varchar(2),DATEPART(mi,GETDATE()))
   DECLARE @nameOfBackupTable VARCHAR(100)
   SELECT @nameOfBackupTable = 'employee_Backup_' + @currentDataAndHour
   
   DECLARE @sSQL VARCHAR(max)
   SET @sSQL = 'SELECT * INTO ' + @nameOfBackupTable + ' FROM employee'
   PRINT @sSQL
   
   EXEC (@sSQL)
   

2. Dzięki numerowi PESEL będę rozpoznawać zatrudnioną osobę + oczywiście wypłata.

   DECLARE @tempTable TABLE(
    [PESEL] [CHAR](11) NOT NULL,
    [Salary] [INT] NOT NULL
   )

3. Załóżmy, że importowanie plików w SQL Managment Studio nie działa :). Wchodzę do Excel, usuwam niepotrzebne kolumn i zapisuję plik w csv:
   

   PESEL,salary
   28092418406,5000
   47101816477,2500
   55020101734,4100
   21060309285,1800

   Następnie na stronie http://www.convertcsv.com/csv-to-sql.htm. Wrzucam plik z csv, wybieram opcję:  First row is column names, wpisuję nazwę tabeli do której mają być dodane rekordy: @tempTable. Klikam CSV TO SQL INSERT i kopiuje inserty:

   INSERT INTO @tempTable(PESEL,salary) VALUES (28092418406,5000);
   INSERT INTO @tempTable(PESEL,salary) VALUES (47101816477,2500);
   INSERT INTO @tempTable(PESEL,salary) VALUES (55020101734,4100);
   INSERT INTO @tempTable(PESEL,salary) VALUES (21060309285,1800);
   

4. Dodanie nowej kolumny do istniejącej tabeli:

   ALTER TABLE employee
   ADD salary [INT]

    

5. Sprawdzam czy PESEL jest taki sam. Jeżeli tak do podmieniam wypłatę.

   UPDATE [Warehouse].[dbo].[employee]
   SET salary = temp.salary
   FROM employee empl
   LEFT JOIN @tempTable temp
   ON empl.PESEL = temp.PESEL

Całość prezentuje się tak:

DECLARE @currentDataAndHour CHAR(12)
SELECT @currentDataAndHour = CONVERT(VARCHAR(24),GETDATE(),112) + CONVERT(varchar(2), DATEPART(hh,GETDATE())) +  CONVERT(varchar(2),DATEPART(mi,GETDATE()))
DECLARE @nameOfBackupTable VARCHAR(100)
SELECT @nameOfBackupTable = 'employee_Backup_' + @currentDataAndHour

DECLARE @sSQL VARCHAR(max)
SET @sSQL = 'SELECT * INTO ' + @nameOfBackupTable + ' FROM employee'
PRINT @sSQL

EXEC (@sSQL)

DECLARE @tempTable TABLE(
 [PESEL] [CHAR](11) NOT NULL,
 [Salary] [INT] NOT NULL
)

INSERT INTO @tempTable(PESEL,salary) VALUES (28092418406,5000);
INSERT INTO @tempTable(PESEL,salary) VALUES (47101816477,2500);
INSERT INTO @tempTable(PESEL,salary) VALUES (55020101734,4100);
INSERT INTO @tempTable(PESEL,salary) VALUES (21060309285,1800);

ALTER TABLE [Warehouse].[dbo].[employee]
ADD salary [INT]

UPDATE [Warehouse].[dbo].[employee]
SET PESEL = temp.PESEL
FROM employee empl
LEFT JOIN @tempTable temp
ON empl.PESEL = temp.PESEL

 

id	name	surname	PESEL	salary
1	Jan	Kowalski	28092418406	5000
2	Zbigniew	Kręcina	47101816477	2500
3	John	Smith	55020101734	4100
4	Albert	Nowak	21060309285	1800