This article shares the information about what happens when INSERT Statement is used inside Stored procedure with & without Columns list .
--Scenario 1 - INSERT statement Without Columnslist inside Stored procedure
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 .
--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 .
No comments:
Post a Comment