Let me explain the case first, so you will get the idea when this is required to use. In the database, there is more than one tables which store the future values and will move on specific time to standard tables. For example,
Table “UserList2” contains the future values consider as temporary table. Table “UserList1” contains standard values consider as standard table. SQL SCRIPT as below:
GO
/****** Object: Table [dbo].[UserList2] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
CONSTRAINT [PK_UserList2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList2] ON
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (1, N'Prakash', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (2, N'Rupal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (3, N'Arpit', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (4, N'Vishal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (5, N'Ajay', N'Gandhinagar')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (6, N'Kamal', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (7, N'Akshay', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (8, N'Khimjibhai', N'Gandhinagar')
SET IDENTITY_INSERT [dbo].[UserList2] OFF
/****** Object: Table [dbo].[UserList1] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[UserList1Id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList1] ON
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (1, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (2, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (3, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (4, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (5, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (6, N'Arpit', N'Anand', 3)
SET IDENTITY_INSERT [dbo].[UserList1] OFF
Want to move data from UserList2 to UserList1 and need to use UserList1 Identity column for further process, how to get all newly inserted which as below:
DECLARE @output TABLE (id int)
Insert into UserList1 (Name, City, UserList1Id)
OUTPUT inserted.Id INTO @output
SELECT Name, City, Id FROM UserList2 Where City='Anand'
select * from @output
OUTPUT clause introduced in MS SQL Server 2005. Want to know more about OUTPUT clause refer this link
