November 26, 2013 at 7:40 am
Hi All,
I have two tables PROFILES & ROLES
CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)
insert into PROFILES values ('rryan','Jamie Fox','jfox@live.com')
insert into PROFILES values ('mclark','Michael Clark','mclark@live.com')
insert into PROFILES values ('djones','Dean Jones','djones@live.com')
insert into PROFILES values ('jfox','Jamie Fox','jfox@live.com')
insert into PROFILES values ('drivers','Doc Rivers','drivers@live.com')
CREATE TABLE ROLES(
USER_ID varchar(20) UNIQUE NOT NULL,
Role char(10) NOT NULL,
Applications char (10) NOT NULL
)
insert into ROLES values ('rryan','M','Consultant')
insert into ROLES values ('mclark','AM','Organizer')
insert into ROLES values ('djones','SM','Admin')
insert into ROLES values ('jfox','M','Consultant')
insert into ROLES values ('drivers','AM','Organizer')
select P.USER_ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLES R
ON P.USER_ID= R.USER_ID
This query joins both the tables and gets displayed in a grid.
select P.ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLE R
ON P.ID= R.ID
I need to write an Insert & update query which should reflect to both the tables. On some conditions they need to be queried, for Insert first the data should be inserted to PROFILE table and then to ROLE table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILE table, then in this case the value should be added to the ROLE table. Single query for insert and delete. Can anyone suggest me on this?
November 26, 2013 at 7:52 am
Not exactly sure what you are trying to do here but I will take a stab at it.
Your insert stored proc needs to have 2 insert statements.
insert Profiles
insert [Role]
I have no idea what you mean by the second part.
For what it is worth I would avoid using reserved words for object names like Role. Also, I would recommend using a more descriptive name than ID for the primary keys in your tables. Something like ProfileID and RoleID would be better names in my opinion. Otherwise your column names start changing when they are foreign keys which will drive you nuts after awhile.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2013 at 8:15 am
Hi Sean,
Got it. I made changes to the table. I am not creating a stored procedure, instead I am trying to write two separate queries for Insert & Delete.
In a single query I need to write it for Insert and in a single query I need to write it for Delete too. The reason for not going to SP is, I am using this query for .net code.
November 26, 2013 at 8:24 am
I'm still unsure what you are try to do here. Why do you need a single statement? Why not use a transaction to control concurrency? Can you state you problem using the data that you have provided? Please provide some sample problems, i.e. if this then that kind of scenarios.
November 26, 2013 at 8:37 am
vigneshlagoons 51204 (11/26/2013)
Hi Sean,Got it. I made changes to the table. I am not creating a stored procedure, instead I am trying to write two separate queries for Insert & Delete.
In a single query I need to write it for Insert and in a single query I need to write it for Delete too. The reason for not going to SP is, I am using this query for .net code.
Not sure I follow your logic about not using a SP because it is in .net code. This is all the more reason you should be using a stored proc.
It is impossible to write a single query that will insert into 2 tables. These types of statements work on one and only table at a time. Now somebody may argue that you could use a trigger but that just means that one of the two queries was moved to the trigger. You would still be executing both of them.
You could use RI to handle the delete but it seems that you are trying to model your data by how you want to execute queries. This is backwards. You model your data structures after the data. Then you create your queries to work with the data model.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2013 at 9:53 am
Yeah that's fine, I'm clear now. So probably how about a stored procedure to perform Insert, Update and Delete operations. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table. Please advice.
November 26, 2013 at 10:21 am
Look at the suggestions made by Sean about the design of your tables. Also, make sure that you are using FKs between the two tables. After that you can simply check to see if the value already exists in the Profiles table. If it doesn't insert into both. If it does then check to see if it is in the Roles table and add it if it doesn't.
One of the things that a lot of systems do is to do an Upsert (Update if exists or Insert if it doesn't) you don't mention if you would like this approach or not. If so then you could use the Merge statement. Although you should be aware of some weird behavior with this statement. Google or Bing "sql.server merge bug" to see some of the issues.
The other question is what should happen if you insert into the Profiles table and then the system crashes before you insert into the Roles table? Should the first insert succeed? or should it rollback?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply