Tag: sql-server

Compare a row having comma separated string to a row in another table, sort it and then Insert the row into a third table

I have two tables in MSSQL Server me which looks like below Table 1: A Table which has comma separated values in Col2 ————————————————– | S. No | Col1 | Col2 | Col3 | ————————————————– | 1 | AAAA |A2,A1,C2,B2,C1| X1 | ————————————————– | 2 | BBBB | B2 | X2 | ————————————————– | 3 […]

How to specify that a SQLCLR table valued function is ordered?

I have a sqlclr table valued function that has a prototype like CREATE FUNCTION [dbo].[some_func] (…) RETURNS TABLE ( [order_id] INT NULL, [value1] VARCHAR(50) NULL, [value2] INT NULL ) AS EXTERNAL NAME [some_db].[MyProject].[some_func] The actual function doesn’t matter. Now I know that my function always returns the results in sequential order based on order_id. I’m […]

How to return a respective value of column in a CASE statement in SQL Server

I’m trying to write a query for following.Lets say table A and table B has following columns as defined. TableA(a,b,c,d,e,f,g,h) TableB(a,b,c,d,e,f,g,h,i,j,k) – This has a subset of rows of Table A. declare @tableA table (a int, b int, c int, d int, e int, f int, g int, h int) insert into @tableA values (1,2,3,4,5,5,7,8), […]

Replace character in whole table in SQL Server

I would like ask you how I can replace 1 character in the whole table? I want replace character which is © and change it to Š I found : http://www.sqlteam.com/article/using-replace-in-an-update-statement I read lot of staff but i found code for replacing character only one column in whole table.. I try add names of columns […]

sql greater than statement returns values less than and greater than

I know my issue is that the value is a string but how can I work around it and return the correct results? Here is my query and an excerpt of the results returned select [Count of Disk Errors], _ResourceGuid from Inv_Hard_Disk_Errors where _resourceguid in (select guid from vComputer where IsManaged = ‘1’) and [COUNT […]

Does having a lot of tables degrade performance?

We import a huge amount of data weekly from our clients and append it to an internal table in our SQL Server database. We have a manager who believes it’s easier and more expedient to create (and hopefully, delete) temporary tables for certain reports we run each week from this data. (We do something like […]

Stored Procedure Skipping over Update Statement

I am using an EXE to call a stored procedure a couple of times an hour to move data from one set of tables to another, while also updating a count in another table to show how many times the stored procedure ran. The EXE calls the stored procedure with a certain number to tell […]

SQL Calculating Aggregated Totals-To-Date

Suppose I have a table, my_table, with 2 columns: date, value. And I want to calculate a 3rd column: aggregated_value_year_to_date. Currently I’m doing this: SELECT date, value, (SELECT SUM(value) FROM my_table AS Match WHERE Match.Year = Base.Year AND Match.date < Base.date) AS aggregated_value_year_to_date FROM table AS Base Are there any better options? Particularly is there […]

Update Table records ignoring duplicates

I have a table like so: id | name | xyID —————- 1 | xxx | 100 2 | yyy | 200 3 | zzz | 300 4 | zzz | 200 I need to update all items that are in xyID 200 to now be in xyID = 300 but i have a key […]

COALESCE used in join

MS sql server, table1 comment_no comment 1 excellent 2 ok 3 acceptable table2 name service_comment quality_comment quantity_comment shop1 0 0 1 shop2 0 2 0 shop3 1 0 0 the outcome table will be name service_comment quality_comment quantity_comment comment shop1 0 0 1 Excellent shop2 0 2 0 good shop3 1 0 0 excellent how […]

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