CODEWITHSUNDEEP
sqlsql-server
Jeanie Miflin
Jeanie Miflin

Reputation: 69

SQL: Copying entire tables without an identification column?

Basically my question is how would I copy a certain table without (as in the entirety of its contents) the identity column?

To elaborate, lets say I have a schema called completed_tasks and I would like to insert all of its contents in completed_tasks_archive: 

$stmt = $GLOBALS['conn']->prepare("INSERT INTO completed_tasks_archive SELECT * FROM completed_tasks; ");
$stmt->execute();

The code ought to work normally but due to the identity column, which I would refer to as ID I cannot perform this operation.

I get an error like this one: 

PHP Fatal error:  Uncaught PDOException: SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An explicit value for the identity column in table 'completed_tasks_archive' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Basically, I am wondering to whether or not is there a quicker way to do this transaction of data without specifying all the nitty-gritty details in the query.

I am using MS SQLSRV to store the data.

And I kind of begin to hate MS SQLSRV after working with MySQLi for a couple of weeks.

I apologise if my question is a bit convoluted but I am still learning on how to use all of this and, more importantly, learn how to use it elegantly.

Upvotes: 0

Views: 54

Answers (1)

user9171788
user9171788

Reputation:

Because you are using a column with autoincrement identity, you have to enable inserting for this column.

SET IDENTITY_INSERT completed_tasks_archive ON;

After performing your insert you should deactivate it.

SET IDENTITY_INSERT completed_tasks_archive OFF;

You also have to provide the column names. A "*" is not sufficient.

Have a look at IDENTITY INSERT

Upvotes: 2

Related Questions