asked
stored procedure to insert multiple values with splitting
i have a table Companiesdata CREATE TABLE [dbo].[Companiesdata] ( [Company Name] nvarchar(255) NULL, [ParentId] int NULL, ) the records are CompanyName Parent ID XYZ technologies 1 apple Technologies 1 EGG BANK 2 abc Technologies 4 PQR Technnologies 5 Now i have another table companynames create table [dbo].[companynames] ( companyname varchar(max) ) Now i want to write a procedure such that when i give multiple parentids like ex: 1,2 the companyname corresponding to the ids ( here ids 1,2) must insert into companynames table. I want the out put as: exec parentid 1,2 the records must be displayed as xyz technologies apple technlogies egg bank
You can pass the parameter as comma delimited string and us eg. the CSV spillting function by Jeff Moden ([Tally OH! An Improved SQL 8K “CSV Splitter” Function][1] or CLR Splitter on SQL Server 2005+ ([Fastest CSV strings splitting using CLR (T-SQL vs. CLR revisited)][2]). Using the Jeff Moden's function you can use have a stored proc something like: OCEDURE [dbo].[usp_InsertCompanies] @ids varchar(8000) AS BEGIN INSERT INTO [dbo].companynames( [comnanyname] ) SELECT [Company Name] FROM [dbo].[Companieesdata] WHERE [ParentId] IN (SELECT [Item] FROM [dbo].[DelimitedSplit8K](@ids, ',') END Than you can call it using: `EXEC [dbo].[usp_InsertCompanies] '1,2'` [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/ [2]:
http://www.pawlowski.cz/2010/10/fastest-csv-strings-splitting-using-clr-t-sql-vs-clr-revisited/
For SQL 2008+ you can use MERGE as mentioned for ALL other versions this concrete situation you can handle using the EXCEPT INSERT INTO [dbo].companynames( [comnanyname] ) SELECT [Company Name] FROM [dbo].[Companieesdata] WHERE [ParentId] IN (SELECT [Item] FROM [dbo].[DelimitedSplit8K](@ids, ',') EXCEPT SELECT [Company Name] FROM [dbo].[companynames]
If it solved you rproblem, you can accept the answer, so other users when searching answers will know, that it is the solution to the problem.
hi pavel for the above question i want to add something.. i want to check if the given company is already inserted that if it is present in the companynames table then do not insert else insert.that is i dont want duplicates to be inserted again.Hope u got my question
9 People are following this question.
