Tag: sql-server-2008

compare comma separated values in sql

I am trying to write a function to compare comma separated values in SQL I’ve taken some code from Internet : SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Split(@v1) WHERE ‘, ‘ + LTRIM(@v2) + ‘,’ LIKE ‘%, ‘ + LTRIM(Item) + ‘,%’ ) THEN 1 ELSE 0 END; Then I make a function […]

Copy identity and put it to another table

Good day! I’m having trouble with my stored procedure, here is my code: INSERT INTO dbo.CS_RefQuestionChoice(ChoiceDescription, QuestionID, RatingID, SetID) SELECT @choice, @@IDENTITY,@ratingid,1 FROM CS_RefQuestions The code is running smoothly, but I’m having trouble because it saves 75 records on my database, it should save only one, all I want this to execute is when I […]

SQL: Avoid duplicate data

Table1: Person_ID Name Salary_Revisions 1 Test1 100 1 Test1 200 2 Test2 300 2 Test2 400 Table2: Person ID Department ————————– —————- 1 Physics 1 Chemistry 2 Maths I would like to get the result like: Person_ID Name Salary_Revisions Department ——————— —————— ———————- ————– 1 Test1 100 Physics 1 Test1 200 Chemistry 2 Test2 300 […]

Trigger and stored procedure security in SQL Server

I have a database (SQL Server) that is being used by 20 users, all members of the same security role. The role enables them to insert, delete and update to Table1, but they have no permissions for Table2. Table1 has a trigger that fires a stored procedure, Table2_Refresh, that truncates Table2 and rebuilds it from […]

Listing New Customers Per Month

This is kind of a part two to a previous question I asked and already got an answer for. There I had wanted a count of all new customers per month, per year. Now I want to actually see a list of who is new, by email address (but I don’t really understand the code […]

Calculate Average after populating a temp table

I have been tasked with figuring out the average length of time that our customers stick with us. (Specifically from the date they become a customer, to when they placed their last order.) I am not 100% sure that I am doing this properly, but my thought was to gather the date we enter the […]

Trying to connect to SQL Server 2008 Express database with SQL Server authentication always result in “Login failed for user '…'.”

I am using jTDS to connect a Liferay instance to an SQL Server 2008 Express server using the SQL Server authentication (instead of the Windows’ auth method). I have something like this in my portal-ext.properties: jdbc.default.driverClassName=net.sourceforge.jtds.jdbc.Driver jdbc.default.url=jdbc:jtds:sqlserver:// jdbc.default.username=someuser jdbc.default.password=somepassword (For those that do not know Liferay, it is somewhat alike to call Class.forName(“net.sourceforge.jtds.jdbc.Driver”); con = […]

Within the same group find and exclude records that have the same parent ID for certain types

I have a table like following: GroupID ParentID Type 1 ABC IND 1 ABC IND 1 CDE ORD 1 EFG STD 2 ZZZ IND 2 ZZZ IND 2 ZZZ IND 3 YYY COR 3 YYY COR I need to exclude those records that are in the same group, having the same parent ID and the […]

Insert Records that are not exists in Target Table and delete records that are not exists in source dataset Using MERGE

I have a table With data like below (Table 1) id valueId Value ———– ———– ————————————————– 1 1 Value 1 1 1 Value 2 1 1 Value 3 1 2 Value 1 1 2 Value 2 1 2 Value 3 And i have another dataset like below (DataSet) id valueId Value ———– ———– ——- 1 […]

How do I merge data from two tables into one using Sql Server 2008?

I’m using a SQL merge and trying to merge from two tables into a single table. Basically, there are three tables named: t1,t2,t3. What I’m attempting to do is to get the t2 table data and t3 table data into the t1 table using merge. Here is the code: MERGE daily_so_invoice AS target1 USING temp_invoice […]

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