create one insert stored for multiple tables
I had applicant project with multiple pages as the user will go through these pages to complete his applicant data then at the last page he will find submit the data.
My problem is that, I have multiple tables and when the user submit his data the data will inserted into it’s table , so how can I create one stored for all these table .
2 Solutions collect form web for “create one insert stored for multiple tables”
If you want to insert/update several tables at once, you would be better off using a stored procedure. Create/Read/Update/Delete (CRUD) stored procedure are generally good practice and reduce the risk of SQL injection attacks.
Here’s what your Create (insert) stored procedure might look like (it’s incomplete and unnormalized):
CREATE PROCEDURE [dbo].[ApplicantIns] ( @Name nvarchar(50) ,@Skill nvarchar(5) ,@Age int ,@comment nvarchar(50) ) AS SET NOCOUNT ON; INSERT INTO [dbo].[Applicant]([name]) VALUES(@Name); INSERT INTO [dbo].[Skillset]([Skill], [Comment]) VALUES(@Skill,@Comment); INSERT INTO [dbo].[Statistics]([Age]) VALUES (@Age);
You could then call this stored procedure when someone submits a new application.
Are you using .NET Framework? If so, what version?
In case you’re using .NET > 3.5 + MS SQL 2008 you can use table valued parameters