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 |
|
workindan
Starting Member
21 Posts |
 Posted - 2011-08-24 : 12:49:09
|
| Hello,I'm using access vba and an adodbrecordset to upload records from a local table into sql server.The problem is that even though the sql server table has a primary key, I can upload the same records again and again and they'll insert without issue into the sql server table.If the record gets written to the sql server table once, how could it be written to again if the primary key already exists?-----------------------do while adodata.eof = falsedocmd.RunSQL "INSERT INTO doc.tblDocs (DocKey, etc...) VALUES '" & adodata.fields(1) & adodata.fields(2)"', '" etc....SQL SERVER doc.tblDocs--------------------------(Primary Key) doc.tblDocs.DocKey - nvarchar(100)other fields etc....Thoughts? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-24 : 12:58:28
|
| What version of SQL Server?Sure that you're actually adding dupes on the PK?Can you show the table DDL? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-24 : 13:28:47
|
| either it is not PK or it may be part of composite pk.try thissp_pkeys 'tblDocs'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-24 : 13:37:21
|
| My thought was that perhaps your application is getting an error-response from SQL Server, but nothing is catching that error (so the first record inserted will succeed, but the subsequent ones will actually have failed) |
|
|
|
workindan
Starting Member
21 Posts |
Posted - 2011-08-24 : 14:06:02
|
| Well, I removed the second field which was an identifier being created with NewID() (which I ultimately didn't need).After removing that field, the records loaded and I received the proper error for trying to add duplicates. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-24 : 14:11:58
|
"NewID()"... will create a unique value every tie its called, so if that was part of your PK then every row inserted would have a unique key ... so there would have been no error.So I guess that explains it  |
|
|
|
gwilson67
Starting Member
42 Posts |
Posted - 2011-08-25 : 00:32:23
|
| Try inserting the records into a staging table. Investigate this table to see if there are duplicates. If no duplicates then do an INSERT INTO.Greghttp://www.freewebstore.org/tsqlcoderepositoryPowerful tool for SQL Server development |
|
|
|
|
|
|
Insert Into creating duplicate Primary Keys