0

I have two database one of them in Latin collate and have Arabic data. In some case I take data that stored in the first database and store it in the second database table which has Arabic collate but each column in both database have varchar datatype. When I store data in the second database, It stored text with question mark.

If I change the type to nvarchar every thing goes well but old data still has question mark how can I retrieve the old data

I try to get old data but I get unreadable text with question mark.

CREATE TABLE dbo.cust_supp (
    company_id t_id_char2   NOT NULL,
    acc_name_a t_id_var70       NULL,
    acc_name_e t_id_var70       NULL,
    closed_by  numeric(4,0)     NULL COLLATE SQL_Latin1_General_CP850_CI_AS
);

CREATE TABLE dbo.dcl_item_update (

    item_update_id   numeric(18,0) NOT NULL IDENTITY,
    item_group_id    numeric(18,0) NOT NULL,
    change_column_id numeric(9,0)  NOT NULL,
    dcl_record_key   numeric(9,0)  NOT NULL,
    old_value        varchar(510)      NULL COLLATE Arabic_CI_AS,
    new_value        varchar(510)      NULL COLLATE Arabic_CI_AS
);

I store the acc_name_a value in to the columns old_value and new_value.

Blag
  • 5,818
  • 2
  • 22
  • 45
haneen
  • 3
  • 4
  • _"but each column in both database have `varchar` datatype"_ - **oops** – Dai Nov 17 '22 at 09:30
  • 1
    Please post your **full** `CREATE TABLE` statements and the **exact** `COLLATE` names you're using. – Dai Nov 17 '22 at 09:31
  • _"I change the type to `nvarchar` every thing goes well but old data still has question mark how can I retrieve the old data"_ - restore a backup from before the column type change. – Dai Nov 17 '22 at 09:32
  • 1
    Which database system do you use? How **exactly** do you store the data= – Nico Haase Nov 17 '22 at 09:46
  • I edited the question with create statement @Dai – haneen Nov 17 '22 at 09:47
  • why are you using `numeric` instead of `int` for what are clearly columns for integer values? – Dai Nov 17 '22 at 09:48
  • I cannot use a back up because it is a log table so the data may be changed @Dai – haneen Nov 17 '22 at 09:48
  • \*facepalm\* - I think you're SOL - or tell your boss you'll have to take the database offline to restore the deleted data. Also, consider using Temporal Tables instead of application-managed auditing tables. – Dai Nov 17 '22 at 09:49
  • I used sql server and store data using stored procedure I take the value from the first table and store it in other table @NicoHaase – haneen Nov 17 '22 at 09:49
  • I use numeric because it is integrated with old system that use it as a numeric and I can not edit it @Dai – haneen Nov 17 '22 at 09:51
  • Please add **all** clarification to your question by editing it – Nico Haase Nov 17 '22 at 10:05

1 Answers1

0

Did this work ? (I don't have an SQL Server right now to try, and I'm not sure if it's really what you want either...)

INSERT INTO dbo.dcl_item_update (old_value)
SELECT closed_by COLLATE Arabic_CI_AS as closed_by 
FROM dbo.cust_supp
Blag
  • 5,818
  • 2
  • 22
  • 45
  • I think there is a misunderstanding of my question I insert to the table previously but because of the difference of collation I can not read the column old value it appears like 'E??? ??????¤ ??????? ????µ???' So I ask if there is anyway to read it – haneen Nov 17 '22 at 12:13
  • @haneen `SELECT old_value COLLATE SQL_Latin1_General_CP850_CI_AS as old_value FROM dbo.dcl_item_update ` this maybe ? – Blag Nov 17 '22 at 12:42