Tags

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


SQL PUZZLE | Can you Insert NON Matching ROWS in IDENTITY column? | SQL Interview Question

In this puzzle you have find the non matching rows from two tables(Identity Table,Another Table) and then whatever count you got insert the same number of rows into Identity Table. Lets say you got 4 NON matching rows from the two tables then in that case you need insert 4 rows into the Identity Table. Please check out sample input and expected output.

Sample Input

Identity Table

Id
1
2

Another Table

Id
3
4
1

Expected output

Output required Identity Table

Id
1
2
3
4

Script – DDL and INSERT sample data

--

CREATE TABLE IdentityTable
(
	Id INT IDENTITY(1,1)
)
GO

INSERT INTO IdentityTable DEFAULT VALUES
GO

INSERT INTO IdentityTable DEFAULT VALUES
GO

CREATE TABLE AnotherTable
(
	 Id INT
)
GO

INSERT INTO AnotherTable VALUES (3),(4),(1)
GO

--

SOLUTION 1 | USING Merge Statement

--

MERGE INTO IdentityTable a
USING ( SELECT * FROM AnotherTable ) T
ON a.Id = T.Id
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;

--

OUTPUT – 1

--

--
(2 rows affected)
--

SELECT * FROM IdentityTable
GO

Id
-----------
1
2
3
4

(4 rows affected)

--

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

Advertisements