| Author |
Topic |
|
gemispence
Yak Posting Veteran
71 Posts |
 Posted - 2006-03-06 : 16:51:03
|
| We're performing an insert and the column that has identity is numbering the records without a problem, but it's assigning duplicate ID's on the records. Does Autoincrement or Autonumbering need to be turned on somewhere?spencer |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-06 : 17:05:14
|
| check to see if you have some code that is setting identity_insert on (check BOL - don't have SQL on home machine atm). Unless you have a unique index on the identity column, unqiueness is not enforced.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-06 : 17:05:17
|
| >>Does Autoincrement or Autonumbering need to be turned on somewhere?No.>>but it's assigning duplicate ID's on the recordsNot sure what you mean by this. Are you saying that the value that sql server is generating for the identity column already exists in the table? Can you post the DDL of the table and the code you're using to perform the insert?Be One with the OptimizerTG |
|
|
|
gemispence
Yak Posting Veteran
71 Posts |
Posted - 2006-03-06 : 17:53:12
|
| Here is the INSERT statement. I think I narrowed the problem down a bit. I'm trying to insert records but only want to insert records that don't already exist in the USERS table below. That's why I created a WHERE NOT EXIST clause. This isn't working. I believe that SQL is evaluating the statement and not considering the WHERE NOT EXIST portion b/c I have not used the correct syntax or order of the expression. help.INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]FROM MiamiHeraldWHERE not exists (select * from users Where users.Username = miami.AdvertiserEmail)where validAD=1 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-06 : 19:15:45
|
Did you copy and paste this code or did you retype it?This can't be the statement that is generating dupes because it won't even run. where AND validAD=1also " = miami.AdvertiserEmail"miami is not a table or table alias in your query? Be One with the OptimizerTG |
|
|
|
gemispence
Yak Posting Veteran
71 Posts |
Posted - 2006-03-07 : 09:15:55
|
| It was a typo when I copied it. Yes the syntax should have read AND, and the table should have also read Miamiherald.email. Please disregard any syntax related issues. My issue still remains at large :) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-07 : 10:10:03
|
| So, the USERS table has a identity column?Do you have a unique index on that table? If you added one, you'd be sure to catch a culprit - naturally that might not be a viable approach in your production environmnet - I'm thinking more like in your test environment.Please send DDL of the table with the issue - (as I said, I expect is is table USERS ?).*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
gemispence
Yak Posting Veteran
71 Posts |
Posted - 2006-03-07 : 10:34:50
|
| Yes there is identity on the column. I believe I know what the problem is. Since we're importing a content partners data into our system, this particular table (users) doesn't relate all that well to our content partners data (miamiherald table). Hence, we're joining on a field (email) that isn't necessarily unique, b/c the same user can enter multiple data on the site. Therefore, I need to figure out an alternative way to relate these two tables and keep a semblance of uniqueness. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-07 : 11:18:24
|
| Ahah - hence the other thread we are having a discussion in :-)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
gemispence
Yak Posting Veteran
71 Posts |
Posted - 2006-03-07 : 12:04:47
|
| yes :). I wish there was an option in which the author could close a thread. |
|
|
|
|
Identity Column Numbering