x

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

more ▼

asked Aug 02 '12 at 10:46 AM in Default

tsaliki gravatar image

tsaliki
150 8 10 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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'

more ▼

answered Aug 02 '12 at 01:15 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

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
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402
x103
x22

asked: Aug 02 '12 at 10:46 AM

Seen: 1429 times

Last Updated: Aug 03 '12 at 09:37 AM