Update a flag based on portion of data avialble in other column - Database Discussion Boards

We regularly use Red-Gates data compare and it does supply the scripts to merge the data. It tends to have trouble with extensive data changes but for fairly small changes it does an excellent job. It allows you to save the scripts. Never underestimate the power of human stupidity RAH
Sign In·View Thread·Permalink

In Red-gates data compare, please let me know how to get the scripts to merge data?
Sign In·View Thread·Permalink

Hi,
Could some one help me with loading the TABLE2 like the way it is shown below.

SELECT COL1, COL2 FROM TABLE1

COL1 COL2
1 Tier 1 (QL) (ST) (PA)
2 Tier 2 (QL) (ST)
3 Tier 1 (ST)
4 Tier 1 (ST) (PA)
5 Tier 1
-----------------------------------------------------
INSERT INTO TABLE2 (COL1,COL2,QTY_FLG,STP_THPY_FLG,PRIOR_AUTHN_FLG)
SELECT COL1,COL2, , , FROM TABLE1
------------------------------------------------------

SELECT * FROM TABLE2

COL1 COL2 QL_FLG ST_FLG PA_FLG
1 Tier 1 (QL) (ST) (PA) 1 1 1
2 Tier 2 (QL) (ST) 1 1 0
3 Tier 1 (ST) 0 1 0
4 Tier 1 (ST) (PA) 0 1 1
5 Tier 1 0 0 0

Basically from Table1(COL1, COL2) i want to copy data to Table2(Col1,Col2)..while inserting, i want check if COL2 is having any specification ((QL) (ST) (PA)) accoringly flag should be updated with 1 to the respective _FLG column,if there is no specility (example : 5th row) all the _FLG columns should be updated with 0.

Please let me know if there are any related posts.

Thanks

Looking at your example it is not possible to identify the col1/2 content. Assuming the fist number is not in col1 I would break it into 2 queries The first query should group by the col1 and count the instances of the different specifications. The second query should build the string based on that information. Never underestimate the power of human stupidity RAH
Sign In·View Thread·Permalink

Hi, Thank you so much for your reply.

Could you please find the table information clearly with each row and data separated with pipe delimeter (|).

As you suggested, Group the column by Col1.. As the Col1 Information is unique.. i think it will give the same data even after grouping.

SELECT COL1, COL2 FROM TABLE1

COL1|COL2
1| Tier 1 (QL) (ST) (PA)
2| Tier 2 (QL) (ST)
3| Tier 1 (ST)
4| Tier 1 (ST) (PA)
5| Tier 1
-----------------------------------------------------
INSERT INTO TABLE2 (COL1,COL2,QL_FLG,ST_FLG,PA_FLG)
SELECT COL1,COL2, , , FROM TABLE1
------------------------------------------------------

SELECT * FROM TABLE2

COL1| COL2| QL_FLG| ST_FLG| PA_FLG
1| Tier 1 (QL) (ST) (PA)| 1 |1| 1
2| Tier 2 (QL) (ST)| 1 |1| 0
3| Tier 1 (ST)| 0 |1 |0
4| Tier 1 (ST) (PA)| 0| 1| 1
5| Tier 1 |0 |0 |0

Ok so this is not structured data it is rubbish, you are going to have to parse col2 of table1 into proper data. Then create a normalised table where you have 3 records for 1|Tier1.

After that it is a positional or possibly a pivot.

OR

You can create a cursor spit to process each record into the desired table2 format by parsing the col2 of table1.

Never underestimate the power of human stupidity
RAH

HI, Thank you for the reply.
Actaully the data is pretty much structered... since i cannot able to show the data here with proper horizantal and vertical bars,i kept Pipe delimeter so that data of each column will be separate from each other.

My source table is having rows in arround 500 - 600K using a cursor will be costly in performence point of view.

Could u please suggest if there is any other alternative.

HI, this is how it worked for me INSERT INTO TABLE2 (COL1, COL2, QL_FLG, ST_FLG, PA_FLG) (SELECT COL1, COL2, DECODE(INSTR(COL2,'QL'),0,0,1) QL_FLG, DECODE(INSTR(COL2,'ST'),0,0,1) ST_FLG, DECODE(INSTR(COL2,'PA'),0,0,1) PA_FLG FROM TABLE1); Thank you so much for your help.
Sign In·View Thread·Permalink

any one can give me example about comlpex data binding with access database and its tables (bind combo box to access database tables)
Sign In·View Thread·Permalink

This question begs a LMGTFY, have you done even the minimum of research before asking a question that has been answered 1000s of times. Read the guidelines, follow them! Never underestimate the power of human stupidity RAH
Sign In·View Thread·Permalink

		9,892,478 members (28,344 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using