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ę: 

    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

Leave a Comment

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *