How to insert data into more than one table using sp in sql server 2012
0
How do I insert data into several tables each with different types and number of parameters. e.g
Table1
Code Type
'A' X
Table 2
Code Type Status Dasl
Here the number of parameters are different.
In this case how to insert the data into tables using a common stored procedure.
sql-server edited Nov 15 '18 at 9:10
Paddy
2,50512042
asked Nov 15 '18 at 8:34
sasisasi
318
add a comment |
0
How do I insert data into several tables each with different types and number of parameters. e.g
Table1
Code Type
'A' X
Table 2
Code Type Status Dasl
Here the number of parameters are different.
In this case how to insert the data into tables using a common stored procedure.
sql-server edited Nov 15 '18 at 9:10
Paddy
2,50512042
asked Nov 15 '18 at 8:34
sasisasi
318
1
you will required 2insertstatement. One for each table. Create stored procedure for each of the table. Don't try to make it common. Unless the 2 tables are related with FK, in this case, you do it in a single stored procedure within one transaction
– Squirrel
Nov 15 '18 at 8:49
2
I agree - don't try and make a "kitchen sink" stored procedure that can insert into any table. This is overly complicated and a database anti-pattern. What is your objective? Limit database access through a stored proc?
– Nick.McDermaid
Nov 15 '18 at 9:09
What is your end goal? Why do you need to create theStored Procedurein the first place?
– Birel
Nov 15 '18 at 9:13
@Birel my requirement is to save it through stored procedure.
– sasi
Nov 15 '18 at 9:19
add a comment |
0
0
0
How do I insert data into several tables each with different types and number of parameters. e.g
Table1
Code Type
'A' X
Table 2
Code Type Status Dasl
Here the number of parameters are different.
In this case how to insert the data into tables using a common stored procedure.
sql-server edited Nov 15 '18 at 9:10
Paddy
2,50512042
asked Nov 15 '18 at 8:34
sasisasi
318
How do I insert data into several tables each with different types and number of parameters. e.g
Table1
Code Type
'A' X
Table 2
Code Type Status Dasl
Here the number of parameters are different.
In this case how to insert the data into tables using a common stored procedure.
sql-server sql-server edited Nov 15 '18 at 9:10
Paddy
2,50512042
asked Nov 15 '18 at 8:34
sasisasi
318
edited Nov 15 '18 at 9:10
Paddy
2,50512042
asked Nov 15 '18 at 8:34
sasisasi
318
edited Nov 15 '18 at 9:10
Paddy
2,50512042
edited Nov 15 '18 at 9:10
Paddy
2,50512042
edited Nov 15 '18 at 9:10
Paddy
2,50512042
2,50512042
asked Nov 15 '18 at 8:34
sasisasi
318
asked Nov 15 '18 at 8:34
sasisasi
318
asked Nov 15 '18 at 8:34
sasisasi
318
318
1
you will required 2insertstatement. One for each table. Create stored procedure for each of the table. Don't try to make it common. Unless the 2 tables are related with FK, in this case, you do it in a single stored procedure within one transaction
– Squirrel
Nov 15 '18 at 8:49
2
I agree - don't try and make a "kitchen sink" stored procedure that can insert into any table. This is overly complicated and a database anti-pattern. What is your objective? Limit database access through a stored proc?
– Nick.McDermaid
Nov 15 '18 at 9:09
What is your end goal? Why do you need to create theStored Procedurein the first place?
– Birel
Nov 15 '18 at 9:13
@Birel my requirement is to save it through stored procedure.
– sasi
Nov 15 '18 at 9:19
add a comment |
1
you will required 2insertstatement. One for each table. Create stored procedure for each of the table. Don't try to make it common. Unless the 2 tables are related with FK, in this case, you do it in a single stored procedure within one transaction
– Squirrel
Nov 15 '18 at 8:49
2
I agree - don't try and make a "kitchen sink" stored procedure that can insert into any table. This is overly complicated and a database anti-pattern. What is your objective? Limit database access through a stored proc?
– Nick.McDermaid
Nov 15 '18 at 9:09
What is your end goal? Why do you need to create theStored Procedurein the first place?
– Birel
Nov 15 '18 at 9:13
@Birel my requirement is to save it through stored procedure.
– sasi
Nov 15 '18 at 9:19
1
1
you will required 2
insert statement. One for each table. Create stored procedure for each of the table. Don't try to make it common. Unless the 2 tables are related with FK, in this case, you do it in a single stored procedure within one transaction– Squirrel
Nov 15 '18 at 8:49
you will required 2
insert statement. One for each table. Create stored procedure for each of the table. Don't try to make it common. Unless the 2 tables are related with FK, in this case, you do it in a single stored procedure within one transaction– Squirrel
Nov 15 '18 at 8:49
2
2
I agree - don't try and make a "kitchen sink" stored procedure that can insert into any table. This is overly complicated and a database anti-pattern. What is your objective? Limit database access through a stored proc?
– Nick.McDermaid
Nov 15 '18 at 9:09
I agree - don't try and make a "kitchen sink" stored procedure that can insert into any table. This is overly complicated and a database anti-pattern. What is your objective? Limit database access through a stored proc?
– Nick.McDermaid
Nov 15 '18 at 9:09
What is your end goal? Why do you need to create the
Stored Procedure in the first place?– Birel
Nov 15 '18 at 9:13
What is your end goal? Why do you need to create the
Stored Procedure in the first place?– Birel
Nov 15 '18 at 9:13
@Birel my requirement is to save it through stored procedure.
– sasi
Nov 15 '18 at 9:19
@Birel my requirement is to save it through stored procedure.
– sasi
Nov 15 '18 at 9:19
add a comment |
2 Answers
2
active
oldest
votes
0
Create sample stored procedure to get an idea how to save data into records.
Create Table Type like below to insert multiple records in to your database
CREATE TYPE [dbo].[tbl_Table2] AS TABLE(
[Code] [char](2) NULL,
[Type] [char](2) NULL,
[Status] [varchar](5) NULL,
[Dasl] [varchar](5) NULL
)
GO
CREATE PROCEDURE [dbo].[Usp_DataInsert]
(
@Code CHAR(2),
@Type CHAR(2),
@tbl_Table2 AS tbl_Table2 READONLY
)
AS
BEGIN TRY
IF EXISTS(SELECT 1 FROM @tbl_Table2)
BEGIN
INSERT INTO Table2
(
Code
,[Type]
,[Status]
,Dasl
)
SELECT Code
,[Type]
,[Status]
,Dasl
FROM @tbl_Table2
END
INSERT INTO Table1
(
Code,
[Type]
)
SELECT @Code,@Type
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
Insert data into table through stored procedure
DECLARE @RC int
DECLARE @Code char(2)='Z'
DECLARE @Type char(2)=33
DECLARE @tbl_Table2 tbl_Table2
INSERT INTO @tbl_Table2
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB'
EXECUTE @RC = [dbo].[Usp_DataInsert]
@Code
,@Type
,@tbl_Table2
GO
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
add a comment |
0
You need to use different stored procedures to insert with multiple tables with different parameters, You can use transaction from front end also
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53315252%2fhow-to-insert-data-into-more-than-one-table-using-sp-in-sql-server-2012%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Create sample stored procedure to get an idea how to save data into records.
Create Table Type like below to insert multiple records in to your database
CREATE TYPE [dbo].[tbl_Table2] AS TABLE(
[Code] [char](2) NULL,
[Type] [char](2) NULL,
[Status] [varchar](5) NULL,
[Dasl] [varchar](5) NULL
)
GO
CREATE PROCEDURE [dbo].[Usp_DataInsert]
(
@Code CHAR(2),
@Type CHAR(2),
@tbl_Table2 AS tbl_Table2 READONLY
)
AS
BEGIN TRY
IF EXISTS(SELECT 1 FROM @tbl_Table2)
BEGIN
INSERT INTO Table2
(
Code
,[Type]
,[Status]
,Dasl
)
SELECT Code
,[Type]
,[Status]
,Dasl
FROM @tbl_Table2
END
INSERT INTO Table1
(
Code,
[Type]
)
SELECT @Code,@Type
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
Insert data into table through stored procedure
DECLARE @RC int
DECLARE @Code char(2)='Z'
DECLARE @Type char(2)=33
DECLARE @tbl_Table2 tbl_Table2
INSERT INTO @tbl_Table2
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB'
EXECUTE @RC = [dbo].[Usp_DataInsert]
@Code
,@Type
,@tbl_Table2
GO
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
add a comment |
0
Create sample stored procedure to get an idea how to save data into records.
Create Table Type like below to insert multiple records in to your database
CREATE TYPE [dbo].[tbl_Table2] AS TABLE(
[Code] [char](2) NULL,
[Type] [char](2) NULL,
[Status] [varchar](5) NULL,
[Dasl] [varchar](5) NULL
)
GO
CREATE PROCEDURE [dbo].[Usp_DataInsert]
(
@Code CHAR(2),
@Type CHAR(2),
@tbl_Table2 AS tbl_Table2 READONLY
)
AS
BEGIN TRY
IF EXISTS(SELECT 1 FROM @tbl_Table2)
BEGIN
INSERT INTO Table2
(
Code
,[Type]
,[Status]
,Dasl
)
SELECT Code
,[Type]
,[Status]
,Dasl
FROM @tbl_Table2
END
INSERT INTO Table1
(
Code,
[Type]
)
SELECT @Code,@Type
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
Insert data into table through stored procedure
DECLARE @RC int
DECLARE @Code char(2)='Z'
DECLARE @Type char(2)=33
DECLARE @tbl_Table2 tbl_Table2
INSERT INTO @tbl_Table2
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB'
EXECUTE @RC = [dbo].[Usp_DataInsert]
@Code
,@Type
,@tbl_Table2
GO
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
add a comment |
0
0
0
Create sample stored procedure to get an idea how to save data into records.
Create Table Type like below to insert multiple records in to your database
CREATE TYPE [dbo].[tbl_Table2] AS TABLE(
[Code] [char](2) NULL,
[Type] [char](2) NULL,
[Status] [varchar](5) NULL,
[Dasl] [varchar](5) NULL
)
GO
CREATE PROCEDURE [dbo].[Usp_DataInsert]
(
@Code CHAR(2),
@Type CHAR(2),
@tbl_Table2 AS tbl_Table2 READONLY
)
AS
BEGIN TRY
IF EXISTS(SELECT 1 FROM @tbl_Table2)
BEGIN
INSERT INTO Table2
(
Code
,[Type]
,[Status]
,Dasl
)
SELECT Code
,[Type]
,[Status]
,Dasl
FROM @tbl_Table2
END
INSERT INTO Table1
(
Code,
[Type]
)
SELECT @Code,@Type
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
Insert data into table through stored procedure
DECLARE @RC int
DECLARE @Code char(2)='Z'
DECLARE @Type char(2)=33
DECLARE @tbl_Table2 tbl_Table2
INSERT INTO @tbl_Table2
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB'
EXECUTE @RC = [dbo].[Usp_DataInsert]
@Code
,@Type
,@tbl_Table2
GO
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
Create sample stored procedure to get an idea how to save data into records.
Create Table Type like below to insert multiple records in to your database
CREATE TYPE [dbo].[tbl_Table2] AS TABLE(
[Code] [char](2) NULL,
[Type] [char](2) NULL,
[Status] [varchar](5) NULL,
[Dasl] [varchar](5) NULL
)
GO
CREATE PROCEDURE [dbo].[Usp_DataInsert]
(
@Code CHAR(2),
@Type CHAR(2),
@tbl_Table2 AS tbl_Table2 READONLY
)
AS
BEGIN TRY
IF EXISTS(SELECT 1 FROM @tbl_Table2)
BEGIN
INSERT INTO Table2
(
Code
,[Type]
,[Status]
,Dasl
)
SELECT Code
,[Type]
,[Status]
,Dasl
FROM @tbl_Table2
END
INSERT INTO Table1
(
Code,
[Type]
)
SELECT @Code,@Type
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
Insert data into table through stored procedure
DECLARE @RC int
DECLARE @Code char(2)='Z'
DECLARE @Type char(2)=33
DECLARE @tbl_Table2 tbl_Table2
INSERT INTO @tbl_Table2
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB' UNION ALL
SELECT 'A','X','A','AB'
EXECUTE @RC = [dbo].[Usp_DataInsert]
@Code
,@Type
,@tbl_Table2
GO
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
answered Nov 15 '18 at 10:22
Sreenu131Sreenu131
1,465138
1,465138
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
add a comment |
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
Hi, This is good meeting the requirement. But my front end screen is Excel VBA. So here I cant use table valued parameters. What I am done is created a master sp from that i called different stored procedures each table wise. Is this holds good?
– sasi
Nov 16 '18 at 4:00
add a comment |
0
You need to use different stored procedures to insert with multiple tables with different parameters, You can use transaction from front end also
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
add a comment |
0
You need to use different stored procedures to insert with multiple tables with different parameters, You can use transaction from front end also
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
add a comment |
0
0
0
You need to use different stored procedures to insert with multiple tables with different parameters, You can use transaction from front end also
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
You need to use different stored procedures to insert with multiple tables with different parameters, You can use transaction from front end also
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
answered Nov 15 '18 at 10:27
Julius MasilamaniJulius Masilamani
11
11
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53315252%2fhow-to-insert-data-into-more-than-one-table-using-sp-in-sql-server-2012%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
you will required 2
insertstatement. One for each table. Create stored procedure for each of the table. Don't try to make it common. Unless the 2 tables are related with FK, in this case, you do it in a single stored procedure within one transaction– Squirrel
Nov 15 '18 at 8:49
2
I agree - don't try and make a "kitchen sink" stored procedure that can insert into any table. This is overly complicated and a database anti-pattern. What is your objective? Limit database access through a stored proc?
– Nick.McDermaid
Nov 15 '18 at 9:09
What is your end goal? Why do you need to create the
Stored Procedurein the first place?– Birel
Nov 15 '18 at 9:13
@Birel my requirement is to save it through stored procedure.
– sasi
Nov 15 '18 at 9:19