Tag: sql

PHP SQLSRV: passing a string of values to IN clause

I need a way to perform the following query: SELECT * FROM myTable WHERE column NOT IN (‘val1’, ‘val2′) Now, val1 and val2 are inside an array that I’m imploding into a string like this: $inClause = “‘” . implode(“‘,'”, $inClauseArr) . “‘”; If I put the string in the query declaration like this it […]

Update column1 case Date between @Date1 AND @Date2

I want to update one column from condition of date which is another column. DECLARE @FIRSTDATE DATE =’2016-11-01′; DECLARE @LASTDATE DATE =’2016-11-30′; UPDATE A SET A.Status = CASE WHEN B.Date IS NULL THEN 0 ELSE 1 END, A.DESCRIPTION = CASE WHEN B.Date IS NULL THEN ‘UNPAID’ WHEN B.Date BETWEEN @FIRSTDATE AND @LASTDATE THEN ‘CARRY FORWORD’ […]

Why is an SQL update on a table variable slower than with a temp table

I have something like: DECLARE @tbl TABLE ( name varchar(255), type int ) UPDATE c SET c.name = t.name FROM dbo.cars c JOIN @tbl t ON t.type = c.type I have a stored procedure that does something similar but it takes over 20 minutes with the table variable. It runs in less than 2 minutes […]

Bulk insert error, when loading date format

I’m receiving a lot of .csv files that I need to bulk insert into a SQL table. In the csv file the date format is in YYYY-MM-DD and a seperate column for time which is in format HH:MM:SS. “2016-11-24″;”01:00:16” In my table I created the two columns as a date datatype and a time datatype […]

Conversion error in cursor?

I keep getting this error when I run a simple update statement. Msg 16922, Level 16, State 1, Procedure TRG_MEM_BALANCE_AWW, Line 72 Cursor Fetch: Implicit conversion from data type datetime to decimal is not allowed. This is my code that I have for my trigger. I don’t see what is being converted incorrectly? ALTER TRIGGER […]

Looping in SSIS via source tables

we have a DB server that contains multiple instances of the same database. All databases are enumerated (e.g. MyDB001 to MyDB006). All instances have the same tables. For reporting purposes we would like to extract all these DBs to a single DB. Current solution is to have multiple connections in the SSIS – for each […]

MS SQL Server 2014 | “Recursive” delete through a star schema

Good day all, I’ll try and be as succinct as possible. I have a star schema with a fact table in the centre, surrounded by a set of dimension tables. Each dimension table snowflakes into further dimension tables. Pseudo-visual example: Super_FAC >–FK– First_DIM –NK– Snowflake_DIM (>- indicates an n-to-1 relationship, — indicates a 1-to-1 relationship, […]

How to filter temporary table in sql

I have created a stored procedure and store the columns in a temporary table called @Temp. I want to filter the @Temp temporary table now based on the value in column. BEGIN DECLARE @ActivityId INT DECLARE @NM AS DATE DECLARE @Tmp VARCHAR(50) DECLARE @Temp AS TABLE ( MachineName VARCHAR(500) ,ActivityName VARCHAR(500) ,NM DATE ) DECLARE […]

Implementing sql logic for a troublesome requirement

I am working on locations kind of app. So I have a set of records brought down like below: Select Ways.*, ErrorFlag from Ways with(no lock) inner join Locations with(no lock) on Locations.WayId = Ways.WayId In above set I will get all the ways which have locations. Now I have a requirement to show a […]

Duplicate records in a select statement except for one column in complex join

So I have a query that would select data metrics that has long joins and aggregates. Some of the results would return what seems to be a duplicate except for one column, and that is a transaction number. Reason for this is that a separate transaction was made for the exact same record. We can’t […]

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