Help with writing the correct values and linked tables

Hoping you can help a nube.

I have TableA with an FK that links to the PK of TableB,

So before insertion, I need to check if the values trying to be entered are already there on TableB, if not add a new one. In either case, afterwards I need to either grab the chosen existing record, or the newly created one and mark the FK of TableA with that ID value.

The reason why I have to do this check is because it doesn't just involve the FK value in relation to the PK on the other table, I have to check whether two separate field values are already on TableB that has the linked PK, not just one field. And the check is if BOTH fields are identical on TableB to what we wish to enter on it, don't add a record, use the existing one, or else add one.

Hoping this is a common and easy question. Thanks.
LVL 9
BobHavertyComhAsked:
Who is Participating?
 
brasso_42Commented:
Hi

The post I put above is TSQL so it would go in your sqlcommand.

You don't have to go down the store procedure route but like you say its recommended practice especially for heavier queries.

I'd have a play round with my sql syntax in sql management studio first, get it right then move over to your c#.  below is the same as above with some more comments, if you cant follow it let me know where you're getting stuck.

--declare a variable for storing our unique ID in (used later on)
declare @myVal int

--to see if something exists use a select query like below, it can be any select statement, if it returns something, anything then exists=true, if nothing is returned then its false.  
 If exists (Select Field from TableA where Field = 845)
--beigin starts the what to do if the above is true, you can do as much or as little as you want in here, we say finished with end
 begin
 --If it does exits do something here
 insert into TableB ......
 end
--else is the other side of the if exists, e.g. if it does not exist
 else
--begin, as above but for the false side
 begin
 --If it doesn't exist
 --Write vales into tableA
 insert into TableA (Filed1,Field2,)
 Select 'blar','Blar'
 --Get the ID of that record inserted above
 Set @myVal = (SELECT SCOPE_IDENTITY())
 --Insert into TableB
 insert into TableB ......
 Select @myVal , 'Blarr', 'Blar'
 end


Play with this in SQL FIRST, if you cant get your head round it there post your problem here with a sample of the code you're trying to get to work.

KR

Brasso
0
 
brasso_42Commented:
Hi

You could do something like this

declare @myVal int

If exists (Select Field from TableA where Field = 845)
begin
--If it does exits do something here
insert into TableB ......
end
else
begin
--If it doesn't exist
--Writ vales into tableA
insert into TableA (Filed1,Field2,)
Select 'blar','Blar'
--Get the ID of that record inserted above
Set @myVal = (SELECT SCOPE_IDENTITY())
--Insert into TableB
insert into TableB ......
Select @myVal , 'Blarr', 'Blar'
end


Hope this helps
0
 
BobHavertyComhAuthor Commented:
Hi brasso. Thanks for answering. I don't really know how to implement your answer. I'm really more of a C# person, if anything, than an SQL person, so I didn't fully understand what you did.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Bob.

If you don't know how to implement the solution above how are you doing with your inserts and updates in C#?
0
 
BobHavertyComhAuthor Commented:
Hi Vitor, I'm sorry I didn't see this, I thought the question was dead

Hi Bob.
If you don't know how to implement the solution above how are you doing with your inserts and updates in C#?

Ex.

try {                   
                        SqlCommand myInsert = new SqlCommand("INSERT INTO Temp (PhoneNumber, Address, DollarAmt, Created, CreatedBy, DispatcherID) Values ('" + phone + "', '" + address + "', '" + dollars + "', '" +  DateTime.Now + "', '" + userID + "', '" + companyID + "')", myConnection);
                        myInsert.ExecuteNonQuery();
                    }

Open in new window


I do not know what to do with the BEGIN and GO statements in what he posted. I've never used those in SQL statements. Can I actually just put those in the SqlCommand string? He never explained. I have to have a higher level of understanding to understand what he posted, and if I did, I probably wouldn't need to even ask here.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see.
You can create a stored procedure in SQL Server and the execute from C#:
try {                   
                   SqlCommand mySPExec = new SqlCommand("myStoredProcedureName"', myConnection);
--------------- In the case your SP have parameters -----------------
                   mySPExec.Parameters.Add("@UserId", SqlDbType.Int).Value = UserId;
                   mySPExec.Parameters.Add("@CompanyId", SqlDbType.Int).Value = CompanyId;
------------------------------ End parameters ----------------------------
                   mySPExec.ExecuteNonQuery();
                    }

Open in new window

You may need to learn how to create a stored procedure in SQL Server. MSDN have an help on SP creation (see the examples in the end of the article).
0
 
BobHavertyComhAuthor Commented:
Hi Vitor. Yes, eventually I'm going to check everything on my site to see if it is worthy of being a stored procedure, and I intend to do that all at once, after I learn more about it. But I know that it is correct to convert to stored procedures whenever possible for the best possible performance. I appreciate the link. For right now, I'm just trying to get things up and running just to show an idea to someone. Does it have to be a stored procedure right now? And I still don't understand the BEGIN and GO statements and how they fit into C# code, so that's why I didn't really know what to do with his answer.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can't see any BEGIN and GO statements but BEGIN goes with END and define blocks to be threated as one. For example, in T-SQL after a WHILE or a IF the engine will only understand the next line as the one to be executed in the scope of the loop or condition so if you want the engine to process more than one line you need to put all the code in a block and for that you'll use BEGIN..END:
DECLARE @Cont INT
SET @Cont=1
WHILE @Cont < 10
     PRINT @Cont '--> Infinite loop since WHILE will only process this line
     SET @Cont = @Cont+1 '--> never reached since will continue loop on the previous line

######## With BEGIN..END #####
'   This will work properly 
DECLARE @Cont INT
SET @Cont=1
WHILE @Cont < 10
BEGIN
     PRINT @Cont
     SET @Cont = @Cont+1
END

Open in new window


The GO it's a batch separator. You shouldn't need it but for you understand what is:
DECLARE @Cont INT
SET @Cont=1
WHILE @Cont < 10
BEGIN
     PRINT @Cont 
     SET @Cont = @Cont+1
END
GO '--> Batch terminator

PRINT @Cont  '--> Will return an error since it won't recognize @Cont because was defined in previous batch

Open in new window

0
 
BobHavertyComhAuthor Commented:
Hi brasso, it looks like this will actually work correctly and is a very concise and elegant SQL statement, now that I am not confused by BEGIN and GO. I can see the logic. Let me figure out how to put my real values into this and try it in SQL mgr as you suggested.  Can't do that right this moment but will do so soon. I appreciate your expertise. Although your answer is a little above me, I'm sure it's actually the best approach to do it, and that's what I want in the long run.
0
 
brasso_42Commented:
if you get stuck feel free to post your code and I'll try to figure out where you're going wrong.
0
 
BobHavertyComhAuthor Commented:
I got the code suggested to work with my real values added. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.