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, 2012 at 10:46 AM in Default

avatar image

tsaliki
150 10 10 14

(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, 2012 at 01:15 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Thank you so much pavel

Aug 03, 2012 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, 2012 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, 2012 at 07:25 AM tsaliki

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

Aug 03, 2012 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, 2012 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.

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:

x455
x137
x27

asked: Aug 02, 2012 at 10:46 AM

Seen: 3164 times

Last Updated: Aug 03, 2012 at 09:37 AM

Copyright 2016 Redgate Software. Privacy Policy