Duplicates n times a rocord

jsirico · #1 (**permalink**) 02-03-12, 05:59

Hi Guys,
I'm in need of your help.
This is what I have to do:
I 've to clone all the fields stored in a table:
acc1:

Code:

accounting                        other_fields           length
soomecode1 somecode2           x                        2

where accounting stores information which will be splitted in two or more records;

healdem · #2 (**permalink**) 02-03-12, 06:07

do you mean clone (IE an exact copy of the existing data, if so why?)

you'd need to give mroe information than you have currently
such as what data are you moving from and to
somecode1 somecode2 x 2
is hardly helpful.
or do you mean you need to split accounting with a value of
'somecode1 somecode2' into
somecode1 x 2
somecode2 x 2

jsirico · #3 (**permalink**) 02-03-12, 08:45

Ok I'm agree with you;
Lets say that they have committed me to clone data;
The reason is that there is a field which stores information that would be more readable if each part of this fields will stand alone ;
I'll try to explain it using a table:

Code:

accounting                        other_fields           length
soomecode1 somecode2           x                        2

where the fields Lenght shows the number of "codes" stored in one record,
and Id like to have somethings like this:

Code:

accounting                        other_fields           length
soomecode1                           x                        1
somecode2                           x                        1

The fact is that I can't do an select * into the same table because there are some fields that have more than 2 code per record, so I'd like to find at least a way to clone my data in function of the fields lenght.. In others words I'd like to duplicates N times A recod!!!!!!
is it more clear??????

healdem · #4 (**permalink**) 02-03-12, 08:53

when you say current codes have more than one code in them does that mean you only have a maximum of two per block or could you have say 3, or even more
splitting 2 or 3 codes is doable via an SQL insert into , 4 or more would I suspect require manual intervention
it would help f you could give some sample data
what separates code1,code2...codeN
would you want to retain the original multi code AS well as the new codes

jsirico · #5 (**permalink**) 02-03-12, 08:59

Here is an example of my table when exactly starts the problems of the groth of the codes!!!!

Code:

Accounting                                                                                                                 lenght
d40220100 c40320100	                                                                                                   2
d40220100 c40320100                                                                                                     2
d40220000 c40320000	                                                                                                   2
d40220000 c40320000	                                                                                                   2
d40220100 c40320100	                                                                                                   2
d40220100 c40320100	                                                                                                   2
d40220200 c40320200	                                                                                                   2
d40220200 c40320200                       	                                                                           2
d40200000 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
d40200000 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
d40200100 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
d40200100 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
d40200200 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9

healdem · #6 (**permalink**) 02-03-12, 09:22

are these codes fixed length?
ie always 9 characters?
what you could do is write a series of queries that progressively chop out blocks of 9
eg:-

'extract first code

Code:

INSERT INTO Codes ( Code, NoCodes )
SELECT mid(Code,1,9) AS NewCode, 1 AS NewCodeLength
FROM codes
WHERE len(codes.[NoCodes])>9);

'then modify that for each remaining block

'extract code 2

Code:

INSERT INTO Codes ( Code, NoCodes )
SELECT mid(Code,11,9) AS NewCode, 1 AS NewCodeLength
FROM codes
WHERE len(codes.[NoCodes])>=19);

'extract code 3

Code:

INSERT INTO Codes ( Code, NoCodes )
SELECT mid(Code,21,9) AS NewCode, 1 AS NewCodeLength
FROM codes
WHERE len(codes.[NoCodes])>=29);

'and so on
you'd need to change the start point for the mid fucntion from 1,11,21 to alwasy be the start of the next code block eg 31, 41, 51, 61
you'd need to change the where statement to reflect the length of the record that may have a value in (as your codes are 9 characters long then its on 9 or greater boundariews ie 9,19,29,39 and so on

but that will work ONLY if your codes are 9 character with one space, if they are floating format then you are going to have to do some other method.

if you had some records with more than one space between them then you'd need to pre process the codes to standardise at 10 character intervals

if this is a regualr process then you coudl create a macro which ran a block of queries in succession

jsirico · #7 (**permalink**) 02-03-12, 09:46

ok I need to think about this, just the last question: this will automatically add the the new records to my table?
sorry to insist is not possible to create something like this:
If lenght=2
than insert * into mytable from mytable
if lenght = 4
than copy ' the same record 3 times!!!!
if lenght =11 than copy the same records 10 times!!!!

?????
and just after add to the fields the right values using the mid function!!!!!

healdem · #8 (**permalink**) 02-03-12, 11:05

sorry this sounds like changing the requirement on the fly
if you butcher those queries you will be able to split off specific codes and insert them into your table as single codes with a lenght of one. if you have a maximum of, say, 10 codes then you'd need 10 queries to chop each individual code and insert it into the table

if you need to say start with 10 codes you could modify the query to write say
9 codes, 8 codes, 7 codes and so on

as to whether they work or not why not try 'em on some test data at your end

Internet Services

Web Development

Digital Marketing

Consumer Tech