bulk insert - SQL Server Forums

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)

bulk insert

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-01 : 11:20:02

Hi,

I want to be able to do an INSERT INTO by doing a SELECT. I want my SELECT statement to retur multiple row using a function that creates a table from a space delimited int list as follows
usp_insert work orders
@UserName
@ID -- '20 25 21'
@Quantity --- '40 50 42 ' @ID and Quantity columns match @ID = 20 has quanity 40 etc.

INSERT INTO [dbo].[myTable]
(ID
,Number]
,[QuantityNeeded]
,[UserName])
SELECT Number
FROM dbo.int_CreateTableFromList(@ID) AS ID,
Number
SELECT Number
FROM dbo.int_CreateTableFromList(@Quantity ) AS QuantityNeeded

sort of thing ..is that possible ? I keep getting the Subquery returned more than 1 value error of course. But how can I get around that other than creating a cursor?
Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-01 : 11:39:39

ok sometimes just throwing the question out there makes it possible to figure it out, theraputic. Here is how I worked it out with CTE

WITH CWorkOrders AS
(
SELECT Number AS WorkOrderID, TableID
FROM dbo.int_CreateTableWithIdentityFromList('20 25 21')
),
CQuantities As
(
SELECT Number AS QuantityNeeded, TableID
FROM dbo.int_CreateTableWithIdentityFromList('40 50 42')
)
SELECT WorkOrderID,
QuantityNeeded
FROM CWorkOrders cs
INNER JOIN CQuantities cq
ON cs.TableID = cq.TableID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources