Cloning a set of records within a table strategy

[Argonak]

Hi, I've been trying to think of a way to do this (just conceptually). I have a rather large table of price quotes from suppliers with columns:

line_number (primary key) | quote_id_number | item_code | quantity

i'm using an msdn sql server and access 2002. the line_numbers are just an assigned number to give the row a unique identifier, a sample of the table might be

1	1	2	500
15	1	4	5
147	2	977	1
24	1	782        1	
278	2	1	2
2962	2	4	10

the numbers just refer to unique identifiers in other tables with longer information that gets repeated a lot in an attempt to reduce network transfer and storage space. what i want to do is copy the last two columns with a number 2 in the second column, and assign them a 3 for that column. I'm likely to be working with several hundred rows each time I run this, although it won't be a commonly run operation.

Should I use ADO and copy the records individually? Not too familiar with ado yet but i could work with it. Anyone have ideas as to what might be the most efficient strategy?

 

[modest]

Where are you copying them to?

Why use ADO/DAO when you can just do this with a query.

 

[Argonak]

well, i couldn't figure out a way to get the sql tables to auto number themselves, and so I've been numbering them myself upon insert with a random number assignment scheme. Some of my tables, this one in paticular don't have any other item that can be used as a primary key.

So I'm not sure how i'd go about doing an insert query while at the same time assigning them their id_numbers. I've only been doing this access and vba programming for a few weeks, so if there's a better way I'm all ears. I looked all over the net and my reference books but I wasn't able to find an autonumber attribute within the sql tables like access has.

 

[modest]

So I'm not sure how i'd go about doing an insert query while at the same time assigning them their id_numbers.

I'm not sure where you're inserting to is the problem. I am not understanding where the data is coming from or going to. I'm assuming the following table is something already constructed. Please fill in the holes where you see fit.

line(PK) | quote | item | quantity
1	     1       2	    500
15	     1       4	     5
147	     2      977	     1
24	     1      782      1	
278	     2       1       2
2962	     2       4 	     10

Line 147 has a quote that's a 2. So you want to copy 977 and 1. And you want to change the 2 to a 3. What happened to the 977 and 1 though. Just copying doesn't do anything, do you want to "paste" them anywhere?

Why not?
Copy: SELECT Item, Quantity From TableName Where Quote = 2
Change 2 to 3: UPDATE TableName SET Quote = 3 WHERE EXISTS (SELECT * FROM TableName WHERE Quote = 2)=True

 

[Argonak]

Well, I want to clone the records so that a previous quote can be updated without losing the old quote. I think what you're suggesting would work though, I'll try and build a query to do it and get back to you.

 

[Argonak]

i'm still not seeing a way of assigning the id_numbers, like i said earlier i had to assign unique id numbers to use as a primary key. i can't just copy the id number since its already in use by the previous version of the quote.

I need to copy all records for a given quote_id number, example: "2" and assign them a unique line_number before i reinsert them into the table somehow.

thanks for responding so far, I'm still hoping there's a way to do what I need to do.

 

[modest]

If you want the line_number to be a unique primary key that you specify the value of, you're going to have to take away the (autonumber). There are very few ways to get around renumbering the autonumber field (it is virtually impossible aside from the foremention workaround). On another note, the good thing about VB and being this programmer is you can assure data integrity and reduce the possibility of data redundancy phenomenons by using Visual Basic. This might mean you won't need a Primary Key at all, just make the index the field, don't allow duplicates, but don't make it a primary key.


I'm pretty much certain that you're using the AutoNumber, when the field should just be a number, but again I don't understand what you're doing or what you're trying to do, which may make my remarks irrelevant.

 

[Argonak]

I'm not using an autonumber. I'm developing this for the microsoft sql desktop server rather than a normal database because of its manner of handling client - server relationships.

The sql tables wouldn't let me insert data unless i had a primary key, so I added the line_number field as a primary key whenever there wasn't any other unique identifier available, because I was unable to find any sort of autonumbering system within the microsoft sql tables.

I solved this problem this afternoon though whats probably not the best solution, but it works.

I do a select on the information i want (the quantity, size, and id_code) and place it in a listbox. Then, using a for loop I insert each row into the main table again with its new quote_id and a new line_number assignment generated by me on insertion.

I was somewhat worried about the speed issue, but this won't be a common operation to the application so I think it'll be allright. The test quotes I use are maybe 1/20 of the size of an actual quote list and I was unable to notice the calculation time so it seems ok.

 

[modest]

Why is it that I still have no idea what's going on? To me it sounds like you are now generating an autonumber for your primary key, which Access does automatically.