SSIS Parent table Child relation migration

I'm trying to use SSIS to move some data from one SQL server to my Destimation SQL server, the source has a table "Parent" with Identity field ID that is a Foreign key to the "Child" table. 1 - N relation The question is simple, what is the best...
more »

2017-01-13 20:01 (1) Answers

TSQL - Partition BY if in order

I am trying to partition by [Status] after it's sorted by [Status_DateTime] DESC. So only [Status] that are in a row will be partitioned in order to get the earliest Date for it. /****** Object: Table [dbo].[Order_Status] Script Date: 1/13/2017 ...
more »

2017-01-13 20:01 (2) Answers

Different OUTPUT when using MERGE statement?

I'm using MERGE to insert or update records into a table: MERGE INTO target USING SELECT * FROM @source WHEN MATCHED THEN UPDATE SET ...columns... WHEN NOT MATCHED THEN INSERT ...columns... OUTPUT inserted.* INTO @insertedRecord If the above stat...
more »

2017-01-13 18:01 (2) Answers

CASE STATEMENT IN SQL

Select CASE WHEN loc ='J' THEN 'Y' WHEN loc NOT IN ('J') THEN 'N' ELSE 0 END AS loc FROM [prd].[dbo].[GRP] EXCEPT Select location FROM [prd1].[dbo].[GRPO] WHERE VALUE IN('Y','N'); Error: Conversion failed w...
more »

2017-01-13 16:01 (1) Answers

Finding missing numbers in a table

I'd like to generate a query that will produce a list of index values that are missing from a SQL table. So far, what I have is SELECT index - 1 FROM table WHERE index - 1 NOT IN ( SELECT DISTINCT index FROM table) AND index != 1; ...
more »

2017-01-13 15:01 (2) Answers

Reusing query plan in SQL Server 2012

I'm using SQL Server and I want to benefit from reusing query plan. I found this document, but it remains unclear for me whether the plan for my query is being reused or not. declare @su dbo.IntCollection -- TABLE (Value int not null) insert i...
more »

2017-01-11 15:01 (1) Answers

Collate while parsing xml

On same server I have few databases with diferent collation. I'm parsing xml as CREATE TABLE #tblDocuments ( Document varchar(5) COLLATE DATABASE_DEFAULT ,DocCode varchar(15) COLLATE DATABASE_DEFAULT ,Ammount decimal(18,2) ); IF @@ERRO...
more »

2017-01-11 14:01 (1) Answers

Calculate Weighted Average in SQL Server

I am trying to calculate a weighted average based on the following calculations. I have a dataset that looks something like this: item | Date Sent | Date Received 1 | 2 Feb 10am | 3 Feb 10am 1 | 6 Feb 11am | ...
more »

2017-01-11 00:01 (1) Answers

Join pivot table sql

I have two tables that I need to do a join on. One, the headers I need are actually in the fields. After much playing around I figured out this below. I was really excited because it was running, but then to my dismay I realized it only has the heade...
more »

2017-01-10 22:01 (1) Answers

Adding XML Schema Reference in SQL

I can't find for the life of me if it's possible to add a direct reference to the schema in the root of my XML. Here is my code: Declare @Employee table (EmployeeID int,First_Name varchar(50),Last_Name varchar(50)) Insert into @Employee values ...
more »

2017-01-10 22:01 (2) Answers

Loop for extracting attributes of elements XML

I need to extract all attributes from a long list of various elements. Therefore, I'm seeking to build a loop going through all of my elements and return their attributes. Through several posts I have been able to write the code below. However, I ...
more »

2017-01-10 20:01 (2) Answers

generate DDL script from SQL Server database

How to generate DDL (with Foreign keys, indexes, etc.) script of all tables from SQL Server database using SQL (select/stored procedure/etc.)? i need everything except the data. I can't use Sql Server Management Studio because i want to use it in a ...
more »

2017-01-09 22:01 (2) Answers

What is the Logic Working Behind of this Query?

I am running the below query to get 3rd highest salary from employee table and it worked correctly, but I can't understand its logic. How does the sub-query values match the main query(left part). Can someone please explain what is the logic working...
more »

2017-01-09 12:01 (4) Answers