Stored Procedure in SQL Server for insert and update
HTML-код
- Опубликовано: 19 июл 2022
- In this SQL Server video tutorial, I will create a Stored Procedure in SQL Server for inserting and Updating data in the database. The tutorial contains the latest tips and tricks to insert and update the data using SQL Server Stored Procedure.
Here is the list of queries that I will discuss
1. Stored procedure for insert and update in SQL Server
2. Stored procedure in SQL Server for inserting data into a table
3. Stored procedure in SQL Server to insert into table
4. Stored procedure SQL Server insert date
5. Insert stored procedure in SQL Server with parameters
6. Insert stored procedure in SQL Server example
7. Stored procedure SQL Server bulk insert
8. Stored procedure SQL Server select insert
9. Stored procedure in SQL Server for insert update delete
10. Stored procedure SQL Server update table
11. Update stored procedure in SQL Server example
12. Stored procedure for bulk update in SQL Server
++++++++++++++++++++++++++++++++++++
Check out the complete tutorial on SQL Server stored procedure for insert and update
databasefaqs.com/stored-proce...
++++++++++++++++++++++++++++++++++++
Check out previous SQL Server tutorials
How to Find Text in Stored Procedures in SQL Server • How to Find Text in St...
How to create table in azure SQL database
• How to create table in...
How to schedule a stored procedure in SQL
• How to Schedule a Stor...
++++++++++++++++++++++++++++++++++++
Want to learn machine learning? Check out a complete training course on Python and Machine Learning Training Course sharepointsky.teachable.com/p...
++++++++++++++++++++++++++++++++++++
Subscribe to Our RUclips Channel for more videos on Python, Blockchain, Bitcoin, Solidity, Ethereum, Cryptocurrency, Azure SQL , SQL Server, PostgreSQL, MongoDB, Oracle, MariaDB, etc
/ tsinfotechnologies
\\ Playlist
SQL Server: • Create a stored proced...
MariaDB: • How to truncate table ...
PostgreSQL: • How to Create table in...
+++++++++++++++++++++++
#sqlserver
#sqlstoredprocedure
#storedprocedure
#sqlservermanagementstudio
#sqltutorial
#sqltutorialforbeginners
storedproceduresqlserverforinsertandupdate
Do Visit Our Websites
pythonguides.com/
databasefaqs.com/
salesforcefaqs.com/
technologydots.com/
enjoysharepoint.com/
spguides.com/ Наука
Easily the best Stored Procedure video I've seen up to now, well done!
Glad you liked it and thanks for your kind words!
Very Helpful, Thank you!
Please upload more such type of industry oriented videos , much helpful thanks
very informative video thanks for sharing your knowledge
Glad it was helpful!
Yathish Kumar
1 second ago
hi, i would like know the logic to create a stored procedure to "insert data into old table EMP from the table EMP2" by giving the name of the table emp2 with stored procedure. could you please lead me to resolve this
can you do the video how to update database from one server to an existing database on other server? Thanks for a great video
I am a new sql learner, and feeling joyous to learn from this channel.
I have only simple enquiry.
I came across the varchar() function... What is the best practice when deciding to chose a number for that function given i am going to extract names for example...Can i put 1000 for instance so that i can be in the safe side or that may rise problems.
Thanks a lot.
See varchar is used to represent data of variable-size string. Here is its full syntax
varchar [ ( n | max ) ]
Here n is utilized to specify the string size in bytes and it ranges from 1 to 8000. Alternatively, we can also specify max in place of n to specify the contact column size with the maximum storage of 2 GB.
Hai, Please help me. I want to create a stored Procedure to handle Bulk insert or update.
Here is the steps that you can follow.
Syntax: Below is the syntax you can use to perform bulk insert and update.
1. Create a type that matches the structure of your target table, which means defining the stored procedure with a parameter that accepts a table-valued parameter (TVP). TVPs allow you to pass a set of rows to a stored procedure or a function.
CREATE TYPE dbo.YourTableType AS TABLE(
ID INT,
Column1 VARCHAR(50),
Column2 INT,
-- Add more column here
IsNewRecord BIT -- This column will be used to determine if a row should be inserted or updated
);
2. Create the stored procedure
CREATE PROCEDURE dbo.usp_InsertOrUpdateYourTable
@YourTableData dbo.YourTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
-- Perform the Update
UPDATE yt
SET yt.Column1 = tmp.Column1,
yt.Column2 = tmp.Column2
-- Add other columns as needed
FROM dbo.YourTable yt
INNER JOIN @YourTableData tmp ON yt.ID = tmp.ID
WHERE tmp.IsNewRecord = 0; -- 0 indicates an existing record
--Perform the Insert
INSERT INTO dbo.YourTable (ID, Column1, Column2 /* Add other columns as needed */)
SELECT ID, Column1, Column2 /* Add other columns as needed */
FROM @YourTableData
WHERE IsNewRecord = 1 AND ID NOT IN (SELECT ID FROM dbo.YourTable); -- 1 indicates a new record
END;
Example: Take the example of a Product table with columns ProductID, ProductName, Price, and Quantity.
1. First, define a table type that matches the structure of the Product table, with an additional column to indicate whether the record is new or existing.
CREATE TYPE dbo.ProductType AS TABLE(
ProductID INT,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
Quantity INT,
IsNewRecord BIT -- 0 for update, 1 for insert
);
2. Create a stored procedure that takes a ProductType table-valued parameter and performs bulk insert or update based on the IsNewRecord flag.
CREATE PROCEDURE dbo.usp_InsertOrUpdateProducts
@Products dbo.ProductType READONLY
AS
BEGIN
SET NOCOUNT ON;
-- Update existing products
UPDATE p
SET p.ProductName = tmp.ProductName,
p.Price = tmp.Price,
p.Quantity = tmp.Quantity
FROM dbo.Product p
INNER JOIN @Products tmp ON p.ProductID = tmp.ProductID
WHERE tmp.IsNewRecord = 0;
-- Insert new products
INSERT INTO dbo.Product (ProductID, ProductName, Price, Quantity)
SELECT ProductID, ProductName, Price, Quantity
FROM @Products
WHERE IsNewRecord = 1 AND ProductID NOT IN (SELECT ProductID FROM dbo.Product);
END;
its Ok but this is not fair🥲