Jak skopiować tabelę za pomocą SELECT INTO, ale zignorować właściwość TOŻSAMOŚĆ?

Mam tabelę z kolumną tożsamości powiedzieć:

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

Dobrze wiadomo, że to

select * into copy_from_with_id_1 from with_id;

powoduje także copy_from_with_id_1 z tożsamością na id.

W poniższym pytaniu dotyczącym przepełnienia stosu wymieniono jawnie listę wszystkich kolumn.

Spróbujmy

select id, val into copy_from_with_id_2 from with_id;

Ups, nawet w tym przypadku id jest kolumną tożsamości.

To, czego chcę, to stół

create table without_id (
 id int,
 val varchar(30)
);

Z książek online

Format nowej_tabeli określa się, oceniając wyrażenia na liście wyboru. Kolumny w nowej_tabeli są tworzone w kolejności określonej przez listę wyboru. Każda kolumna w tabeli nowa_ ma taką samą nazwę, typ danych, dopuszczalność nullab i wartość jak odpowiednie wyrażenie na liście wyboru. Przenoszona jest właściwość TOŻSAMOŚĆ kolumny, z wyjątkiem warunków określonych w „Praca z kolumnami tożsamości” w sekcji Uwagi.

W dół strony:

Po wybraniu istniejącej kolumny tożsamości do nowej tabeli nowa kolumna dziedziczy właściwość TOŻSAMOŚĆ, chyba że spełniony jest jeden z następujących warunków:

• Instrukcja SELECT zawiera funkcję join, GROUP BY lub funkcję agregującą.
• Wiele instrukcji SELECT jest łączonych za pomocą UNION.
• Kolumna tożsamości znajduje się na liście wyboru więcej niż jeden raz.
• Kolumna tożsamości jest częścią wyrażenia.
• Kolumna tożsamości pochodzi ze zdalnego źródła danych.

Jeśli którykolwiek z tych warunków jest spełniony, kolumna jest tworzona NIE NULL zamiast dziedziczenia właściwości TOŻSAMOŚĆ. Jeśli w nowej tabeli wymagana jest kolumna tożsamości, ale taka kolumna nie jest dostępna lub chcesz mieć wartość początkową lub przyrostową inną niż kolumna tożsamości źródłowej, zdefiniuj kolumnę na liście wyboru za pomocą funkcji TOŻSAMOŚĆ. Zobacz „Tworzenie kolumny tożsamości za pomocą funkcji TOŻSAMOŚĆ” w sekcji Przykłady poniżej.

Więc ... teoretycznie można uciec:

select id, val 
into copy_from_with_id_2 
from with_id

union all

select 0, 'test_row' 
where 1 = 0;

Ważne byłoby skomentowanie tego kodu, aby go wyjaśnić, aby nie został usunięty następnym razem, gdy ktoś na niego spojrzy.

Zainspirowany odpowiedzią Ericssona znalazłem następujące rozwiązanie, które zależy tylko od nazw tabel i nie używa żadnej konkretnej nazwy kolumny:

select * into without_id from with_id where 1 = 0
union all
select * from with_id where 1 = 0
;
insert into without_id select * from with_id;

Edytować

Można nawet to poprawić

select * into without_id from with_id
union all
select * from with_id where 1 = 0
;

Możesz użyć złączenia, aby utworzyć i wypełnić nową tabelę za jednym razem:

SELECT
  t.*
INTO
  dbo.NewTable
FROM
  dbo.TableWithIdentity AS t
  LEFT JOIN dbo.TableWithIdentity ON 1 = 0
;

Z powodu tego 1 = 0warunku prawa strona nie będzie pasowała, a zatem zapobiegnie duplikacji rzędów po lewej stronie, a ponieważ jest to połączenie zewnętrzne, rzędy po lewej stronie również nie zostaną wyeliminowane. Wreszcie, ponieważ jest to sprzężenie, właściwość TOŻSAMOŚĆ została wyeliminowana.

Dlatego wybranie tylko lewej kolumny spowoduje utworzenie dokładnej kopii danych dbo.TableWithIdentity tylko w zakresie danych, tj. Z usuniętą właściwością IDENTITY.

Biorąc to wszystko pod uwagę, Max Vernon podniósł ważny punkt w komentarzu, o którym warto pamiętać. Jeśli spojrzysz na plan wykonania powyższego zapytania:

zauważysz, że tabela źródłowa jest wymieniona w planie wykonania tylko raz. Drugi przypadek został wyeliminowany przez optymalizator.

Jeśli więc optymalizator może poprawnie ustalić, że prawidłowa strona złączenia nie jest potrzebna w planie, uzasadnione jest oczekiwanie, że w przyszłej wersji programu SQL Server może być w stanie stwierdzić, że właściwość TOŻSAMOŚĆ nie musi być usunięto również, ponieważ nie ma już innej kolumny TOŻSAMOŚCI w źródłowym zestawie wierszy zgodnie z planem zapytań. Oznacza to, że powyższe zapytanie może w pewnym momencie przestać działać zgodnie z oczekiwaniami.

