Solved

keeping the data structure of two tables in sync using SQL

Posted on 2003-12-05
6
452 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
[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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
T-SQL: Wrong Result 7 39
Using a SUBQUERY for the set variable 10 29
Search Text in Views 2 28
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

734 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