Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Copying Table structure (using VB rdo and SQL)

Posted on 2003-03-06
9
Medium Priority
?
596 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:rajah02
9 Comments
 
LVL 4

Expert Comment

by:xxg4813
ID: 8081905
Hi,

select * into tblNew from tblOld where 1 = 0

will only copy the table structure without any data.

Good luck!
0
 
LVL 7

Expert Comment

by:TroyK
ID: 8082404
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
0
 

Author Comment

by:rajah02
ID: 8083152
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)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:TroyK
ID: 8083493
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
0
 

Author Comment

by:rajah02
ID: 8084084
>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
0
 
LVL 7

Expert Comment

by:TroyK
ID: 8084295
>>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
0
 

Author Comment

by:rajah02
ID: 8088857

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
0
 
LVL 7

Accepted Solution

by:
TroyK earned 300 total points
ID: 8089612
rajah02;

Getting back to your original question of how to construct a faithful copy of the table, including indexes, etc:

I ran a profiler trace and then chose 'Generate SQL Script' against a test table 'Employees' in my DB. The trace shows the following (abbreviated) list of actions:

exec sp_MStablerefs N'[dbo].[Employees]', N'actualkeys', N'foreign'
exec sp_MSdependencies N'[dbo].[Employees]', null, 262421
exec sp_MShelpcolumns N'[dbo].[Employees]', 512, @orderby = 'id'
SELECT s.groupname FROM dbo.sysfilegroups s, dbo.sysindexes i WHERE i.groupid = s.groupid AND i.id = object_id(N'[dbo].[Employees]') AND i.indid in (0, 1)
SELECT s.groupname FROM dbo.sysfilegroups s, dbo.sysindexes i WHERE i.id = (select x.id from dbo.sysindexes x where x.indid in (0,1) AND x.id = object_id(N'[dbo].[Employees]'))     AND i.groupid = s.groupid AND i.indid = 255
exec sp_MStablekeys N'[dbo].[Employees]', null, 6
exec sp_MShelpindex N'[dbo].[Employees]'
exec sp_MShelpcolumns N'[dbo].[Employees]', @orderby = 'id'
exec sp_MStablekeys N'[dbo].[Employees]', null, 6
exec sp_MStablechecks N'[dbo].[Employees]'
exec sp_MShelpindex N'[dbo].[Employees]'
select c.name, null, r.name, user_name(r.uid), d.name, user_name(d.uid), null from dbo.sysobjects d right outer join ( dbo.sysobjects r right outer join dbo.syscolumns c on c.domain = r.id ) on c.cdefault = d.id and d.category & 0x0800 = 0 where c.id = object_id(N'[dbo].[Employees]') and COLUMNPROPERTY(c.id, c.name, N'isIdentity') <> 1 order by c.name
exec sp_MStablekeys N'[dbo].[Employees]', null, 8

Most of the stored procs in use above are undocumented, and therefore may change in an update (service pack, Yukon) of SQL Server. It is still left as an exercise to utilize the info provided by those procs to generate a CREATE TABLE script.

Perhaps an easier solution would be to maintain DDL for each of the tables you need to do this action on, and apply that within your process to create the copy of the table.

I'm afraid that beyond these suggestions, to gain a proper understanding of your data loading procedure and to propose a viable solution may be beyond the capabilities of this medium. I invite others, however, to offer their perspectives.

HTH,
TroyK, MCSD
0
 

Expert Comment

by:CleanupPing
ID: 9276095
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.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question