T-SQL - INSERT Statement with Columnslist inside Stored procedure

This article shares the information about what happens when INSERT Statement is used  inside Stored procedure with & without Columns list .


--Create Sample table
CREATE TABLE Test (Id INT , Name VARCHAR(20))
GO


--Scenario 1 - INSERT statement Without Columnslist inside Stored procedure




--Create Stored procedure to insert data into table

CREATE PROCEDURE SP_Insert_WithoutColumnslist
AS
BEGIN
INSERT Test SELECT 1,'Sathya'
END
GO

--Add new column to the table

ALTER TABLE Test ADD Age INT NULL
GO

--Execute Stored procedure

EXEC SP_Insert_WithoutColumnslist
GO








--Scenario 2 - INSERT statement With Columnslist inside Stored procedure

CREATE PROCEDURE SP_Insert_WithColumnslist
AS
BEGIN
INSERT Test (Id,Name) SELECT 2,'Deepak'
END

EXEC SP_Insert_WithColumnslist
GO

SELECT * FROM Test

DROP TABLE Test
DROP PROCEDURE SP_Insert_WithoutColumnslist,SP_Insert_WithColumnslist







Sometimes due change in Data model we might add new columns to the table , if the Stored procedure inserting data into that table is already running in production , after adding new column , Stored procedure will fail as discussed above in Scenario 1 and Stored procedure will insert NULL into new column as discussed above in Scenario 2 . Care should taken for both scenario's .

Here is the tip :

If you are too lazy to type each columns (for table with many columns) inspite of Intellisence help , Under Object Explorer - >  Databases - > Tables - > Expand the table node - > you will notice Columns folder , drag&drop that on Query window , you will get comma separated columns list .



See Also:




 

No comments: