Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL PUZZLE | How to explicitly Insert the NEXT default value in a Column? | SQL Interview Question

In this puzzle you have to explicitly call the insert statement to insert the next default value in the id column from the default constraint. For more details please see the sample input and expected output.

Sample Input

Id
70D8AD6F-8152-E811-B049-68F728AE8695

Note – For your machine value can be uniqueidentifier.

Expected Output

Id
70D8AD6F-8152-E811-B049-68F728AE8695
B39FE98E-8152-E811-B049-68F728AE8695

–We have to explicitly call the insert statement to insert the next value.

Script – DDL and INSERT Sample Data

--

CREATE TABLE [dbo].[OneColumn]
(
    [Id] [uniqueidentifier] NOT NULL
)
GO

ALTER TABLE [dbo].[OneColumn] 
    ADD  CONSTRAINT [df_id]  DEFAULT (NEWSEQUENTIALID()) FOR [Id]
GO

SELECT * FROM [dbo].[OneColumn]
GO

--

SOLUTION – 1

--

DECLARE @myId TABLE (Id UNIQUEIDENTIFIER)

INSERT INTO [OneColumn] 
OUTPUT inserted.Id INTO @myId DEFAULT VALUES

SELECT * FROM @myId
GO

--

Output – 1

--

(1 row affected)
Id
------------------------------------
A9E7BC08-8252-E811-B049-68F728AE8695

(1 row affected)

Id
------------------------------------
70D8AD6F-8152-E811-B049-68F728AE8695
B39FE98E-8152-E811-B049-68F728AE8695
A8E7BC08-8252-E811-B049-68F728AE8695
A9E7BC08-8252-E811-B049-68F728AE8695

(4 rows affected)

--

SOLUTION – 2

--

DECLARE @Id TABLE (Id UNIQUEIDENTIFIER)
INSERT INTO [OneColumn] (Id)
OUTPUT inserted.Id INTO @Id VALUES(DEFAULT)

SELECT * FROM @Id
GO

SELECT * FROM [dbo].[OneColumn]
GO

--

Output – 2

--

(1 row affected)
Id
------------------------------------
D2AEEF15-8252-E811-B049-68F728AE8695

(1 row affected)

Id
------------------------------------
70D8AD6F-8152-E811-B049-68F728AE8695
B39FE98E-8152-E811-B049-68F728AE8695
A8E7BC08-8252-E811-B049-68F728AE8695
A9E7BC08-8252-E811-B049-68F728AE8695
D2AEEF15-8252-E811-B049-68F728AE8695

(5 rows affected)

--

Microsoft Reference

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com