WYBIERZ NA zmienną tabelową w T-SQL


372

Mam złożone zapytanie SELECT, z którego chciałbym wstawić wszystkie wiersze do zmiennej tabeli, ale T-SQL na to nie pozwala.

W tym samym wierszu nie można używać zmiennej tabeli z zapytaniami SELECT INTO lub INSERT EXEC. http://odetocode.com/Articles/365.aspx

Krótki przykład:

declare @userData TABLE(
                        name varchar(30) NOT NULL,
                        oldlocation varchar(30) NOT NULL
                       )

SELECT name, location
INTO @userData
FROM myTable
    INNER JOIN otherTable ON ...
WHERE age > 30

Dane w zmiennej tabeli zostaną później wykorzystane do wstawienia / aktualizacji z powrotem do różnych tabel (głównie kopia tych samych danych z niewielkimi aktualizacjami). Celem tego byłoby po prostu uczynienie skryptu nieco bardziej czytelnym i łatwiejszym do dostosowania niż wykonywanie SELECT INTObezpośrednio w odpowiednich tabelach. Wydajność nie stanowi problemu, ponieważ rowcountjest dość mała i jest uruchamiana ręcznie tylko w razie potrzeby.
... lub po prostu powiedz mi, czy robię to wszystko źle.

Odpowiedzi:


601

Wypróbuj coś takiego:

DECLARE @userData TABLE(
    name varchar(30) NOT NULL,
    oldlocation varchar(30) NOT NULL
);

INSERT INTO @userData (name, oldlocation)
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;

2
Jeśli wybierzesz „SELECT name, location FROM myTable” jako wartości, które wstawisz do tabeli UserData, nie ma znaczenia, czy nazwy zmiennych w select są zgodne z nazwami w definicji tabeli. Wybieracie „name”, aby przejść do zmiennej UserData „name”, ale wybieracie „location” i jakoś przypisujecie ją do zmiennej „oldlocation” UserData. Czy SQL po prostu zmapuje je automatycznie, czy wygeneruje jakiś wyjątek?
Aran Mulholland,

Nie ma znaczenia nazwa, tylko typ kolumny.
CristiC,

5
Wow, to ma sens, ale jednocześnie parser we mnie czuje się obrażony :)
Aran Mulholland

Wydaje mi się, że nie mogę tego użyć w instrukcjach UPDATE: gist link
Paul-Sebastian Manole

1
W instrukcji insert, jeśli kolumny nie zostaną jawnie zadeklarowane, wówczas zostaną odwzorowane w kolejności zadeklarowanej w oryginalnej instrukcji tworzenia tabeli, podobnie jak robi to select *. Lokalizacja w instrukcji select jest odwzorowana na starą lokalizację w tabeli @userData, ponieważ lokalizacja znajduje się w pozycji 2 w zestawie wyników wyboru, a stara lokalizacja to kolumna 2 w definicji tabeli. To powiedziawszy, nigdy tego nie rób. Nie można polegać na porządkowaniu w bazie danych kolumn lub wierszy. Zawsze mów o tym wyraźnie.
absmici

94

Celem SELECT INTOjest (według dokumentów, mój nacisk)

Aby utworzyć nową tabelę na podstawie wartości w innej tabeli

Ale ty już masz tabelę docelową! Więc czego chcesz

INSERTSprawozdanie dodaje jeden lub więcej nowych wierszy do tabeli

Możesz określić wartości danych w następujący sposób:

...

Za pomocą SELECTpodzapytania do określenia wartości danych dla jednego lub więcej wierszy, takich jak:

  INSERT INTO MyTable 
 (PriKey, Description)
        SELECT ForeignKey, Description
        FROM SomeView

W tej składni dozwolone MyTablejest bycie zmienną tabelową.


1
Naprawdę chciałbym, żeby zaakceptowana odpowiedź zawierała te informacje!
Davie Brown,

Rozumiem, że MyTable robi to „Nieprawidłowa nazwa obiektu”, więc w tej odpowiedzi brakuje czegoś.
Mike Flynn,

@MikeFlynn MyTabletutaj jest symbolem zastępczym nazwy Twojej aktualnej tabeli . Nie sądzę, żeby istniały prawdziwe bazy danych z tabelą o nazwie MyTable...
AakashM

A jeśli chcę utworzyć / zadeklarować zmienną tabelową za pomocą SELECT INTO ...? Na przykład, aby zdefiniować kolumny zmiennej tabeli jako t1.somecolumn, t1.othercolumn, t2. *
Armando

27

Możesz także użyć typowych wyrażeń tabelowych do przechowywania tymczasowych zestawów danych. Są bardziej eleganccy i przyjaźni adhoc:

WITH userData (name, oldlocation)
AS
(
  SELECT name, location 
  FROM   myTable    INNER JOIN 
         otherTable ON ...
  WHERE  age>30
)
SELECT * 
FROM   userData -- you can also reuse the recordset in subqueries and joins

Kocham to! Dziękuję Ci.
fourpastmidnight

Nie sądzę, że to robi kopię, jeśli usuniesz lub zaktualizujesz dane użytkownika, czy nie usunie i nie zaktualizuje rekordów w twoich oryginalnych tabelach?
atreeon

Tak, DELETE i UPDATE na CTE zmodyfikują tabelę źródłową, o ile CTE nie odwołuje się do wielu tabel przy użyciu złączeń, związków itp.
nanestev

