Как скопировать таблицу с помощью SELECT INTO, но игнорировать свойство IDENTITY?

У меня есть таблица с столбцом с идентификатором:

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

Хорошо известно, что этот

select * into copy_from_with_id_1 from with_id;

приводит к copy_from_with_id_1 с идентификатором на идентификаторе.

Следующий вопрос о переполнении стека явно перечисляет все столбцы.

Попробуем

select id, val into copy_from_with_id_2 from with_id;

К сожалению, даже в этом случае id является столбцом идентификации.

То, что я хочу, это таблица типа

create table without_id (
 id int,
 val varchar(30)
);
36 голосов | спросил bernd_k 26 Jpm1000000pmWed, 26 Jan 2011 19:28:07 +030011 2011, 19:28:07

10 ответов


46

Из онлайн-книг

  

Формат new_table определяется путем оценки выражений в списке выбора. Столбцы в new_table создаются в порядке, указанном в списке выбора. Каждый столбец в new_table имеет одно и то же имя, тип данных, значение nullability и значение как соответствующее выражение в списке выбора. Свойство IDENTITY для столбца передается , за исключением условий, определенных в разделе «Работа с столбцами идентификаторов» в разделе «Примечания».

Вниз по странице:

  

Когда существующий столбец идентификации выбран в новую таблицу, новый столбец наследует свойство IDENTITY, если не выполняется одно из следующих условий:

     
  • Оператор SELECT содержит предложение join, GROUP BY или агрегацию.
  •   
  • Несколько операторов SELECT объединяются с помощью UNION.
  •   
  • Столбец идентификатора указан более чем один раз в списке выбора.
  •   
  • Столбец идентичности является частью выражения.
  •   
  • Идентификационный столбец из удаленного источника данных.
  •   

Если какое-либо из этих условий истинно, столбец создается NOT NULL вместо наследования свойства IDENTITY. Если в новой таблице требуется столбец идентификатора, но такой столбец недоступен или вы хотите, чтобы значение семени или приращения, отличное от столбца идентификатора источника, определите столбец в списке выбора с помощью функции IDENTITY. См. «Создание столбца идентификации с помощью функции IDENTITY» в разделе «Примеры» ниже.

Итак ... теоретически можно уйти с:

select id, val 
into copy_from_with_id_2 
from with_id

union all

select 0, 'test_row' 
where 1 = 0;

Было бы важно прокомментировать этот код, чтобы объяснить это, чтобы он не удалился в следующий раз, когда кто-то посмотрит на него.

ответил Eric Humphrey - lotsahelp 26 Jpm1000000pmWed, 26 Jan 2011 21:46:50 +030011 2011, 21:46:50
24

Вдохновленный ответом Эрикс, я нашел следующее решение, которое зависит только от имен таблиц и не использует никакого имени конкретного столбца:

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;

Edit

Можно даже улучшить это до

select * into without_id from with_id
union all
select * from with_id where 1 = 0
;
ответил bernd_k 27 Jam1000000amThu, 27 Jan 2011 09:41:03 +030011 2011, 09:41:03
12

Вы можете использовать соединение для создания и заполнения новой таблицы за один раз:

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

Из-за условия 1 = 0, правая сторона не будет иметь совпадений и, таким образом, предотвратит дублирование строк в левой части, а поскольку это внешнее соединение, то строки в левой части не будут также устранены. Наконец, поскольку это соединение, свойство IDENTITY исключается.

При выборе только столбцов левой стороны будет создана точная копия dbo.TableWithIdentity только для данных, т. е. с отключенным свойством IDENTITY.

Все, что было сказано, Макс Вернон поднял действительный момент в комментарии, который стоит сохранить в уме. Если вы посмотрите план выполнения вышеуказанного запроса:

План выполнения

вы заметите, что исходная таблица упоминается в плане выполнения только один раз. Другой экземпляр был устранен оптимизатором.

Итак, если оптимизатор может правильно установить, что правая сторона соединения не нужна в плане, то разумно ожидать, что в будущей версии SQL Server она сможет выяснить, что свойство IDENTITY также не нужно удалять, поскольку в исходной строке, установленной в соответствии с планом запроса, больше нет другого столбца IDENTITY. Это означает, что указанный выше запрос может перестать работать как ожидалось в какой-то момент.

