Solved

keeping the data structure of two tables in sync using SQL

Posted on 2003-12-05
6
450 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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