Insert a string many times into an SQL statement.

I have to move 1500 parts from one location to another and need to insert the item number the old location and new location into the following SQL statement. How can I do this? I was thinking something like mail merge but that really doesn't work. I was then thinking of making the entries into a variable and have it read an excel spreadsheet but really don't know how to do that and wonder if someone might be able to help.

exec p21_move_qty_wwms_wrapper
@item_id = '<ITEM ID>'  
,@location_id = <LOCATION ID>  
,@from_bin ='<FROM BIN ID>'  
,@to_bin ='<TO BIN ID>'  
,@move_full_qty = ‘Y'
TimSr. System AdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
The simple way..
INSERT INTO newlocation (column1, column2, columnN) 
SELECT column1, column2, columnN
FROM oldlocation

Open in new window

Does the SP  return all 1,500 rows, or one at a time?  If 1500, then assuming that the schema of the newlocation and the SP return set are exactly the same..
INSERT INTO newlocation (column1, column2, columnN) 
exec p21_move_qty_wwms_wrapper {parameters go here}

Open in new window

Also, explain the string part of 'Insert a string many times into an SQL statement.', as the question reads as if you wish to insert rows.
Bill PrewIT / Software Engineering ConsultantCommented:
Where do you currently have the 1500 sets of data located that you need to pass to the SP one at a time?  Is that data in a table someplace?  In a text file, or Excel?  As I understand your need, you need to execute the SP 1500 times, passing it the proper parms each time.  Where will the values for each set of parms you pass to the SP come from?

~bp
TimSr. System AdminAuthor Commented:
Yes the items, to and From bins are on an excel spreadsheet and need to insert them into this query.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Bill PrewIT / Software Engineering ConsultantCommented:
Okay, here is one approach I use for one time things like this.  I add a new column, and then build up the SQL statement I need to execute in it, using concatenated fixed text and inserting the data from the excel sheet where it needs to be inserted.  Attached is an example, see if this makes sense and might help.

~bp
EE28704594.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimSr. System AdminAuthor Commented:
That worked great! Thanks.
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad that was helpful.

~bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.