CODEWITHSUNDEEP
sql-server-2005unique
Ddev
Ddev

Reputation: 31

unique query to store values in different tables

i want to write a unique query to store same values in different tables ex: i am having aa and bb tables.with a attribute no, address, phoneno, city. in this both no and city are same and remaining are different. in the both tables some attributes are unique and some are different and i want to update the 2 tables using one query in sql server.

Upvotes: 2

Views: 314

Answers (1)

Justin Ohms
Justin Ohms

Reputation: 3523

You will have to get a little more advanced than just a simple query to accomplish this. My advice would be that you probably should normalize your design to put the number and city in a separate table and and then simply link to this table in the other two tables.

In general you should not duplicate information into more than one table. However there are exceptions to this.

However you didn't ask for advice so you might have reasons to do it the way you asked. If this is still the path you want to go down your options are dictated by what flavor of SQL you are using. On many SQL platforms you could accomplish this with a stored procedure. On some you could use a view, a trigger and a stored procedure. I'll give you a quick synopsis of each option.

- Stored procedure Write one or more stored procedures (perhaps one each for insert, update, delete) These procedures take your values as parameters. The procs then run the queries on each of the tables one after the other.

- Stored procedure + View + Trigger (You can do this on MS SQL server but I'm not sure of others.) Here you create a single view that merges your two tables. (joining on your common fields.) This gives you one view that is basically the two tables joined together. Now since this view is based on two tables it becomes a non-update-able view. However you can attach stored procedures to the view for insert, update and delete operations. Here you do the same thing as in the first method, you create several procs to actually update the data in the two tables under the view. These procs are a little different because these need to be designed to work as triggers. You then attach these procs to the insert, update or delete triggers of the view. When all is said and done you should be able to directly insert, update or delete data on the view and see the changes in the underlying tables.

If all that seems really complicated, it's because it is. If you have another option such as normalizing your data structure to solve the problem you should try that first. If you really have no choice and triggers are available, the 2nd options works really well.

Upvotes: 2

Related Questions