Insert data when not exists

I have that diagram. UserId for both table are same (take it as foreign key). How can which UserId doesn’t exists in UserAdditioStamp table and then insert these data in UserAdditioStamp table. SELECT min(ev.UserId) as UserId, ev.Value, min(uas.UserId) as AdditionUserId, max(uas.[TimeStamp]) as AdditionTimestamp, count(ev.UserId) as [Registrations] FROM EventLog ev LEFT JOIN UserAdditionStamp uas ON ev.UserId […]

C# how do i direct users to different forms based on their role

I’ve connected a SQL database called Data to a windows form application in visual studio C#, the database contains one table called Login. The table displays like this: Here is image link to how data looks inside my table, so basically I will have Username, Password and Role (I have two types of roles which […]

How do I get the table names referenced by a trigger in sql server?

I have a trigger with me and I want that which tables are being referenced by this trigger.Also same for Rules in sql server.I have a Rule in my db and I want the table name on which it is created. Also same for Indexes. =========================EDIT=================== In case of following trigger Definition: CREATE TRIGGER [dbo].[trgAfterInsert] […]

Sql Server Group by subquery

I have a database of sales transactions that have sales of multiple items identified by a unique ‘sale number’ in the salenum field. Some sales are taxable and are identified by a code field with the value ‘T’. The non taxed sale just omits the ‘T’ value to indicate it is a non taxed transaction. […]

Combining two queries to add additional column

I have two SQL queries that I want to combine into one. The 2nd should add an additional column to the 1st. Query #1: SELECT T3.AcctCode, T3.AcctName, SUM(CASE WHEN T0.TaxDate BETWEEN ‘2015-12-01’ AND ‘2015-12-31’ THEN T1.Debit – T1.Credit ELSE 0 END)* -1 AS [Balance], SUM(T1.Debit – T1.Credit)* -1 AS [YTD Balance], (CASE WHEN T3.AcctCode BETWEEN […]

How to avoid duplicate primary key in Entity Framework manual Insert?

Typically in the past before we used entity framework we used composite keys for every table and manually iterated the key. For a simple example, basically the parent tables in this case is vendor and log type, whose fk is the pk of the log table +————–+————-+ | VendorID(PK) | Name | +————–+————-+ | 1 […]

Subtract variable from column while variable has remaining value

I have a column with different numerical values. And I have a numeric variable called X with a value of 50000. rownumber col1 ————————– 1 5000 2 1000 3 10000 4 12000 5 300 6 35000 DECLARE @X AS decimal(18,2) = 50000 I need somehow cover the value from col1 to zero while the variable […]

How can i find which table is updated or affected by website action for a large my sql database

How can i find which table is updated or affected by website action for a large database. Is there any software available or any other way? How can we check which table record is updated/deleted/inserted, If we do not have any knowledge about flow of database table. Like if we complete check out process and […]

XML node values where xml data has colon syntax

I’m trying to move data from an xml column to a normalized database table and I can’t figure out how to read the xml nodes. Can someone provide some help. I haven’t seen any examples where the colon : syntax is used. DECLARE @xmldata XML; SET @xmldata='<storename:results xmlns:storename=”http://www.storename.com/soap/rpc” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=”http://www.storename.com/soap/rpc results.xsd “> <storename:testCode>11</storename:testCode> <storename:drawNumber>2286</storename:drawNumber> <storename:resultset> […]

SQL Result sets in stored procedures

I currently have 2 stored procedures that I’m trying to combine into 1. The first SP should only ever return 1 record from my DB. The 2nd SP returns many records(31k+). Currently I’m just inserting my data into a temp table with a query like: insert @Results3 select r2.d_key select r1.name select r1.comments select r1.time […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.