stored procedure to insert multiple values with splitting

Question

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

Answer 1

0

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 or CLR Splitter on SQL Server 2005+ (Fastest CSV strings splitting using CLR (T-SQL vs. CLR revisited)).

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'

answered Aug 02 '12 at 01:15 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Thank you so much pavel

Aug 03 '12 at 04:20 AM tsaliki

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.

Aug 03 '12 at 06:06 AM Pavel Pawlowski

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

Aug 03 '12 at 07:25 AM tsaliki

Learn about the MERGE statement. It will help! :)

Aug 03 '12 at 07:50 AM eghetto

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]

Aug 03 '12 at 08:11 AM Pavel Pawlowski

show 3 more replies

(comments are locked)

stored procedure to insert multiple values with splitting

Follow this question

Related Questions