SQL Server - Stored Procedure - populate from another

Question

Hi

I have 2 tables with the exact same structure. Table1 is a template table, Table2 is the CompItemRecord table.

The CompItemRecord table receives 1 datarow for each job item.

My condition is to add a new template record from Table1 INTO Table2 IF a CompItemRecord doesnt exist.

Table Structure - Table1
CREATE TABLE [dbo].[tbl_KLA_CompItemTemplate](
[comp_template_id] [int] IDENTITY(1,1) NOT NULL,
[job_id] [int] NOT NULL,
[item_id] [int] NOT NULL,
[rev_no] [int] NOT NULL,
[Description] varchar NOT NULL,
[PartNumber] varchar NOT NULL,
CONSTRAINT [PK_tbl_KLA_CompItemTemplate] PRIMARY KEY CLUSTERED
(
[comp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Table Structure - Table2
CREATE TABLE [dbo].[tbl_KLA_CompItem](
[comp_id] [int] IDENTITY(1,1) NOT NULL,
[job_id] [int] NOT NULL,
[item_id] [int] NOT NULL,
[rev_no] [int] NOT NULL,
[Description] varchar NOT NULL,
[PartNumber] varchar NOT NULL,
CONSTRAINT [PK_tbl_KLA_CompItem] PRIMARY KEY CLUSTERED
(
[comp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Im currently going through my VB.Net code to get the template row from Table1, store the values in a rangel or properties and sending it to Table2 Stored Procedure INSERT with a condition only to INSERT if the record currently doesnt exist and i do this as follows-

IF NOT EXISTS (SELECT [job_id], [item_id] FROM [dbo].[tbl_Table2] WHERE [job_id] = @job_id AND [item_id] = @item_id)
    BEGIN
    INSERT INTO

Question.
Is it possible to collect the row from Table1 by comp_template_id using a Stored Procedure and adding it to Table2 with the same condition as above without me doing this through the VB.Net class? Am i able to get the datarow and assign the values to the INSERT statement parameter values?

Thanks

Answer 1

Hi @NachitoMax
Since you have 2 tables with the exact same structure.Why not use Insert into ...... Select ......,like this:

 IF NOT EXISTS (SELECT [job_id], [item_id] FROM [dbo].[tbl_Table2] WHERE [job_id] = @job_id AND [item_id] = @item_id)  
   BEGIN  
     INSERT INTO [dbo].[tbl_Table2]   
	 SELECT * FROM [dbo].[tbl_Table1]   
	 WHERE [job_id] = @job_id AND [item_id] = @item_id  
   END  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes, you can use table valued parameter (alternatively in SQL 2016+ you can pass json of your record).

For the table valued parameters you would need to define a type matching your record. Here is an article I used as my prototype when implementing this the very first time

https://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/

BTW, we recently discussed this - for a single row you probably would want to pass all your column values as parameters (or, as I said, may pass a JSON). Table valued parameters are helpful if you want to pass several rows of data.

Answer 3

Hi

Thanks for the reply.

The example looks like its populating the UDT's data with C# locally rather than in SQL Server which is my preference. Seems uneccessary in my case to collect data from SQL Server to the application, then send it straight back through the Stored Procedure.

I think in this instance as it is just a single row, i will collect the fields and process them normally.

Out of curiosity, how would i pass a JSON serverside?

Thanks

Answer 4

Thanks everyone for the replies, some good suggestions.

@LiHong-MSFT @Erland Sommarskog
I have changed my template table to remove the PK and instead, search with a criteria of product type.
My actual table has 89 fields (which was too big to include here) so i truncated it down as an example.
Some testing last night confirmed that i can pass the complete row as LiHong suggested (without the PK) into the desired table.

@Anonymous
I was curious to the JSON suggestion but i believe i would need to extract that data row first outside of SQL server

My objective overall is to pass in the filter criteria from my application but make the condition server side and just return the final result instead of the multiple transactions. I feel that i have a good route now and can put some logic together to accomplish my needs

Thanks everyone :)

Nacho