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
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
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
OUTPUT inserted.* INTO @insertedRecord
If the above stat... more
Select CASE WHEN loc ='J' THEN 'Y'
WHEN loc NOT IN ('J') THEN 'N'
ELSE 0 END AS loc FROM [prd].[dbo].[GRP]
Select location FROM [prd1].[dbo].[GRPO] WHERE VALUE IN('Y','N');
Conversion failed w... more
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
WHERE index - 1 NOT IN (
SELECT DISTINCT index
index != 1;
When I run the script:
cast(s as float)
ISNUMERIC(s) = 1
it stops with the error:
Error converting data type varchar to float.
Why does it happen? I'm trying to convert to float only numerics. How do I foun... more
Why is Float stored as Real in sys.columns or Information_schema.columns when precision <= 24.
CREATE TABLE dummy
checking the data type
I need to select duplicate values highlighted in green from the Object table show below:
I've tried different combination of the code below. But cannot return the two duplicate rows.
;with CTE as
(Select distinct ID, count([Object ID]) as [Ob... more
As most of you know, SSIS has a hard time reading metadata whenever a stored procedure with temp tables is used as an OleDbSource. Previously, this could be easily prevented by adding SET FMTONLY OFF; before the EXEC statement. The downside of thi... more
I have two databases with same tables, but different data. I want to take all data from the table in the second database and update existing values in the first one. If there is no such entry, I want to insert the new one.
Here is my code (which does... more
I am creating a 14 digit random number in SQL Server 2008 R2 using the following query:
select FLOOR(RAND() * POWER(CAST(10 as BIGINT), 14))
Now I need to convert this number into varchar for which I use this query:
I have a query of which the select-part is really long. I'd like to split this in several pieces, especially because some parts are in there twice or even more often.
What I'd like is something like the following:
Declare @SQLPart as varchar(1000)
Why does this SQL query work even though the SELECT is missing a FROM, which would be a syntax error. Also note that the query only shows the one that satisfies the WHERE clause.
CREATE TABLE Customer_Tbl
I am parsing an xml input, using xQuery which helps parse xml files in a stored procedure like here, and then insert them into a table. I am able to insert the values from XML correctly into the table.
However, I want to insert a newly created UUID ... more
When I run the following two SQL statements (SQL Server 2008 R2), I get different results. The first excludes rows where pernr is NULL; the second includes them.
SELECT pernr FROM tbl
WHERE pernr <> 123;
DECLARE @input int = 123;
SELECT pe... more
I have a table that inserts the status of a sensor every second. It does this by inserting a 0 or 1 into every column in the table. It will do an insert every second regardless. There are currently just 10 or so columns but this will grow to 100 o... more
Is it possible to improve SQL Server 2008 R2 (and newer) insert performance by replacing (say) 50 float columns with a single binary(n) (n being 50 x 4)?
I would presume that using a fixed size binary(n) should improve performance (amount of data i... more
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
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
IF @@ERRO... more
i'm trying to build a query in which I need to apply 2 different where clauses, depending on the value of Current Month. In this case, I need to show data from the last 2 years, only of the months before the current month:
Current Date i... more
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
I have a table in SQL Server 2014 like this:
Now I want to add one more column named "Status" to label each rows for same person.
If Category = 3 and Fee <> 0 then label this row as "Active" and rest of them (1 and 2) would be "non-active";
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
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
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
I've problem that I've been struggling with for hours.
I'm trying to create a special counter on a column from a temporary table, my table has many columns, one of them is col1:
I am not sure if this is even possible but I have not been able to come across clear documentation for this use case. I am using F# 4 and the FSharp.Data.SqlClient library to connect to Sql Server 2016. I am wanting to call a stored procedure that re... more
When trying to search for one book title's number of copies in every branch in my Library Database this works great:
select BranchName, Title, sum(No_Of_Copies) as NumberOfCopies
from Library_Branch lb
join Book_Copies bc
on lb.BranchId ... more
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
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