Wednesday 14 December 2016

Interview Question - To populate a table without specifying values in the INSERT statement

This one should be quick and most of must be aware of it. 

Following question was asked to one of my friend in an interview for the post of Sr. Software Engineer

How would you populate following table with 5 records without specifying values in INSERT statement ?

The table structure was as follows

IF OBJECT_ID('tempdb..#default') IS NOT NULL
DROP TABLE #default

CREATE TABLE #default
(
id INT IDENTITY(1,1),
software VARCHAR(20) DEFAULT('microsoft'),
osType VARCHAR(10)
)

GO

My friend did not know the answer to this question and honestly neither was I aware of the way to get it done... what I liked about this question is the way interviewer made simple question looked tricky.... 

The answer lies in the structure of the table... If you observe closely you'll understand that both the columns of the table can be automatically populated... The "id" column is an identity column which will be auto populated while "software" column has default value assigned to it while "osType" column can accept NULL value.  This means all the columns can generate values for themselves when not explicitly provided..If we can make INSERT statement add default value to "software" column then that should solve the problem.....

So the question can be framed as, is there a way to populate table with the default values ? And the answer to that question is YES... 

Try running following statement along with the table structure code give above and you'll see the results

INSERT INTO #default DEFAULT VALUES
GO 5

SELECT * FROM #default


No comments:

Post a Comment

bloggerwidgets