keeping the data structure of two tables in sync using SQL

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!
LVL 1
josephfluckigerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DexstarCommented:
@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
LowfatspreadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
josephfluckigerAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

josephfluckigerAuthor Commented:
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
josephfluckigerAuthor Commented:
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
LowfatspreadCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.