Ale, jak słusznie zauważył ypercubeᵀᴹ , do tej pory instrukcja wyraźnie stwierdzała, że ​​jeśli istnieje sprzężenie, właściwość TOŻSAMOŚĆ nie jest zachowywana:

Gdy istniejąca kolumna tożsamości zostanie wybrana do nowej tabeli, nowa kolumna odziedziczy właściwość TOŻSAMOŚĆ, chyba że [...] [t] on instrukcja SELECT zawiera sprzężenie.

Tak więc, o ile instrukcja ciągle o tym wspomina, możemy być pewni, że zachowanie pozostanie takie samo.

_{Uznanie dla Shaneis i ypercubeᵀᴹ za wychowywanie pokrewnego tematu na czacie.}

Wypróbuj ten kod ..

SELECT isnull(Tablename_old.IDENTITYCOL + 0, -1) AS 'New Identity Column'
INTO   dbo.TableName_new
FROM   dbo.TableName_old 

ISNULLPołączenia zapewnia, że nowa kolumna jest tworzony z NOT NULLwartości null.

Aby pokazać inny sposób:

Możesz użyć połączonego serwera .

SELECT * 
INTO without_id 
FROM [linked_server].[source_db].dbo.[with_id];

Możesz tymczasowo utworzyć serwer połączony z serwerem lokalnym, używając:

DECLARE @LocalServer SYSNAME 
SET @LocalServer = @@SERVERNAME;
EXEC master.dbo.sp_addlinkedserver @server = N'localserver'
    , @srvproduct = ''
    , @provider = 'SQLNCLI'
    , @datasrc = @LocalServer;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'localserver'
    , @useself = N'True'
    , @locallogin = NULL
    , @rmtuser = NULL
    , @rmtpassword = NULL;

W tym momencie uruchomisz select * intokod, odwołując się do localserverczteroelementowej nazwy połączonego serwera:

SELECT * 
INTO without_id 
FROM [localserver].[source_db].dbo.[with_id];

Po zakończeniu wyczyść localserverpołączony serwer w następujący sposób:

EXEC sp_dropserver @server = 'localserver'
    , @droplogins = 'droplogins';

Lub możesz użyć OPENQUERYskładni

SELECT * 
INTO without_id 
FROM OPENQUERY([linked_server], 'SELECT * FROM [source_db].dbo.[with_id]');

Właściwość tożsamości nie jest przenoszona, jeśli instrukcja select zawiera złączenie i tak dalej

select a.* into without_id from with_id a inner join with_id b on 1 = 0;

da również pożądane zachowanie (skopiowanej idkolumny, aby nie zachowywać IDENTITYwłaściwości. Będzie jednak miało efekt uboczny polegający na tym, że nie kopiuje żadnego wiersza! (jak w przypadku niektórych innych metod), więc będziesz musiał zrobić:

insert into without_id select * from with_id;

(dzięki AakashM!)

Najprostszym sposobem jest uczynienie kolumny częścią wyrażenia.

Przykład:
Jeśli tabela dbo.Klient ma tożsamość w kolumnie ID, to w poniższym przykładzie tabela tymczasowa #t będzie miała również TOŻSAMOŚĆ w kolumnie ID.

--temp table has IDENTITY
select ID, Name 
into #t
from dbo.Employee

Zmień to, aby zastosować wyrażenie do identyfikatora, a Ty nie będziesz już mieć TOŻSAMOŚCI w kolumnie Identyfikator. W takim przypadku stosujemy prosty dodatek do kolumny identyfikatora.

--no IDENTITY
select ID = ID + 0, Name 
into #t
from dbo.Employee

Inne przykłady wyrażeń dla innych typów danych mogą obejmować: convert (), konkatenacja ciągów lub Isnull ()

Czasami chcesz wstawić z tabeli, w której nie wiesz (lub nie obchodzi cię), czy kolumna została utworzona przy użyciu TOŻSAMOŚCI, czy nie. Może nie być nawet kolumną z liczbą całkowitą, z którą pracujesz. W takim przypadku będą działać następujące funkcje:

SELECT TOP(0) ISNULL([col],NULL) AS [col], ... INTO [table2] FROM [table1]
ALTER TABLE [table2] REBUILD WITH (DATA_COMPRESSION=page)
INSERT INTO [table2] ...

ISNULL usunie atrybut TOŻSAMOŚĆ z kolumny, ale wstawi go o tej samej nazwie i typie co oryginalna kolumna, a także sprawi, że nie będzie on miał wartości zerowej. TOP (0) utworzy pustą tabelę, w której możesz następnie wstawić wybrane wiersze. W razie potrzeby tabelę można również skompresować przed wstawieniem danych.

select convert(int, id) as id, val 
into copy_from_with_id_without_id 
from with_id;

usunie tożsamość.

Minusem jest to, że idstaje się zerowalne, ale można dodać to ograniczenie.

Ty nie. select * intozachowuje tożsamość.