Modifying data in a table

[dpu]

Hi I have a source database with a table called PatientDemographics. Within that table there is a column called PatientID. I need to import this data into another database but I need to append the letters CA into the source database patienID column. So the patient id would become CA12345. How can I do this?

 

[bouwob]

select 'CA' + PatientID from PatientDemographics

 

[dpu]

That is just a select statement. I need to actually add CA to the patiendID. Currently the patient id is for example 12345. I need to add CA to it so the ID number field becomes CA12345.

 

[danvlas]

Why?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ca8msm]

You will need to use an insert statement coupled with the select statement above. Please read up on the INSERT command in Books Online as you need to understand the basics of sql.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[TysonLPrice]

You can do it after you load if you want. I'm assuming the column is VarChar.

create table ##TestIt(patientID varchar(8))

insert into ##TestIt(PatientID) Values(12341)
insert into ##TestIt(PatientID) Values(12342)
insert into ##TestIt(PatientID) Values(12343)
insert into ##TestIt(PatientID) Values(12344)
insert into ##TestIt(PatientID) Values(12345)

update ##TestIT set patientid = 'CA' + patientid

select * from ##Testit

Drop table ##TestIT

 

[danvlas]

Assuming your PatientID is numeric, primary key, you would need to change the data type of it and all foreign keys from all related tables.

You would also need to enforce cascade updates on the relationships.

And you gain practically nothing.

If all you need to do is 'display' CA before the ID, just use 'CA' + cast(PatientID as varchar) in your view/stored proc.

If 'CA' is the state, you could get it from the addresses/states tables and this way you would automatically have 'CA', 'NY', whatever.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html