To copy a record by generating a new id for it ( identity column), solution 2 of the @TobyMosque response (explicitly leaving columns out of the id column) is correct:

insert into pessoa (nome, endereco)
select nome, endereco from pessoa where id = 1

In order not to have to write all the columns, you will have to generate the command insert dynamically.

You have the option to do this in the application and also through SQL commands.

Generate SQL dynamically using just SQL

In Microsoft SQL Server, you can run a query contained in a string using the exec command or the sp_executesql stored procedure.

I recommend sp_executesql because you can pass parameters to reuse the execution plan by re-executing the query with different arguments - this provides a better performance that may be important or irrelevant depending on the scenario.

In addition, sp_executesql validates parameter types and automatically treats apostrophes in the middle of the string, making life easier and making SQL Injection difficult.

Well, consider the following table:

create table pessoa(
  id int identity,
  nome varchar(50),
  endereco varchar(50));

insert into pessoa values ('Eu', 'Rua das Rocas');
insert into pessoa values ('Tu', 'Rua das Cabras');

To get the list of columns in this table, except id , you can run the following command:

select name
from sys.columns
where object_id = OBJECT_ID('pessoa')
and name <> 'id'

This returns:

name
------------
nome
endereco

To get the names of these columns in a string, separated by a comma, you can declare a variable and concatenate the value of each row in it, like this:

DECLARE @colunas VARCHAR(8000) = ''
SELECT @colunas = @colunas + ', ' + name
FROM sys.columns
where object_id = OBJECT_ID('pessoa')
and name <> 'id'

select @colunas

This command returns:

------------
, nome, endereço

Oops, we have a problem: we end up with an extra comma in the column list. To work around this, one option is to use coalesce instead of pre-initializing the value of the columns variable:

DECLARE @colunas VARCHAR(8000)
SELECT @colunas = COALESCE(@colunas + ', ', '') + name
FROM sys.columns
where object_id = OBJECT_ID('pessoa')
and name <> 'id'

So, in the first iteration, the variable is NULL and its concatenation with commas will result in NULL , so the coalesce will act and will result in empty space; and from the second iteration the variable will contain the name of the first column and coalesce will not act anymore.

If you display the result of @columns after the above command, you will get the following result:

------------
nome, endereço

We're making progress!

Now we just need to dynamically generate a query by taking advantage of the @columns variable and then executing this query.

The complete command looks like this:

DECLARE @colunas VARCHAR(8000)

SELECT @colunas = COALESCE(@colunas + ', ', '') + name
FROM sys.columns
where object_id = OBJECT_ID('pessoa')
and name <> 'id'

exec ('insert into pessoa (' + @colunas + ') select ' + @colunas + ' from pessoa where id = 1')

You can run all of these commands as a single query from within your application.

If you now display all the records in the person table, you will see that the id = 1 record was duplicated with a new id 3 :

id  nome  endereco
----------------------
1   Eu    Rua das Rocas 
2   Tu    Rua das Cabras 
3   Eu    Rua das Rocas 

See working in sqlfidle .

Rafael, as you did not inform the purpose of your query, the table structure or the DBMS, I will give you two very generic alternatives to SqlServer:

1 - Disable Identity temporarily

SET IDENTITY_INSERT valores ON;

insert into valores 
select * from valores 
where id = 1;

SET IDENTITY_INSERT valores OFF;

Possibly you will need to do a RESEED after this insert.

2 - Omit id column in select and insert

insert into valores (coluna-1, coluna-2, ..., coluna-n)
select coluna-1, coluna-2, ..., coluna-n from valores 
where id = 1

You're probably duplicating some primary key in your table or some column with constraint UNIQUE .

You can specify the individual fields you want to copy (according to Toby's answer) or create a new table using CREATE TABLE AS (works in SQL Server and MySQL):

CREATE TABLE novaTabela
AS (SELECT * from valores)