Но, как правильно отмечено ypercubeáμ € ¹'¹ , пока в руководстве явно указано, что если есть соединение, свойство IDENTITY не сохраняется

  

Когда существующий столбец идентификаторов выбран в новую таблицу, новый столбец наследует свойство IDENTITY, если только [...] [t] оператор SELECT не содержит соединение.

Итак, до тех пор, пока руководство продолжает упоминать об этом, мы, возможно, можем быть уверены, что поведение останется прежним.

Признаки Shaneis и ypercubeáμtheá¹¹ для воссоздания связанной темы в чате.

ответил Andriy M 12 Maypm16 2016, 16:08:42
5

Попробуйте этот код ..

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

Вызов ISNULL гарантирует, что новый столбец создан с нулевым значением NOT NULL.

ответил Saurav Ghosh 27 Jpm1000000pmMon, 27 Jan 2014 16:02:39 +040014 2014, 16:02:39
3

Просто, чтобы показать другой способ:

Вы можете использовать связанный сервер .

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

Вы можете временно создать связанный сервер на локальном сервере, используя это:

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;

В этот момент вы запустите код select * into, ссылаясь на localserver связанный сервер с четырьмя частями:

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

После этого очистите связанный сервер localserver с этим:

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

Или вы можете использовать синтаксис OPENQUERY

SELECT * 
INTO without_id 
FROM OPENQUERY([linked_server], 'SELECT * FROM [source_db].dbo.[with_id]');
ответил bernd_k 13 FebruaryEurope/MoscowbSun, 13 Feb 2011 17:22:42 +0300000000pmSun, 13 Feb 2011 17:22:42 +030011 2011, 17:22:42
1

Свойство идентификации не передается, если оператор select содержит объединение, и поэтому

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

также даст желаемое поведение (скопированного столбца id), чтобы не сохранить свойство IDENTITY. Однако у него будет побочный эффект не копирования любой строки в все! (как и с некоторыми другими методами), поэтому вам нужно будет:

insert into without_id select * from with_id;

(спасибо AakashM!)

ответил anon-99 1 MarpmTue, 01 Mar 2016 12:49:03 +03002016-03-01T12:49:03+03:0012 2016, 12:49:03
1

Легкий способ - сделать часть столбца выражения.

Пример:
Если таблица dbo.Employee имеет идентификатор в столбце ID, то в приведенном ниже примере таблица temp #t также будет иметь идентификатор в столбце ID.

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

Измените это, чтобы применить выражение к ID, и у вас #t больше не будет столбца IDENTITY on ID. В этом случае мы применяем простое дополнение к столбцу идентификатора.

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

Другие примеры выражений для других типов данных могут включать: convert (), конкатенацию строк или Isnull ()

ответил FistOfFury 25 +03002016-10-25T01:11:22+03:00312016bEurope/MoscowTue, 25 Oct 2016 01:11:22 +0300 2016, 01:11:22
1

Иногда вы хотите вставить из таблицы, где вы не знаете (или не заботитесь), если столбец был создан с использованием IDENTITY или нет. Он может даже не быть целым столбцом, с которым вы работаете. В этом случае будет работать следующее:

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

ISNULL удаляет атрибут IDENTITY из столбца, но вставляет его с тем же именем и тип, что и исходный столбец, а также делает его недействительным. TOP (0) создаст пустую таблицу, которую вы можете использовать для вставки выбранных строк. Вы также можете сделать таблицу сжатой, прежде чем вставлять данные, если это необходимо.

ответил Tony 28 FebruaryEurope/MoscowbWed, 28 Feb 2018 23:18:14 +0300000000pmWed, 28 Feb 2018 23:18:14 +030018 2018, 23:18:14
0
select convert(int, id) as id, val 
into copy_from_with_id_without_id 
from with_id;

удалит идентификатор.

Недостатком является то, что id становится допустимым, но вы можете добавить это ограничение.

ответил john hunter 15 +03002017-10-15T18:20:00+03:00312017bEurope/MoscowSun, 15 Oct 2017 18:20:00 +0300 2017, 18:20:00
-2

Нет. select * into сохраняет личность.

ответил Mladen Prajdic 26 Jpm1000000pmWed, 26 Jan 2011 20:41:07 +030011 2011, 20:41:07

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132