Insert into eroor - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert into eroor

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-10-23 : 12:01:56

Hallo,

I have two tables, dbo.o_pat and dbo_o.pat1. The second table have data which i would like to transfer to dbo_o.pat. Also note, dbo.o_pat table datatype have been set, while in dbo_o.pat1 are all set to nvarchar(50). Is it possible to transfer the data from table to another regardless of the change of data type. Please see below the syntax I used;

INSERT INTO dbo.o_pat (o_pat_uid, o_prac_uid, o_col_uid, o_oper_uid, o_pat_id, o_pat_eid, o_pat_birth_yr, o_pat_birth_mth, 
o_pat_birth_pla, o_pat_fst_ac_dt, o_pat_fst_reg_dt, o_pat_vm_id, o_pat_curr_gender, o_pat_curr_marstat, 
o_pat_curr_smoke, o_pat_curr_drink, o_pat_curr_height, o_pat_curr_weight, o_pat_curr_bmi, o_pat_curr_reg_sta, 
o_pat_curr_trodt, o_pat_curr_tror, o_pat_curr_chsregf, o_pat_family_no, o_pat_bld_grp_uid, o_pat_death_date_i, o_pat_gprd_start_d,
o_pat_gprd_end, o_pat_mother_uid_i, o_pat_father_uid_i, o_pat_reg_f, o_pat_death_dt_i_f, o_pat_death_date, o_pat_mothr_calc_f,
o_father_calc_f, o_pat_ev_rec_f, o_pat_reg_det_f, o_pat_val_sex_f, o_pat_patage_f, o_pat_acc_f, o_pat_sys_d, o_pat_crt_d, 
o_pat_his_d, o_pat_del_d, o_pat_dwload)  
Select o_pat_uid, o_prac_uid, o_col_uid, o_oper_uid, o_pat_id, o_pat_eid, o_pat_birth_yr, o_pat_birth_mth, 
o_pat_birth_pla, o_pat_fst_ac_dt, o_pat_fst_reg_dt, o_pat_vm_id, o_pat_curr_gender, o_pat_curr_marstat, 
o_pat_curr_smoke, o_pat_curr_drink, o_pat_curr_height, o_pat_curr_weight, o_pat_curr_bmi, o_pat_curr_reg_sta, 
o_pat_curr_trodt, o_pat_curr_tror, o_pat_curr_chsregf, o_pat_family_no, o_pat_bld_grp_uid, o_pat_death_date_i, o_pat_gprd_start_d,
o_pat_gprd_end, o_pat_mother_uid_i, o_pat_father_uid_i, o_pat_reg_f, o_pat_death_dt_i_f, o_pat_death_date, o_pat_mothr_calc_f,
o_father_calc_f, o_pat_ev_rec_f, o_pat_reg_det_f, o_pat_val_sex_f, o_pat_patage_f, o_pat_acc_f, o_pat_sys_d, o_pat_crt_d, 
o_pat_his_d, o_pat_del_d, o_pat_dwload  
from dbo.o_pat1

Also tried;

INSERT INTO dbo.o_pat Select * from dbo.o_pat1

I received the following error;

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

Is there anyway I can overcome this and transfer the data to the table. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:30:25

it seems like you've non numeric data present in column you're trying to convert. whats the column you're trying to convert?

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-10-23 : 12:34:27

The datatypes should be set as as shown in table dbo.o_pat. The data stored are saved in another sql server with the shown format in dbo.o_pat. I am just having the same structure and data but is not working... I can change some fields if the need is required as this data will be static... Thanks

dbo.o_pat table

