Databases
StackOverflow2013
Postsdbo
POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
    primarykey
    data
    text
    <p>I'm fairly new to SQL Server any input and advice would help greatly.</p> <p>I have 3 tables which are in one-to-many relationships.</p> <p>Table <code>Person</code> holds customer info</p> <pre><code>CREATE TABLE [dbo].[Person]( [PID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](255) NULL, [LastName] [varchar](255) NULL, [CAddress] [varchar](255) NULL, [Ccity] [varchar](255) NULL, [Cstate] [varchar](2) NULL, [Czipcode] [varchar](20) NULL, [Ccountry] [varchar](255) NULL, [Cphone] [varchar](25) NULL, [Cemail] [varchar](255) NULL, [CipAddress] [varchar](255) NULL) </code></pre> <p>Table <code>Transaction</code> holds their transaction</p> <pre><code>CREATE TABLE [dbo].[Transaction]( [TID] [int] IDENTITY(1,1) NOT NULL, [PID] [int] NOT NULL, [DateOfTransaction] [date] NULL) </code></pre> <p>with a third table, <code>TransactionDetail</code>, which holds transaction details</p> <pre><code>CREATE TABLE [dbo].[TransactionDetail]( [TDID] [int] IDENTITY(1,1) NOT NULL, [TID] [int] NULL, [ProductID] [int] NULL, [ProductName] [varchar](255) NULL, [ProductQTY] [int] NULL, [ProductPrice] [decimal](18, 2) NULL) </code></pre> <p>I would like to create a stored procedure to insert once into the <code>Person</code> table then insert multiple details into the third table.</p> <p>this is what i got i'm not sure if this is correct?</p> <pre><code>CREATE TYPE dbo.TransactionTableType AS TABLE ( TID int, ProductID int, ProductName varchar(255), ProductQTY int, ProductPrice decimal(18,2) ) go CREATE PROCEDURE insertTransacion @NewProduct dbo.TransactionTableType READONLY, @FirstName varchar(255), @LastName varchar(255), @CAddress varchar(255), @Ccity varchar(255), @Cstate varchar(2), @Czipcode varchar(20), @Ccountry varchar(255), @CPhone varchar(25), @Cemail varchar(255), @CipAddress varchar(255), @DateOfTrans date as begin SET NOCOUNT ON; DECLARE @Pid int insert into Person(FirstName,LastName,CAddress,Ccity,Cstate,Czipcode,Ccountry,Cphone,Cemail,CipAddress) values (@FirstName,@LastName,@CAddress,@Ccity,@Cstate,@Czipcode,@Ccountry,@CPhone,@Cemail,@CipAddress) SET @Pid = SCOPE_IDENTITY() insert into PTransactions(PID, DateOfTransaction) values (@Pid, @DateOfTrans) DECLARE @Tid int SET @Tid = SCOPE_IDENTITY() insert into TransactionDetail(TID, ProductID, ProductName, ProductQTY, ProductPrice) Select @Tid, ntd.ProductID, ntd.ProductName, ntd.ProductQTY, ntd.ProductPrice from @NewProduct as ntd end </code></pre> <p>Not sure how to do this in a stored procedure I know how to do it programmatically in asp.net using ado, however I'm trying to avoid that. Sorry for the grammar.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNeo
    1. USNeo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJust a comment, normalize before going any further. The transaction table is pretty much a Person table with a TransactionId and IPAddress. Also, no need to store ProductName in TransactionDetail, this should be in a Product table. http://en.wikipedia.org/wiki/Database_normalization
      singulars
      1. POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
      1. USbrian
    2. COIs it valid to have a TransactionDetails record with a NULL TID?
      singulars
      1. POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
      1. USMike Parkhill
    3. COi could make it not null normalizing the tables thank brian will normalize.
      singulars
      1. POHow do I create a stored procedure that takes a list of products or a datatable as a param, then insert into a table?
      1. USNeo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload