How to handle INSERT INTO with varchar Primary Key(PK) column 1

[ouroboros12321]

Hello,
I've recently began learning SQL with SQL Server and it's been a blast so far. Right now, I am having a problem figuring out how to insert certain data into a destination table (not created by me, cannot alter it) that has a PK field which is a VARCHAR type and it doesn't appear to be an auto-id column.

Every time that I run the script below, I get the error:
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK_AR4CONTACTS'. Cannot insert duplicate key in object 'dbo.AR4CONTACTS'.

Here is my SQL Server script:
--------------------
use ASM_Analysis_Db
go

drop table #temp
go

drop table #u
go

create table #temp
(
Pat_Number varchar(12),
CTYPE VARCHAR(12),
CNOTES VARCHAR(650),
)
GO

bulk insert #temp
from '\\pcnamed\TS_Call_Output.txt'
with
(
fieldterminator = ',',
rowterminator = '\n'
--ERRORFILE = '\\pcnamed\\ERRORFILE.TXT'
)
go

/***? I created the additional # table below because I thought having an IDENTITY field would allow me to insert it into th e final destination table. Unfortunatly the script does not accept this.
***/?

create table #U
(idField int identity(1,1),
Pat_Number varchar(12),
CTYPE VARCHAR(12),
CNOTES VARCHAR(650)
)
GO

insert into #U
(Pat_Number , CTYPE , CNOTES )
select T.Pat_Number, T.CTYPE, T.CNOTES
from #temp as T

insert into [Ar-Express-Test].dbo.AR4CONTACTS
( REFER_CODE, CTYPE, CNOTES, CADDUSERID, DADDDATE)
select U.Pat_Number, U.CTYPE, U.CNOTES, 'ADMIN', GETDATE()
FROM #U as U INNER JOIN [Ar-Express-Test].DBO.AR4CONTACTS AS C
ON U.Pat_Number = C.REFER_CODE
;
--End of script
--------------------------

I have searched all over and cannot find an answer. How do I insert my data to this final table [Ar-Express-Test].dbo.AR4CONTACTS and handle the primary key issue???

I would appreciate any help from the knowledgeable users from this forum.

Thank you.

 

[hopper44]

Your text file '\\pcnamed\TS_Call_Output.txt' probably has duplicate values for the primary key, hence you are getting the error.

View the text file and remove any primary key duplicates.

 

[ouroboros12321]

Hopper44, thanks for the reply.
Maybe my post wasn't clear, but I don't pull any unique values from the text file; better yet, any information that I pull from the text file is not intended to go into a primary key field.
Ultimately, I want to add the data from the text file to a table which requires a PK value, and that is where I am stuck, how to add this unique value.

 

[simian336]

Is this going into a table created by a system that you can't reproduce the PK value?

If so it might be possible to create a value outside the range that the system is using.

Simi

 

[simian336]

Some data examples might also help

Simi

 

[ouroboros12321]

Simian, I'm not sure what you mean by reproduce the PK value. If you are talking about identifying the last PK used then starting from there, I'm not sure how to do that. Regardless, I visually looked to see what was the last PK number used, tried to write that value into the field, and still received the above error.
As for data samples, here are some.
-----------------
Sample data from my text file, which is one line:
025199, phoneCall, Called: 1234534346 Service Date: 01/11/11 2011-01-08 11:53:35 ANSWERED_HUMAN Times per day?: 3 Uses Insulin?: 1 Information Changed?: 3****
-----------------

Data properties of destination table (the PK column is CPK_AR4CONTACTS):
*COLUMN_NAME* *DATA_TYPE*
CPK_AR4CONTACTS varchar
REFER_CODE varchar
CTYPE varchar
CNOTES varchar
CADDUSERID varchar
DADDDATE datetime
-----------------

Hope this helps provide you more information.

 

[gmmastros]

It appears as though CPK_AR4CONTACTS is storing a number even though the data type is varchar Ex: 025199. I would strongly encourage you to modify the table if you can. It would be better to use an INT column with an identity. This would make things a lot easier for you.

I cringe any time I see someone store a number in a varchar column. The problem is... varchar's can store any text, not just numbers, so the potential exists for data corruption. What would happen if someone manually entered a string like this: '3O5' Looks ok, right, but that's not really a 0, it a capital letter O (like [!]O[/!]pen). If you can, I strongly encourage you to change the table structure.

But, I also recognize that this isn't always possible. If you do not have the authority to change the table, I would encourage you to add a constraint to this column that requires all of the data to be numeric. If this were my database, I would add a constraint like this:

Alter Table dbo.AR4CONTACTS Add Constraint MustBeNumber CHECK (IsNumeric('-' + CPK_AR4CONTACTS + '.0e0') = 1)

By adding this constraint, you are basically saying that CPK_AR4CONTACTS must be a positive integer. This constraint will cause inserts and updates to fail if CPK_AR4CONTACTS is NOT a positive integer. This is a powerful way to ensure that your data does not get corrupt. Remember this! "You are the protector of the data. Your number one responsibility is to protect the quality of the data in your database."


Lastly, this can still be done, but it does make things a bit more difficult. You appear to be on the right track by creating a temp table with an identity column. I would just add a little more code to get the greatest value from the table, convert to int, and add it to the identity column of the temp table just prior to inserting the rows in to the real table.

I made a few changes to your script. The changes are highlighted.

use ASM_Analysis_Db
go

drop table #temp
go

drop table #u
go

create table #temp
(
Pat_Number varchar(12),
CTYPE VARCHAR(12),
CNOTES VARCHAR(650),
)
GO

bulk insert #temp
from '\\pcnamed\TS_Call_Output.txt'
with
(
fieldterminator = ',',
rowterminator = '\n'
--ERRORFILE = '\\pcnamed\\ERRORFILE.TXT'
)

/***? I created the additional # table below because I thought having an IDENTITY field would allow me to insert it into th e final destination table. Unfortunatly the script does not accept this.
***/

create table #U
(idField int identity(1,1),
Pat_Number varchar(12),
CTYPE VARCHAR(12),
CNOTES VARCHAR(650)
)

insert into #U
(Pat_Number , CTYPE , CNOTES )
select  T.Pat_Number, T.CTYPE, T.CNOTES
from #temp as T

[!]Declare @MaxValue VarChar(20)

Select  @MaxValue = Max(CPK_AR4CONTACTS) From [Ar-Express-Test].dbo.AR4CONTACTS[/!]

insert into [Ar-Express-Test].dbo.AR4CONTACTS
([!]CPK_AR4CONTACTS[/!], REFER_CODE, CTYPE, CNOTES, CADDUSERID, DADDDATE)
select  [!]U.idField + Convert(Int, @MaxValue),[/!] U.Pat_Number, U.CTYPE, U.CNOTES, 'ADMIN', GETDATE()
FROM #U as U INNER JOIN [Ar-Express-Test].DBO.AR4CONTACTS AS C
ON U.Pat_Number = C.REFER_CODE
;
--End of script
--------------------------

This will absolutely fail if the 'MaxValue' in the column cannot be converted to an integer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ouroboros12321]

gmastros,
thanks for taking the time to respond. I completely understand what you mean about inserting a number into a varchar column. This was built a long time ago and I cannot modify it.
As for your proposed solution, I made the changes you recommended and still got the same error:
---------
Msg 2627, Level 14, State 1, Line 11
Violation of PRIMARY KEY constraint 'PK_AR4CONTACTS'. Cannot insert duplicate key in object 'dbo.AR4CONTACTS'.
The statement has been terminated.
---------
In trying to figure out what the problem is, I tried to break down the code to see what is happening with the @MaxValue being retrieved from the destination field. Here's my code:

--Begin Code
use ASM_Analysis_Db
go

create table #U
(timeage date,
test varchar(20))

declare @test int,
@MaxValue VarChar(20)

Select @MaxValue = Max(CPK_AR4CONTACTS), @test=1
From [Ar-Express-Test].dbo.AR4CONTACTS


insert into #U
(timeage, test)

select GETDATE(), @test + CONVERT(int, @maxvalue)
from #U
;
--End script

Unfortunately, I get zero results:
(0 row(s) affected)

I looked at the destination table AR4CONTACTS and verified that there IS data there.

In case there is some property of the destination table that i'm not seeing, I attached a screenshot of it.

I hope someone can offer some additional guidance.

 

[gmmastros]

Unfortunately, I get zero results:
(0 row(s) affected)

That doesn't surprise me.

[tt][blue]
insert [!]into #U [/!]
(timeage, test)

select GETDATE(), @test + CONVERT(int, @maxvalue)
[!]from #U[/!]
[/blue][/tt]

You are inserting in to the table by selecting from the same table. This is valid syntax, so no error, but since you created the temp table and haven't actually added anything to it, there's nothing in it.

What output do you get when you run this?

Select Max(CPK_AR4CONTACTS) 
From   [Ar-Express-Test].dbo.AR4CONTACTS

And also this...

Select Count(*)
From   [Ar-Express-Test].dbo.AR4CONTACTS
Where  IsNumeric(CPK_AR4CONTACTS) = 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ouroboros12321]

Ah, of course it's not going to pull anything from an empty table.

Result for the first script:
0000003116

Result for the second script:
0

 

[gmmastros]

One more....

Select Max(CPK_AR4CONTACTS) As MaxString,
       Max(Convert(int,  CPK_AR4CONTACTS)) As MaxInt
From   [Ar-Express-Test].dbo.AR4CONTACTS

It's good to know that there aren't any strings that are not numeric in the column. In other words, all the data in the column is numeric, which is good news.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ouroboros12321]

On the last script I get this result:
MaxString MaxInt
0000003116 3116