Tag: sql-server-2008-r2

Query to concatenate column values from multiple rows SQL Server 2008 R2

How to concatenate column values from multiple rows Table A FName LName amount Id ——————— A B 100 1 A B 200 2 A C 400 3 A B 300 4 I need the result like : FName LName totalAmount count Id ———————————— A B 600 3 1,2,4 A C 400 1 3 How to […]

Sql Server Pagening with large amount of records

I have this SQL query for pagination: SELECT * FROM ( SELECT T1.*,T2.*, ROW_NUMBER() over(ORDER BY ID DESC) row FROM table1 t1 LEFT JOIN table2 t2 on t1.id = t2.pid ) tbl WHERE row>= @start and row<@end Now the problem is that the select result can be thousands of records, that will be executed for […]

SQL linking by Rank() Over

I have to calculate days when the container is OUT of the facility, each time the container arrives it has different primary key but the same ID. Same container can leave and arrive multiple times but of course I have to count the days between the closest departure and arrival. I’ve been trying to do […]

SQL Server locking on non clustered index

I’m in a big trouble with one database instance of a MS SQL Server 2008 R2! I have the structure bellow that I’ve created it to simulate my real problem. USE [sor] GO ALTER DATABASE [sor] SET ALLOW_SNAPSHOT_ISOLATION on; ALTER DATABASE [sor] SET READ_COMMITTED_SNAPSHOT on; CREATE TABLE [dbo].[test] ( [name] [nvarchar](50) NOT NULL, [id] [int] […]

Looping through different tables of different dates

We have a legacy application which created multiple tables with the following naming convention: table_20140618, table_20140623, etc where the date is when the program run. I am trying to clean up the database now, and drop some of these tables. In each table there are two fields: DateStarted and DateFinished. I want to select the […]

set trustworthy on – is it a bad practice? if so any alternatives?

I have a group of users “readers” that have only dbreader on “mydatabase”. I have a stored procedure “sp” that: 1 – updates data within “mydatabase”, 2 – truncate tables within database “mydatabase” – and this is done using dynamic sql because we don’t know the names of the tables that need to be truncated […]

SQL: Must declare scalar variable

When I run the following T-SQL in SQL Server 2012 it works fine, but in SQL Server 2008 R2 I get the error Must declare the scalar variable “@in”andMust declare the scalar variable “@out” Code: DECLARE @outIds nvarchar(max); DECLARE @in TimeLineReportList; DECLARE @out TimeLineReportList; DECLARE @startDate datetime; DECLARE @endDate datetime; DECLARE @assessmentId int; SET @startDate […]

Unpivot on a column that has dash on it


Condition for select statement using SQL Server 2008 R2

I have a table as shown below: Example: Table: CREATE TABLE testo ( namea VARCHAR(10), nameb VARCHAR(10), details VARCHAR(10) ) With some records: INSERT INTO testo VALUES(‘A’,’C’,’xyz’); INSERT INTO testo VALUES(‘A’,’B’,’oxo’); INSERT INTO testo VALUES(‘B’,’D’,’ner’); INSERT INTO testo VALUES(‘X’,’Y’,’tye’); INSERT INTO testo VALUES(‘Z’,’U’,’txt’); INSERT INTO testo VALUES(‘Y’,’N’,’str’); namea nameb details ——————- A C xyz A […]

Import one csv file with data for multiple tables in MS SQL server

I have the following tables (simplified for question): Data: Id Serialnumber Value AddressId (ForeignKey) Addresses: Id Street ZipCode City … Now I have a csv file which contains data, unfortunately for both tables: Serialnumber,Value,Street,ZipCode,City, … 12345,1140,Fakestreet 3,12345,New York 5678,830,Fakestreet 87,12345,New York Is there any way to import csv files like that to Microsoft SQL server […]

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