Access insert multiple rows into SSMS

Question

I have a table in access and I am trying to insert all the rows into a table in SSMS.

This is the data that I have in the Access form. I have it working in Access where it inserts the first row.

PONumber  RollNumber    ItemNumber
123       A20           COFFEESLV.2
123       A21           COFFEESLV.2
123       A23           COFFEESLV.2

Access calls a stored procedure and

INSERT INTO Receipts 
(   
      PONumber
    , ItemNumber
    , RollNumber
)
Values
(   
    @PONumber   --PONumber  (These get passed in from the Access Form.(
    ,@Item      --ItemNumber
    ,'1'        --RollNumber (I'm using this for testing. It works. But now it 
                 needs to insert all of them. There could be up to 100.
)

How do I get it to insert all the rows in the table? The table itself is also in SSMS. It is just a table that holds the values of the rolls of coffee sleeves until we receipt them, and then the values get deleted.

ETA - I think my problem is that the Temp table only contains the Roll Numbers. The form contains fields for the rest of the information. So I can't do a simple insert into select from the two tables right in SSMS. I don't know how to get the Temp table to grab the fields from the subform (I think it's a subform, but its not connected to anything. I tried to get that to work but could not.)

Thank you.

Why don't you use a bound form? Then all your date will be stored in a table and you can transfer your data to SSMS. — Rene
As @Rene suggets, enter data to table then perhaps INSERT SELECT will satisfy requirement: w3schools.com/SQL/sql_insert_into_select.asp. However, instead of query object, use VBA to construct the INSERT SELECT and concatenate references to form controls. Then run the SQL with CurrentDb.Execute. — June7
Just noticed your query is a stored procedure. Since I've never used, can only guess that the INSERT SELECT could be structured in the procedure. — June7

Unknown Unknown · Accepted Answer · 2017-12-18T21:40:31

Thank you.

I did an insert into select in the procedure. I passed in the values that I needed from the form. On the form there was a subform attached to an SSMS table.

So my insert looked like

Insert Into MyTable (Number, Color, Size, Price)
Select Number, Color, Size, @Price
From SubfrmTable

You can just throw in a passed down variable right in the insert into select (@Price). I didn't know that. Hopefully this will help others.

Access insert multiple rows into SSMS

1 Answers