CODEWITHSUNDEEP
sql-serverstored-proceduresdatatablesyntax-errorsql-insert
Nuke
Nuke

Reputation: 1269

Stored Procedure for insert

I am taking a datatable as parameter in my stored procedure and I am trying to insert a few of its column in one of the database tables but I don't know the correct syntax.

This is what I have tried

ALTER PROCEDURE [dbo].[spInsertInvoice]
    @tblInvoice [TypeExcel] READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT into Invoice(InvoiceNo, InvoiceDate, CustomerName,[Subject], Reference)
    VALUES (SELECT Template, Invoice_No, InvoiceDate, Cust_Name,[Subject], Reference FROM @tblInvoice)
END

Please guide me how can I correctly insert values in my table.

Upvotes: 0

Views: 51

Answers (1)

Richard
Richard

Reputation: 108975

You need the form of insert that takes a select rather than a values:

insert into theTable (col1, col2, ...)
select col1, col2, ...
from ...
where...

All the features of a select are available (column aliases, grouping, joins, sub queries, ...).

Upvotes: 3

Related Questions