Automatically insert into a 2nd table when a new data is added into another table

i have a table Companiesdata

CREATE TABLE [dbo].[Companiesdata] ( [Company Name] nvarchar(255), [Industry] varchar(40), [ParentId] int NULL, )

the records are

     CompanyName                        Industry                         Parent ID                                                             
      XYZ technologies                      Media                          1                                      
      apple Technologies                    pharma                         1
       EGG BANK                             Software                       2
       abc Technologies                     Networking                     4
       PQR Technnologies                    Marketing                      5

i have other table

create table dbo.companycategories ( categoryID int identity(1,1), Industry varchar(40) )

Now i want a procedure to automatically insert data into companycategories table if i inserted in companiesdata table.

i.e my output should be

If i insert into companiesdata values ('ijk technologies','finance',4)

then the industry field should automatically get inserted into companycategories table

select * from companycategories

CategoryID Industry
1 Finance

more ▼

asked Aug 03, 2012 at 11:16 AM in Default

avatar image

150 10 10 14

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first



more ▼

answered Aug 03, 2012 at 11:27 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

hey pavel thankyou .. will check it out ..

Aug 06, 2012 at 05:08 AM tsaliki
(comments are locked)
10|1200 characters needed characters left

Instead of the trigger solution I would recommend you create a stored procedure that inserts CompaniesData and CompanyCategories at the same time. All inserts to CompaniesData should be through this stored procedure.

more ▼

answered Aug 06, 2012 at 05:31 AM

avatar image

Scot Hauder
6.5k 13 16 22

scot i didnt get u.can u please write a store procedure for the above one ?

Aug 06, 2012 at 05:33 AM tsaliki

Although we do the same for some databases, but Triggers tends to be the safest approach to handle cases like manual INSERT etc.

Aug 06, 2012 at 09:59 AM Usman Butt

Yes creating triggers tends to be fastest than stored procedures

Aug 06, 2012 at 10:43 AM sravan.434

Sorry but I would disagree a bit. They can be fastest on some occasions but not always. Triggers are generally called evil because of the additional problems it can cause which can also include performance issues.

Aug 06, 2012 at 11:07 AM Usman Butt

As mentioned it depends how the db is used and accessed. Triggers are for sure the safest way, if users (developers) have dirrect access to the uderlying tables as in that scenario TRIGGERS will solve every possible way of inserts/updates.

On the other way, if access to the underlying tables is limited and possible only using the views, functions and stored procedures then with proper procedures and security design, it will sever the same way as TRIGGERS and the insets/updates of other tables can be optimized based on the operations done by the stored procs.

Aug 06, 2012 at 11:38 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 03, 2012 at 11:16 AM

Seen: 3864 times

Last Updated: Aug 06, 2012 at 11:38 AM

Copyright 2018 Redgate Software. Privacy Policy