CODEWITHSUNDEEP
sqlsql-server-2008
JohnZ
JohnZ

Reputation: 482

Insert multiple rows from source

Running SQL Server 2008.

I am making a temp table on each time this query is executed, and trying to find a way how to add multiple lines if there is more then 1 line to enter. I have a receipt information from the customer.

create table #temp (col1 varchar(100),col2 varchar(100),col3 varchar(100))

insert into #temp (col1, col2, col3)values('Store ID', '01', '')
insert into #temp (col1, col2, col3)values('Product','Quantity','Amount')
insert into #temp (col1, col2, col3)values
(
  (select product from receipt where receiptnum = 1),
  (select quantity from receipt where receiptnum = 1),
  (select amount from receipt where receiptnum = 1),
)
select * from #temp

Giving me me output of (this is my whole data structure, but same concept just so I don't flood code): enter image description here

My Question is how can I do it if the receipt has more then 1 product, without knowing ahead. All that is needed for this data to show is receipt number and store number, so I don't know ahead the amount of rows to enter.

From looking around, it seems like I will have to use Cursor, but my knowledge of cursor is none.

Upvotes: 0

Views: 106

Answers (1)

Aaron Webb
Aaron Webb

Reputation: 67

Replacing the third insert statement with the below statement will insert all records into the temp table that were returned in the select.

insert into #temp
select product, quality, amount from receipt where receiptnum = 1

Upvotes: 1

Related Questions