Solved

keeping the data structure of two tables in sync using SQL

Posted on 2003-12-05
6
444 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

12 Experts available now in Live!

Get 1:1 Help Now