rajah02
asked on
Copying Table structure (using VB rdo and SQL)
I am trying to make a copy of a table during run-time using visual basic. I need to be able to make an exact replica of a table. Something like this command
select * from <table> into <table-copy>
Except that I don't want to use Select Into. I tried using the built in system stored procedures (sp_helpindex, sp_helpconstraint...) in SQL server but I am stuck with the sp_helpconstraint procedure. I was not able to retrieve the value in the final column(constraint_keys). I got an error 'Invalid cursor position'. Same thing happened with sp_helpindex. Can access all the columns except for the last one. This is not getting me anywhere.
Is there an easier way to copy a table whose structure I do not know or can you tell me how I can solve the problem with accessing the final columns of the procedure.
Thanks
select * from <table> into <table-copy>
Except that I don't want to use Select Into. I tried using the built in system stored procedures (sp_helpindex, sp_helpconstraint...) in SQL server but I am stuck with the sp_helpconstraint procedure. I was not able to retrieve the value in the final column(constraint_keys). I got an error 'Invalid cursor position'. Same thing happened with sp_helpindex. Can access all the columns except for the last one. This is not getting me anywhere.
Is there an easier way to copy a table whose structure I do not know or can you tell me how I can solve the problem with accessing the final columns of the procedure.
Thanks
rajah02;
Can you back up a bit and explain why you have this
requirement? This may lead to a better solution to whatever
the underlying problem is.
TroyK, MCSD
Can you back up a bit and explain why you have this
requirement? This may lead to a better solution to whatever
the underlying problem is.
TroyK, MCSD
ASKER
troyk,
I have to make periodic updates to tables which might have somewhere around 400,000 records. These updates take like a couple of hours during which time I don't want to lock any part of the table as people are regularly accessing them. So I make a COPY of the table that I am going to update, carry out the update process and rename the table.
I make sure that the tables are synchronized before I rename it and that is the only time I lock the original table.
I was using Select * Into during the test phase, but on the production servers I was told that this would not be a good idea (something to do with select into interrupting system back-ups processes)
I have to make periodic updates to tables which might have somewhere around 400,000 records. These updates take like a couple of hours during which time I don't want to lock any part of the table as people are regularly accessing them. So I make a COPY of the table that I am going to update, carry out the update process and rename the table.
I make sure that the tables are synchronized before I rename it and that is the only time I lock the original table.
I was using Select * Into during the test phase, but on the production servers I was told that this would not be a good idea (something to do with select into interrupting system back-ups processes)
rajah02;
> periodic updates to tables which might have somewhere around 400,000 records
Since the updates are "periodic", could you schedule them
as a job to be run during a low-usage time (such as at night)?
> These updates take like a couple of hours
Have you verified that you have appropriate indexes to support the update operations?
Is it possible to break the updates into smaller chunks so that there is less resource contention?
The reason I ask is that the "copy-update-rename" solution is a bit convoluted, and circumvents many of the inherent benefits of storing data in a SQL DBMS.
HTH,
TroyK, MCSD
> periodic updates to tables which might have somewhere around 400,000 records
Since the updates are "periodic", could you schedule them
as a job to be run during a low-usage time (such as at night)?
> These updates take like a couple of hours
Have you verified that you have appropriate indexes to support the update operations?
Is it possible to break the updates into smaller chunks so that there is less resource contention?
The reason I ask is that the "copy-update-rename" solution is a bit convoluted, and circumvents many of the inherent benefits of storing data in a SQL DBMS.
HTH,
TroyK, MCSD
ASKER
>Since the updates are "periodic", could you schedule them
yeah that is possible.
>Have you verified that you have appropriate indexes to support the update operations?
What do you mean by this?
>The reason I ask is that the "copy-update-rename" solution is a bit convoluted
The reason I need to make a copy and then update the copy is that, after the update process is complete and before I rename the table, I compare the two tables and make sure that the updated table is not corrupted. For example, if names of people are being updated, and there are 50,000 last name changes this is obviously wrong.
I check the number of changes for each field in the table and try to make an educated guess as to whether the updated table is trash or not.
in case the table is trash, all I have to do is drop the copy and not mess around with the original.
-Rajah
yeah that is possible.
>Have you verified that you have appropriate indexes to support the update operations?
What do you mean by this?
>The reason I ask is that the "copy-update-rename" solution is a bit convoluted
The reason I need to make a copy and then update the copy is that, after the update process is complete and before I rename the table, I compare the two tables and make sure that the updated table is not corrupted. For example, if names of people are being updated, and there are 50,000 last name changes this is obviously wrong.
I check the number of changes for each field in the table and try to make an educated guess as to whether the updated table is trash or not.
in case the table is trash, all I have to do is drop the copy and not mess around with the original.
-Rajah
>>Have you verified that you have appropriate indexes to support the update operations?
>What do you mean by this?
Assuming each UPDATE is based on some search argument (i.e., the "WHERE" part), is this search performed on an indexed column? If not, this could cause a table scan which would be very slow on a 400,000 row table.
Re: the "copy-update-rename" issue. What happens if, during the course of the the update (after you have made the copy), a user changes something in the "original" version of the table? What happens to their changes after you accept the "new" version?
Is there a way you can validate the proposed UPDATEs before applying them, rather than after the fact?
HTH,
TroyK, MCSD
>What do you mean by this?
Assuming each UPDATE is based on some search argument (i.e., the "WHERE" part), is this search performed on an indexed column? If not, this could cause a table scan which would be very slow on a 400,000 row table.
Re: the "copy-update-rename" issue. What happens if, during the course of the the update (after you have made the copy), a user changes something in the "original" version of the table? What happens to their changes after you accept the "new" version?
Is there a way you can validate the proposed UPDATEs before applying them, rather than after the fact?
HTH,
TroyK, MCSD
ASKER
thanks for replying
>is this search performed on an indexed column?
yes.
> What happens if, during the course of the the update (after you have made the copy), a user changes something in the "original" version of the table?What happens to their changes after you accept the "new" version
I synchronize the tables before I drop the original version. These are the steps I follow,
I have a table A
- make table A-Copy
- update table A-copy from an input file
- I check for major discrepancies between the two tables. If yes(there are major discrepancies), I drop A-copy, terminate update,send an email to the appropriate person reporting a bad input file,
- if the differences are tolerable, I rename A to A-old, and A-copy to A.
-Update the changes (that happened during update process) to the new 'A' from A-old. (I have a timestamp for each record)
- prepare a report of the changes and email it to the appropriate person.
>Is there a way you can validate the proposed UPDATEs before applying them, rather than after the fact?
I am not sure how that is possible, since I am not checking for just one or two changes but a whole bunch of field changes. The table contains a little more than 60 fields. And the update is from a file rather than a table.
-Rajah
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rajah02:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
select * into tblNew from tblOld where 1 = 0
will only copy the table structure without any data.
Good luck!