hi, can you please make a video covering the topic like create a store procedure that can read the data from a excel file and then save that data into table
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
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.
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;
Easily the best Stored Procedure video I've seen up to now, well done!
Glad you liked it and thanks for your kind words!
Please upload more such type of industry oriented videos , much helpful thanks
very informative video thanks for sharing your knowledge
Glad it was helpful!
Very Helpful, Thank you!
hi,
can you please make a video covering the topic like create a store procedure that can read the data from a excel file and then save that data into table
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🥲