2
Wadą tego jest to, że tabeli CTE można używać tylko w następujących bezpośrednio poleceniach. Jeśli z jakiegokolwiek powodu konieczne jest wykonanie więcej niż jednego przejścia przez zestaw wyników, CTE nie będzie działać. OP wydaje się sugerować, że zostaną dokonane wielokrotne modyfikacje, w którym to przypadku nie zadziała - „Dane w zmiennej tabeli zostaną później wykorzystane do wstawienia / aktualizacji z powrotem do różnych tabel (głównie kopia tych samych danych z niewielkimi aktualizacje). ”
Tony

16

Możesz spróbować użyć tabel tymczasowych ... jeśli nie robisz tego z aplikacji. (Uruchomienie tego ręcznie może być w porządku)

SELECT name, location INTO #userData FROM myTable
INNER JOIN otherTable ON ...
WHERE age>30

Pomijasz wysiłek, aby zadeklarować tabelę w ten sposób ... Pomaga w zapytaniach adhoc ... Tworzy to lokalną tabelę tymczasową, która nie będzie widoczna dla innych sesji, chyba że jesteś w tej samej sesji. Być może problem występuje w przypadku uruchamiania zapytania z aplikacji.

jeśli potrzebujesz, aby działało w aplikacji, użyj zmiennych zadeklarowanych w ten sposób:

DECLARE @userData TABLE(
    name varchar(30) NOT NULL,
    oldlocation varchar(30) NOT NULL
);

INSERT INTO @userData
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;

Edycja: jak wielu wspomniało o zaktualizowanej widoczności sesji od połączenia. Tworzenie tabel tymczasowych nie jest opcją dla aplikacji internetowych, ponieważ sesje mogą być ponownie użyte, w takich przypadkach trzymaj się zmiennych tymczasowych


2
Przepraszam, zapomniałem wspomnieć, że nie mam uprawnień do CREATE TABLE.
Indrek

6
Tworzenie temp ma trochę więcej kosztów ogólnych.
paparazzo

2
korzystanie z tabeli temp nie zawsze jest bezpieczne. Na przykład usługi sieciowe. W przypadku usług sieciowych z jednym połączeniem, aby ograniczyć maksymalne połączenie na serwerze ORAZ jeszcze bardziej chronić SQL, tabela tymczasowa będzie istniała dla KAŻDEGO przechodzącego zapytania i może zastąpić kogoś, kto aktualnie go używa.
Franck

12
@Franck - jeśli używasz globalnej tabeli temp (dwa prefiksy skrótu) masz rację. Jednak lokalna tabela tymczasowa (jeden prefiks skrótu) zostanie odizolowana do pojedynczej sesji (inaczej pojedynczego połączenia), więc nie będzie problemów z współbieżnością, o których wspominasz, chyba że używasz jednego połączenia dla wszystkich żądań (nie rozmyślny). Możliwe są jednak implikacje dotyczące wydajności.
maf748,

@GazB Jasne, każde oświadczenie ze skutkiem ubocznym jest wykluczone z użycia w function. Z mojego doświadczenia wynika, że ​​w większości przypadków, gdy ktoś myśli, że potrzebuje takich oświadczeń, w rzeczywistości oznacza to, że powinien przemyśleć swoje function- lub przynajmniej zmienić je na procedure. Przynajmniej mówię za siebie. :-)
underscore_d


5

Najpierw utwórz tabelę tymczasową:

Krok 1:

create table #tblOm_Temp (

    Name varchar(100),
    Age Int ,
    RollNumber bigint
)

** Krok 2: ** Wstaw pewną wartość do tabeli temperatur.

insert into #tblom_temp values('Om Pandey',102,1347)

Krok 3: Zadeklaruj zmienną tabeli, aby przechowywać dane tabeli temp.

declare   @tblOm_Variable table(

    Name Varchar(100),
    Age int,
    RollNumber bigint
)

Krok 4: wybierz wartość z tabeli temp i wstaw do zmiennej tabeli.

insert into @tblOm_Variable select * from #tblom_temp

Na koniec wartość jest wstawiana z tabeli tymczasowej do zmiennej tabeli

Krok 5: Można sprawdzić wstawioną wartość w zmiennej tabeli.

select * from @tblOm_Variable

1

OK, teraz przy wystarczającym wysiłku jestem w stanie wstawić do @table, używając poniższego:

WSTAW @TempWithheldTable WYBIERZ
a.SuspendedReason, a.SuspendedNotes, a.SuspendedBy, a.ReasonCode FROM OPENROWSET (BULK 'C: \ DataBases \ WithHeld.csv', FORMATFILE = N'C: \ DataBases \ Format.txt ',
ERRORFILE N'C: \ Temp \ MovieLensRatings.txt ') AS a;

Najważniejsze jest tutaj wybranie kolumn do wstawienia.


Pojawia się komunikat o błędzie kompilacji „Muszę zadeklarować zmienną tabeli„ @TempWithheldTable ”
atreeon,

-5

Jednym z powodów użycia WYBIERZ W jest to, że pozwala ono na użycie TOŻSAMOŚCI:

SELECT IDENTITY(INT,1,1) AS Id, name
INTO #MyTable 
FROM (SELECT name FROM AnotherTable) AS t

Nie działałoby to ze zmienną tabelową, co jest zbyt złe ...


6
Możesz jednak zadeklarować zmienną tabelową za pomocą IDENTITYkolumny.
Martin Smith
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.