?
Solved

Copying Table structure (using VB rdo and SQL)

Posted on 2003-03-06
9
Medium Priority
?
586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

801 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