Я хочу иметь возможность вставлять данные из таблицы со столбцом идентификаторов во временную таблицу в SQL Server 2005.
TSQL выглядит примерно так:
-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM MyTable
WHERE 1=0
...
WHILE ...
BEGIN
...
INSERT INTO #Tmp_MyTable
SELECT TOP (@n) *
FROM MyTable
...
END
Приведенный выше код создал #Tmp_Table со столбцом идентификаторов, и вставка впоследствии завершилась ошибкой «Явное значение столбца идентификаторов в таблице '#Tmp_MyTable' можно указать только тогда, когда используется список столбцов и IDENTITY_INSERT включен».
Есть ли способ в TSQL удалить свойство идентификатора столбца во временной таблице без явного перечисления всех столбцов? Я специально хочу использовать «SELECT *», чтобы код продолжал работать, если в MyTable добавляются новые столбцы.
Я считаю, что удаление и воссоздание столбца изменит его положение, что сделает невозможным использование SELECT *.
Обновление:
Я пробовал использовать IDENTITY_INSERT, как было предложено в одном ответе. Это не работает - см. Изображение ниже. Что я делаю неправильно?
-- Create test table
CREATE TABLE [dbo].[TestTable](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
-- Insert some data
INSERT INTO TestTable
(Name)
SELECT 'One'
UNION ALL
SELECT 'Two'
UNION ALL
SELECT 'Three'
GO
-- Create empty temp table
SELECT *
INTO #Tmp
FROM TestTable
WHERE 1=0
SET IDENTITY_INSERT #Tmp ON -- I also tried OFF / ON
INSERT INTO #Tmp
SELECT TOP 1 * FROM TestTable
SET IDENTITY_INSERT #Tmp OFF
GO
-- Drop test table
DROP TABLE [dbo].[TestTable]
GO
Обратите внимание, что сообщение об ошибке «Явное значение для столбца идентификаторов в таблице '#TmpMyTable' можно указать только при использовании списка столбцов и IDENTITY_INSERT ON.» - Я специально не хочу использовать список столбцов, как описано выше.
Обновление 2 Пробовал предложение Майка, но это дало ту же ошибку:
-- Create empty temp table
SELECT *
INTO #Tmp
FROM (SELECT
m1.*
FROM TestTable m1
LEFT OUTER JOIN TestTable m2 ON m1.ID=m2.ID
WHERE 1=0
) dt
INSERT INTO #Tmp
SELECT TOP 1 * FROM TestTable
Что касается того, почему я хочу это сделать: MyTable - это промежуточная таблица, которая может содержать большое количество строк, которые необходимо объединить в другую таблицу. Я хочу обработать строки из промежуточной таблицы, вставить / обновить мою основную таблицу и удалить их из промежуточной таблицы в цикле, который обрабатывает N строк за транзакцию. Я понимаю, что есть и другие способы добиться этого.
Обновление 3
Мне не удалось получить Майка решение для работы, однако было предложено следующее решение, которое действительно работает: префикс с неидентификационным столбцом и удаление столбца идентичности:
SELECT CAST(1 AS NUMERIC(18,0)) AS ID2, *
INTO #Tmp
FROM TestTable
WHERE 1=0
ALTER TABLE #Tmp DROP COLUMN ID
INSERT INTO #Tmp
SELECT TOP 1 * FROM TestTable
Предложение Майка хранить только ключи во временной таблице также является хорошим, хотя в этом конкретном случае есть причины, по которым я предпочитаю иметь все столбцы во временной таблице.
