Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
william_lee78
Yak Posting Veteran
53 Posts |
 Posted - 2004-09-30 : 06:54:20
|
| Hi, I faced the following error when the the sql statement. Error:An explicit value for the identity column in table 'SA_ACTIVITY_LOG_BAK' can only be specified when a column list is used and IDENTITY_INSERT is on.My SQL Statement:INSERT INTO SA_ACTIVITY_LOG_BAK SELECT * FROM SA_ACTIVITY_LOG WHERE DATEDIFF(DAY, SA_ACTIVITY_LOG.DateUpdate, GetDate()) >= 30Can someone help me on this problem?Thank you |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-30 : 06:57:29
|
| Use a field list instead of * |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-30 : 07:02:55
|
| Sure, William, I'm happy to help. The problem is an explicit value for the identity column in table 'SA_ACTIVITY_LOG_BAK' can only be specified when a column list is used and IDENTITY_INSERT is on.- Jeff |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-30 : 07:31:53
|
dr.:  this happens because you want to insert a value into aa identity column which gets populated automatically. like Rick said use a column list to specify columns you want to update.or if you must insert a value into an identity field, use SET IDENTITY_INSERT ON. but be sure to SET IDENTITY_INSERT OFF after you finish.Go with the flow & have fun! Else fight the flow  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-30 : 08:36:11
|
| (I was just giving him a hard time, because the error message SQL gave him pretty much describes perfectly what the problem is, and even offers the solution! All too often, people tend to see error messages, say "@%@#$@! something is wrong!" and not even read them in my experience )- Jeff |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-10-01 : 04:44:32
|
| Hi,Thank you so much for the information and helps. I got the issue solve.Thanks |
|
|
|
|
|
|
INSERT INTO table problem