Copy bank record

Question

I used the following query to try to 'copy' a record

insert into valores select * from valores where id = 1

However, I receive the following message:

An Explicit value for the Identity column in table 'values' can only be specified when a column list is used and IDENTITY_INSERT is ON

I believe the same is true because my ID field is identity

How can I create my query to perform this select and Insert with the collected values?

Edit: it is important not to need to explain the names of the columns because the idea is to have a generic solution for different tables.

Answer 1

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 for Sqlserver:

1 - Disable the 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 make a RESEED after this Insert.

2 - Omit the 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

Answer 2

To copy a record by generating a new id for it (column identity), solution 2 of the @Tobymosque response (clarify the columns leaving out the column id) is the right one:

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

To not need to write all columns, you will have to generate the command insert dynamically.

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

Generate SQL dynamically using SQL only

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

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

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

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 of this table, except the id, you can execute 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 comma-separated string, you can declare a variable and concatenate in it the value of each row, thus:

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 solve this, one option is to use coalesce instead of pre-initializing the variable value @columns:

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

Thus, in the first iteration, the variable is NULL and its comma concatetion will result in NULL, so that 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 the coalesce will no longer act.

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 variable value @columns and then run this query.

The complete command stays 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 these commands as a single query from your application.

If you now display all table records person, you will see that the record of id = 1 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 

_{Note: I used the command exec instead of the stored sp_executesql because I don’t know what can be parameterized in your specific case.}

See working on sqlfidle.

Answer 3

You are probably duplicating some primary key of your table or some column with Constraint UNIQUE.

You can specify the fields individually you want to copy (as per Toby’s reply) or create a new table using CREATE TABLE AS (works on SQL Server and Mysql):

CREATE TABLE novaTabela
AS (SELECT * from valores)