How to insert into a table from select statement when IDENTITY column is present

Question

0.00/5 (No votes)

See more:

Hi,

We have an SQL table 'attendmast'. At the end of the every month we need to append the data from this table into another table 'attendmastm'. Both the tables have same set of columns. We are using the following command to do that :

SQL

insert into attendmastm select * from attendmast

This returns an error

An explicit value for the identity column in table 'attendmastm' can only be specified when a column list is used and IDENTITY_INSERT is ON.

We understand, 'select *' is not a good coding practice... but in this case, We cannot specify the columns since programmatically we receive a table name and this operation has to be done on that table.

Kindly suggest any way out. Thank you in advance.

What I have tried:

Tried googling. Found several links talking about this. Yet not exactly what we want.

Posted 16-Sep-17 2:17am

Priya-Kiko

Updated 16-Sep-17 5:25am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 16-Sep-17 11:27am

attendmastm should not have IDENTITY.

3 solutions

Solution 1

Since the tables always have the same columns, just list the columns on both:

SQL

INSET INTO attendmatsm (col2, col3) SELECT col2, col3 FROM attendmast

Since the tables have the same column, they should all have the same name, regardless of the table name.
If they don't, the chances are the table definition will be wrong, and you will be inserting bad data into your master table.

But this does look like a poor design - I'm not sure exactly what you are trying to do with the data, but the chances are your storage structure is wrong.

Posted 16-Sep-17 2:24am

OriginalGriff

Comments

Priya-Kiko 18-Sep-17 1:25am

Thank you for the response.

The data in the source table(s) will be used in the day to day billing kind of transactions while the data in the target table(s) will not be accessed so frequently.

Hence we have adopted this logic to use the month files for reporting operations and clear the data in the day tables at the beginning of the day and use them in the transactions. This operation of transferring data from day table to month table happens day to day.

Further, we cannot specify the column names, since this operations happens for many tables and our function receives the table name as a parameter.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2017-09-16T04:00:00

Solution 3

If you want the key value to change when the data is copied from the source table to the target table then the Solution 1 would be the way to go.

However, since you describe that you want to append the data then I could imagine that the key value should be the same in the target table as in the source table. If this is the case then you could use SET IDENTITY_INSERT statement to allow you to define the value of the key column regardless if it's an identity. Have a look at SET IDENTITY_INSERT (Transact-SQL) | Microsoft Docs[^]

But if this is the case then you should consider two things:
- Why the target table contains an identity column if all the data is uniquely identified in the source table.
- Copying data to a table from another table on a monthly basis sounds like you're trying to solve some other problem, perhaps a performance related problem. If this is the case, then I believe that this may not be the best option. Moving the data around introduces often unnecessary complexity while the original problem, performance, could perhaps be solved with other solutions.

Posted 16-Sep-17 4:00am

Wendelius

Updated 18-Sep-17 8:25am

Comments

Priya-Kiko 18-Sep-17 1:44am

Thank you for the response.

This process is done on a daily basis, though I have specified 'Monthly' in my question. We have to transfer the day's transactions to a monthly backup file and clear off the day's tables. We use the month tables for reporting and the day tables for daily billing kind of transactions.

At the first, the month table may not be present. So we use :

if already present in sys.tables
insert into <monthtable> select * from <daytable>;
else
select * into <monthtable> from <daytable> ;

delete from <daytable>

This operation is done at the end of the day as part of a 'Day Close Process'

For Our operations, Solution 2 seems to be the only way out.

Wendelius 18-Sep-17 14:19pm

I take it that you're cleaning the daily tables because of performance reasons. If that's the case I kinda understand it.

However, what comes hard to understand is why each month needs it's own history table? As far as I can see this adds extra complexity because you cannot use fixed table names and so on.

You wrote that the monthly tables are used for reporting. What happens if you want to do reporting not on monthly basis but quarterly basis, weekly basis or even with dynamic date range. This may become very hard to handle.

What I would suggest is to analyze different options. If you really need to keep the daily data in a separate table that's fine, but you should at least consider having only single history table.

If the underlying problem is performance, remember that the indexing plan does not need to be the same for the history table as it is for the daily table. You can for example have completely different indexes on the daily table to support fast OLTP operations while the history table can have different kinds of indexes to support reporting.

Why I'm suggesting this is that if you would have only one history table I believe that the data load logic would be far simpler but more importantly you would have flexibility in reporting.

Mahesh Pratap Singh 18-Sep-17 7:41am

If you feel solution 2 as answer, please mark it.

Mahesh Pratap Singh · Accepted Answer · 2017-09-16T03:17:00

Since you are getting table name pragmatically; you can get columns name of that table too using below query:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where table_schema='dbo' and TABLE_NAME = 'attendmast'

Join all the columns in comma separated string and use as a select statement.

That can help you.