November 2015

Insert multiple rows into a table from another table and get identity values

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

Remove illegal characters from file name or file path in c#

File cannot be saved if file name has illegal characters. So how to remove the illegal characters by a single line code as below. I use LINQ Querty for that.


string fileName = "prakash C/O swami.xml";
fileName = System.IO.Path.GetInvalidFileNameChars().Aggregate(fileName, (current, c) => current.Replace(c.ToString(), string.Empty));

Now the fileName is “rakash CO swami.xml”. The code removes slash ‘/’ because it is a illegal characters. As same any illegal characters will remove using this above code.