o_pat_uid decimal(20, 0) Unchecked
o_prac_uid int Checked
o_col_uid int Checked
o_oper_uid decimal(20, 0) Checked
o_pat_id nvarchar(4) Checked
o_pat_eid decimal(20, 0) Checked
o_pat_birth_yr decimal(4, 0) Checked
o_pat_birth_mth decimal(2, 0) Checked
o_pat_birth_pla nvarchar(22) Checked
o_pat_fst_ac_dt datetime Checked
o_pat_fst_reg_dt datetime Checked
o_pat_vm_id nvarchar(12) Checked
o_pat_curr_gender nvarchar(13) Checked
o_pat_curr_marstat nvarchar(13) Checked
o_pat_curr_smoke nvarchar(13) Checked
o_pat_curr_drink nvarchar(13) Checked
o_pat_curr_height nvarchar(12) Checked
o_pat_curr_weight nvarchar(12) Checked
o_pat_curr_bmi nvarchar(12) Checked
o_pat_curr_reg_sta int Checked
o_pat_curr_trodt datetime Checked
o_pat_curr_tror int Checked
o_pat_curr_chsregf nvarchar(1) Checked
o_pat_family_no decimal(6, 0) Checked
o_pat_bld_grp_uid int Checked
o_pat_death_date_i datetime Checked
o_pat_gprd_start_d datetime Checked
o_pat_gprd_end datetime Checked
o_pat_mother_uid_i decimal(20, 0) Checked
o_pat_father_uid_i decimal(20, 0) Checked
o_pat_reg_f nvarchar(1) Checked
o_pat_death_dt_i_f nvarchar(6) Checked
o_pat_death_date datetime Checked
o_pat_mothr_calc_f nvarchar(6) Checked
o_father_calc_f nvarchar(6) Checked
o_pat_ev_rec_f nvarchar(1) Checked
o_pat_reg_det_f nvarchar(1) Checked
o_pat_val_sex_f nvarchar(1) Checked
o_pat_patage_f nvarchar(1) Checked
o_pat_acc_f nvarchar(1) Checked
o_pat_sys_d datetime Checked
o_pat_crt_d datetime Checked
o_pat_his_d datetime Checked
o_pat_del_d datetime Checked
o_pat_dwload nvarchar(6) Checked

dbo.o_pat1 table

o_pat_uid nvarchar(50) Unchecked
o_prac_uid nvarchar(50) Checked
o_col_uid nvarchar(50) Checked
o_oper_uid nvarchar(50) Checked
o_pat_id nvarchar(50) Checked
o_pat_eid nvarchar(50) Checked
o_pat_birth_yr nvarchar(50) Checked
o_pat_birth_mth nvarchar(50) Checked
o_pat_birth_pla nvarchar(50) Checked
o_pat_fst_ac_dt nvarchar(50) Checked
o_pat_fst_reg_dt nvarchar(50) Checked
o_pat_vm_id nvarchar(50) Checked
o_pat_curr_gender nvarchar(50) Checked
o_pat_curr_marstat nvarchar(50) Checked
o_pat_curr_smoke nvarchar(50) Checked
o_pat_curr_drink nvarchar(50) Checked
o_pat_curr_height nvarchar(50) Checked
o_pat_curr_weight nvarchar(50) Checked
o_pat_curr_bmi nvarchar(50) Checked
o_pat_curr_reg_sta nvarchar(50) Checked
o_pat_curr_trodt nvarchar(50) Checked
o_pat_curr_tror nvarchar(50) Checked
o_pat_curr_chsregf nvarchar(50) Checked
o_pat_family_no nvarchar(50) Checked
o_pat_bld_grp_uid nvarchar(50) Checked
o_pat_death_date_i nvarchar(50) Checked
o_pat_gprd_start_d nvarchar(50) Checked
o_pat_gprd_end nvarchar(50) Checked
o_pat_mother_uid_i nvarchar(50) Checked
o_pat_father_uid_i nvarchar(50) Checked
o_pat_reg_f nvarchar(50) Checked
o_pat_death_dt_i_f nvarchar(50) Checked
o_pat_death_date nvarchar(50) Checked
o_pat_mothr_calc_f nvarchar(50) Checked
o_father_calc_f nvarchar(50) Checked
o_pat_ev_rec_f nvarchar(50) Checked
o_pat_reg_det_f nvarchar(50) Checked
o_pat_val_sex_f nvarchar(50) Checked
o_pat_patage_f nvarchar(50) Checked
o_pat_acc_f nvarchar(50) Checked
o_pat_sys_d nvarchar(50) Checked
o_pat_crt_d nvarchar(50) Checked
o_pat_his_d nvarchar(50) Checked
o_pat_del_d nvarchar(50) Checked
o_pat_dwload nvarchar(100) Checked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:39:24

i was refering to data itself rather than datatype. make sure you've only numeric data in nvarchar fields before converting to numeric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources