Solved

keeping the data structure of two tables in sync using SQL

Posted on 2003-12-05
6
447 Views
Last Modified: 2008-01-16
I have two tables with similar data structures. One is the main table and the second is an audit table for the first which has all the same columns of the first plus two columns which keep track of who made a change and when. I'd like to write a SQL statement that helps me keep them in sync. The statement should:

1) Query the data structure of the first table.
2) Look for any changes to column names, new columns, deleted columns, or changed data types as compared to the second table.
3)change the structure of the second table to match the first, without affecting the first two columns of the second table.

first table columns:
---------------------
cktName char(7),
cktDate datetime,
cktLocation varchar(20),

second table columns:
-------------------------
whoChanged char(20),
whenChanged datetime,
cktName char(7),
cktDate datetime,
cktLocation varchar(20),


The solution can be a SQL statement in a DTS package or stored procedure.

How is <i>that</i> for a challenging question?! Thanks a ton in advance.. if anyone can solve it!
0
Comment
Question by:josephfluckiger
  • 3
  • 2
6 Comments
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 100 total points
ID: 9883426
@josephfluckiger:

> I have two tables with similar data structures. One is the main table and the
> second is an audit table for the first which has all the same columns of the
> first plus two columns which keep track of who made a change and when. I'd
> like to write a SQL statement that helps me keep them in sync. The statement should:

I don't think you can do it with a SQL statement, but there are 3rd party utilities available for that exact purpose.  My favorite one is this one:
     http://www.red-gate.co.uk/SQL_Compare.htm

Hope That Helps,
Dex*
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 total points
ID: 9884239
in your audit trigger (if you want to take the hit!!!)

you can query Information_schema.Columns

like

declare @altstr varchar(8000)

While Exists (select column_name
 from  (select column_name from information_schema.columns
   where table_name = 'BaseTable')) as a
 left outer Join (select column_name from information_schema.columns
   where table_name = 'audit table' and column_name not in ('audtcol1','auditcol2')) as b
   on a.column_name = b.column_name
 where b.column_name is null)
begin
select top 1 @altstr='Alter table AuditTableName ADD COLUMN ' + A.column_name +' '+
    +datatype + precision + .....
   from  (Select * from
information_schema.columns
   where table_name = 'BaseTable')) as a
 left outer Join (select column_name from information_schema.columns
   where table_name = 'audit table' and column_name not in ('audtcol1','auditcol2')) as b
   on a.column_name = b.column_name
 where b.column_name is null
 order by a.column_name
 EXEC(@ALTSTR)
End

... continue with trigger...

check bol for all the column name details for the information schema view...

0
 
LVL 1

Author Comment

by:josephfluckiger
ID: 9889556
thanks, both, I'll need some time to digest, but I can see both ideas are good.

additional ideas are of course always welcome as well..
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:josephfluckiger
ID: 9889841
I just thought of an idea, that probably seemed obvious to you, but I could just write my alter table statements in SQL, and run them against both databases, just changing the tablename for the SQL query.  Perhaps you can fill in the correct syntax using the table cktMain and the column cktName. I already filled in the first 3 of 5.

-add column
   ALTER TABLE cktMain ADD cktName varchar(10)
-delete column
   ALTER TABLE cktMain drop column cktName
-change datatype of column
   ALTER TABLE cktMain ALTER COLUMN cktName varchar(15)
-change name of column

-change an column of data type int to auto_increment


I also found a good post along these same lines:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20336135.html?query=alter+table&searchType=all
0
 
LVL 1

Author Comment

by:josephfluckiger
ID: 9889927
OK, I love answering my own questions here :) , but I found the way to rename a column:
sp_rename 'dbo.cktMain2.cktName', 'cktNameNew'

but I couldn't get this statement to work:

ALTER TABLE YOURTABLENAME RENAME COLUMN YOURCOLUMNNAME TO NEWCOLUMNNAME
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9891572
Yes there are various restrictions on what you can do with existing columns

and the alter table statement...

basically you can only ever be sure of being able to alter the length of varchar columns

any of the other allowed changes will only work if the column is not a key or index column...
 (or a text type)

for a lot of scenarios you have to drop the existing column and then re-add it with the characteristics you want...

but all of this code should be generated for you from your modelling tool....
even Enterprise manager allows you to capture the scripts it generates in design mode...

Have a look at alter Table in Book online.
 
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now