For Each Row in Table A - Append rows to Table B

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 2012 Forums

Transact-SQL (2012)

For Each Row in Table A - Append rows to Table B

Author

Topic

jgoodson
Starting Member

3 Posts

Posted - 2015-02-25 : 17:21:44

I have a table that holds customers

CustID, CustName
1, John
2, Sally
3, William

And a FUNCTION that accepts a single CustID and returns the productIDs purchased by that customer. For example

select productID from dbo.uspGetAllProductsUserHasBought(3)

SKU003
SKU004
SKU016

--products purchased by William

I want to build a new table that looks like the following:

CustomerID, Project
3, SKU003
3, SKU004
3, SKU016
2, SKU0132
2, SKU016k33
2, SKU01344
1, SKU016k99
1, SKU016003k
1, SKU01600dd3k

In other words, for each row in the customer table pass in the customerID to the function and write out the results to a new table.

In the actual production system the tables have millions of rows and the procedure will run off-hours.

Thanks in advance.

John Goodson

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-26 : 03:30:05

insert into newtable
select custid, pro.ject
from customers
cross apply func(custid) as pro(ject)

jgoodson
Starting Member

3 Posts

Posted - 2015-02-26 : 06:51:05

Gonna Answer my own question...

CREATE TABLE #CustProducts
(
[CustID] VARCHAR(255) NULL,
[ProdID] VARCHAR(255) NULL,
);
GO

Insert into #CustProducts
( [CustID], [ProdID] )

select c.custID, p.[ProdID] from Customers c
outer apply
(SELECT ProdID
from dbo.uspGetAllProductsUserHasBought(r.CustID)) p

John Goodson

jgoodson
Starting Member

3 Posts

Posted - 2015-02-26 : 06:55:56

quote:
Originally posted by gbritton

insert into newtable
select custid, pro.ject
from customers
cross apply func(custid) as pro(ject)

Thank you for this answer...

John Goodson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources