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 |
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-11-06 : 11:31:14
|
if i have an existing table table2 and i want to copy the rows corresponding to : where mycol='myvalue' in table1 to the existing table table2, but table 2 has a column_id and scope identity. Runing just: insert into table2 select * from table1 mycol='myvalue' gives me the error:An explicit value for the identity column in table 'statventebck' can only be specified when a column list is used and IDENTITY_INSERT is ON.so what should i do to exclude the scope id columnor what should i do to make my command workThank you |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-11-06 : 11:33:45
|
what should i do to exclude the scope id column: List each column that you want to insert. It does NOT matter how many there are. If you have 200 Columns, you MUST list them.Insert Into Table2 (col1,col2,etc)Select col1,col2,etc (do NOT include your key column)From Table1 where mycol = 'myvalue' [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-06 : 11:36:17
|
Don't use SELECT * (which you should get out of the habit of doing anyway because it usually means you're selecting some columns that you don't need). Specify the columns in the SELECT and make sure you leave out the identity column.If you are specifying every column except the identity column, then you don't have to specify the columns in the INSERT as well, if you want to leave out other columns then specify the columns in both the INSERT and the SELECT, something likeINSERT table2 (col2, col5, col7)SELECT col2, col5, col7FROM table1WHERE mycol='myvalue' |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-11-06 : 12:15:07
|
Did i get the error because it s an id column or because it s a scope_idThank you so much. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-06 : 12:17:38
|
Because it is an identity column. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-06 : 12:35:37
|
... and because you have gotten into the bad habit of using "SELECT *" !!Kristen |
|
|
X002548
Not Just a Number
15586 Posts |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-08-19 : 06:49:47
|
and another blast from the past from another "wow look at my super cool excellent site that also does SQL" !!Seriously.. 2006?http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
|