Como copio uma tabela com SELECT INTO mas ignoro a propriedade IDENTITY?

De livros on-line

O formato de new_table é determinado avaliando as expressões na lista de seleção. As colunas em new_table são criadas na ordem especificada pela lista de seleção. Cada coluna em new_table tem o mesmo nome, tipo de dados, nulidade e valor da expressão correspondente na lista de seleção. A propriedade IDENTITY de uma coluna é transferida, exceto sob as condições definidas em "Trabalhando com colunas de identidade" na seção Comentários.

Abaixo da página:

Quando uma coluna de identidade existente é selecionada em uma nova tabela, a nova coluna herda a propriedade IDENTITY, a menos que uma das seguintes condições seja verdadeira:

• A instrução SELECT contém uma junção, cláusula GROUP BY ou função agregada.
• Várias instruções SELECT são unidas usando UNION.
• A coluna de identidade é listada mais de uma vez na lista de seleção.
• A coluna de identidade faz parte de uma expressão.
• A coluna de identidade é de uma fonte de dados remota.

Se qualquer uma dessas condições for verdadeira, a coluna será criada NOT NULL em vez de herdar a propriedade IDENTITY. Se uma coluna de identidade for necessária na nova tabela, mas tal coluna não estiver disponível, ou você desejar um valor de semente ou incremento diferente da coluna de identidade de origem, defina a coluna na lista de seleção usando a função IDENTIDADE. Consulte "Criando uma coluna de identidade usando a função IDENTIDADE" na seção Exemplos abaixo.

Então... teoricamente você poderia se safar com:

select id, val 
into copy_from_with_id_2 
from with_id

union all

select 0, 'test_row' 
where 1 = 0;

Seria importante comentar este código para explicá-lo, para que ele não seja removido na próxima vez que alguém olhar para ele.

Inspirado na resposta de Erics, encontrei a seguinte solução que depende apenas dos nomes das tabelas e não usa nenhum nome de coluna específico:

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;

Editar

É até possível melhorar isso para

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

Você pode usar uma junção para criar e preencher a nova tabela de uma só vez:

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

Devido à 1 = 0condição, o lado direito não terá correspondências e, portanto, evitará a duplicação das linhas do lado esquerdo e, como essa é uma junção externa, as linhas do lado esquerdo também não serão eliminadas. Por fim, por se tratar de uma junção, a propriedade IDENTITY é eliminada.

Selecionar apenas as colunas do lado esquerdo, portanto, produzirá uma cópia exata de dbo.TableWithIdentity somente em termos de dados, ou seja, com a propriedade IDENTITY removida.

Tudo isso dito, Max Vernon levantou um ponto válido em um comentário que vale a pena ter em mente. Se você observar o plano de execução da consulta acima:

você notará que a tabela de origem é mencionada no plano de execução apenas uma vez. A outra instância foi eliminada pelo otimizador.

Portanto, se o otimizador puder estabelecer corretamente que o lado direito da junção não é necessário no plano, deve ser razoável esperar que em uma versão futura do SQL Server ele possa descobrir que a propriedade IDENTITY não precisa ser removido também, pois não há mais outra coluna IDENTITY na linha de origem definida de acordo com o plano de consulta. Isso significa que a consulta acima pode parar de funcionar conforme o esperado em algum momento.

Mas, como corretamente observado por ypercubeᵀᴹ , até agora o manual tem declarado explicitamente que, se houver uma junção, a propriedade IDENTITY não será preservada:

Quando uma coluna de identidade existente é selecionada em uma nova tabela, a nova coluna herda a propriedade IDENTITY, a menos que [...] [a] instrução SELECT contenha uma junção.

Portanto, enquanto o manual continuar mencionando isso, provavelmente podemos ter certeza de que o comportamento permanecerá o mesmo.

_{Parabéns a Shaneis e ypercubeᵀᴹ por trazer um tópico relacionado no chat.}

Tente este código..

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

A ISNULLchamada garante que a nova coluna seja criada com NOT NULLnulidade.

Só para mostrar de uma forma diferente:

Você pode usar um servidor vinculado .

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

Você pode criar temporariamente um servidor vinculado ao servidor local usando isto:

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;

Nesse ponto, você executaria o select * intocódigo, referenciando o localservernome de quatro partes do servidor vinculado:

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

Depois que isso for concluído, limpe o localserverservidor vinculado com isto:

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

Ou você pode usar a OPENQUERYsintaxe

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

A propriedade identity não é transferida se a instrução select contiver uma junção e, portanto,

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

também dará o comportamento desejado (da idcoluna copiada para não manter a IDENTITYpropriedade. No entanto, terá o efeito colateral de não copiar nenhuma linha! (como em alguns outros métodos), então você precisará fazer:

insert into without_id select * from with_id;

(obrigado AakashM!)

A maneira mais fácil é tornar a coluna parte de uma expressão.

Exemplo:
Se a tabela dbo.Employee tiver uma identidade na coluna ID, no exemplo abaixo, a tabela temporária #t também terá uma IDENTIDADE na coluna ID.

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

Altere isso para aplicar uma expressão ao ID e você #t não terá mais uma coluna IDENTITY on ID. Nesse caso, aplicamos uma adição simples à coluna ID.

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

Outros exemplos de expressões para outros tipos de dados podem incluir: convert (), concatenação de strings ou Isnull ()

Às vezes, você deseja inserir de uma tabela onde você não sabe (ou se importa) se a coluna foi criada usando IDENTITY ou não. Pode até não ser uma coluna inteira com a qual você está trabalhando. Nesse caso, funcionará o seguinte:

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

ISNULL removerá o atributo IDENTITY da coluna, mas o inserirá com o mesmo nome e tipo da coluna original e também o tornará não anulável. TOP(0) criará uma tabela vazia que você pode usar para inserir linhas selecionadas. Você também pode compactar a tabela antes de inserir dados, se necessário.

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

removerá a identidade.

A desvantagem é que idse torna anulável, mas você pode adicionar essa restrição.

Tente a seguinte consulta: Encontrei esta funcionando corretamente. Isso selecionará dados em colunas sem identidade = 1

DECLARE @Temp NVARCHAR(MAX); 
DECLARE @SQL NVARCHAR(MAX);

SET @Temp = '';
SELECT @Temp = @Temp + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Person' AND  columnProperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') != 1 

SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [Person]';
EXECUTE SP_EXECUTESQL @SQL;

Você pode obter os detalhes do nome da coluna na tabela sys.columns

Tente a seguinte consulta:

SELECT * FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'

DECLARE @sql as VARCHAR(8000)
SET @sql = 'SELECT '

SELECT @sql += [Name] + ', ' FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'

SELECT @sql += ' FROM Dbo.TableName'

EXEC(@sql)