Inserting data into a table that has just one column which is identity type???

If you have a table with one column, which is promoted with an identity column and you were told to insert rows into the table, how you will be performing the command?

Let’s stimulate the case by taking a table OneColumnTable with only one column id

create table OneColumnTable

(id int identity(1,1))

Now try to insert record in this table using insert statement

insert into OneColumnTable values(1)

//OUTPUT

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'OneColumnTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So it is not allowing to insert record in the table, but then you got a task to insert data into this table, how to do the insertion, lets fire the below query

insert into OneColumnTable default values

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

(1 row(s) affected

Note: If you want to continue inserting identity values, then you can make use of default value clause

And, If you want to insert explicit values then you can write

set identity_insert oneColumnTable on

insert into OneColumnTable(id) values(5);

set identity_insert oneColumnTable on

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

5

(2 row(s) affected)

That’s how you can insert values in identity column. J

Put out of your mind, it’s coding; very straightforward J   

Post Reference: Vikram Aristocratic Elfin Share

How to call a user defined function with a default parameter

Today it happened one of my team member called up me and ask me how to call his function where he has used default parameter. The snapshot of his function is like this only..

create function fnTest(@param1 int, @param2 int = 1 )

returns int

as

begin      

      return @param2      

end

His question was how to call this function in select statement, I simple said, u can either pass two parameter while calling this function, if you are doing so then the second default parameter @param2 value  will get replaced by the new one you pass while calling this function.

select dbo.fnTest(2,5)

-----------

5

(1 row(s) affected)

Where as  if you pass only one parameter to the calling funtion then the @param2 will take the dufault value as 1 in this case.

I give him the below mention line for calling the funtion with one parameter as soon as we excute this line of code we got  error message

select dbo.fnTest(7)

Msg 313, Level 16, State 2, Line 1

An insufficient number of arguments were supplied for the procedure or function dbo.fnTest.

Belive me I was not aware of this then somebody from other team sitting behind us told to use default keyword while calling funtion with default parameter.

select dbo.fnTest(1,default)

-----------

1

(1 row(s) affected)

Thanks Shekhar for the solution.

Post Reference: Vikram Aristocratic Elfin Share