question

tsaliki avatar image
tsaliki 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
stored-proceduresinsertsplit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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/
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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]
1 Like 1 ·
Thank you so much pavel
0 Likes 0 ·
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.
0 Likes 0 ·
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
0 Likes 0 ·
Learn about the MERGE statement. It will help! :)
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.