Looping Multiple Values in Declare Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looping Multiple Values in Declare Statement

Author

Topic

markfigel
Starting Member

8 Posts

Posted - 2008-01-24 : 12:43:37

In the update statement below, I would like to place multiple values in the set @VendorID line. I'm sure that this would require some type of looping command but I don't know how to accomplish that.

declare @CompanyID as varchar(5)
declare @VendorID as varchar(15)

set @CompanyID = 'FII'
set @VendorID = '10205'

INSERT INTO ARIVendorCatalog values (@CompanyID, @VendorID)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-24 : 13:06:02

lopping? why do you want variables in INSERT?cant you do

INSERT INTO Table values('FII','10205')
INSERT INTO Table values('FII',nextval)
....

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-01-24 : 13:16:58

Where are you getting te data, IE what would you be looping over?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

markfigel
Starting Member

8 Posts

Posted - 2008-01-24 : 14:17:47

quote:
Originally posted by visakh16

lopping? why do you want variables in INSERT?cant you do

INSERT INTO Table values('FII','10205')
INSERT INTO Table values('FII',nextval)
....

Yes, I could create an update line for each Company/VendorID combination but the total lines would reach 1600.

markfigel
Starting Member

8 Posts

Posted - 2008-01-24 : 14:19:12

quote:
Originally posted by jhocutt

Where are you getting te data, IE what would you be looping over?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Perhaps looping was the wrong term to use. I have a text file with 1600 different VendorID's.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-01-24 : 15:58:57

create table #tmp (
vendorid varchar(15)
)

BULK INSERT #tmp
FROM 'C:\Temp\Vendors.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
-- FIRSTROW=2, --Uncomment this line if ther is a header row.
BATCHSIZE = 10000,
TABLOCK
)

INSERT INTO ARIVendorCatalog
select 'FII', vendorid from #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources