Tag: sql-server-2008

Is there a way to retrieve inserted identity as well as some values from the query in an INSERT SELECT?

I have a situation in which I need to insert some values from a query into a table that has an identity PK. For some of the records, I need also to insert values in another table which has a 1-to-1 (partial) relationship: CREATE TABLE A ( Id int identity primary key clustered, Somevalue varchar(100), […]

Aggregate function with image data type

What is the best way to perform an aggregate function on an image data type? For example, where binImage is image data type, and I want to return either binImage or null: max( case when somechar = ‘A’ and anotherchar = ‘B’ then binImage else null end ) as result … will not work, because […]

Joining two tables in SQL Server when one table has huge data and other has few data

If I want to join two tables (not inner join), Left table has huge data (millions of record), and right table has few records. What should I prefer (Left or Right Outer join) and why.

How to merge two SQL scripts?

My task is to automate some actions (deleting old rows from a DB), which now are done manually. Currently I have two MSSQL scripts. The first one fetches IDs, I have to work with, as well as a number of rows to delete for every ID. And it looks like that: SELECT e.batchId, b.Name, count […]

Get the value of each XML node and set it to a parameter

How to set a parameter in each XML nodes using SQL Server 2008? I am new with SQL and I have XML data like this Set @xml='<root><name>John </name><age>8</age></root>’ I need to set the value of node <name>, <age> in a parameter. For example: Set @name=’get value of node <name>’ Set @age=’the value of <age>’ Is […]

How can I use an attribute from another table in defining a derived attribute in a new table?

For example: I have a table “Children” which has an attribute “age”. I am now making a table “Students” which inherits only the primary key “SSN” from child, but it should have an attribute “grade” which is derived from the corresponding child’s age as (Children.age – 5). This is something I made up and tried […]

Check if data exists in SQL Server

I’m trying to create a checking from my table. I have three tables hari , transaksi & Outlet. My table hari has only one column called days: days 2016-07-21 00:00:00.000 2016-07-22 00:00:00.000 2016-07-23 00:00:00.000 2016-07-24 00:00:00.000 2016-07-25 00:00:00.000 2016-07-26 00:00:00.000 My second table is transaksi: | outlet | Tanggal | K-MGAS 2016-07-23 K-JGMM2 2016-07-24 K-JGMM2 […]

Double counting in Table

I have a table of Employees, and related table of the Group of Employees The relationship is one to many, when the – id from the table Employee groups can appear many times in the table – Employees I want to know which employee group contains the most Employees. Or, in other words, some of […]

SQL Server stored procedure passing datepart year as a parameter

I was wondering if anyone could help please, I’ve got the below procedure, I’m trying to enable it so that an end user can pass the year portion of the date only and it will query based on that parameter so at the moment I’m querying between ContractDate.ContractDate >= 01/01/2015 AND <= 31/12/2015 but I […]

Find duplicates with different values in T-sql?

I got situation to find duplicates from different sourceport system. for Ex: I got table like below: declare @table table (id int,portnumber int, [sourceport] varchar(50), sourcereportedDate datetime ) insert into @table values (1, 1111, ‘north’ , ‘2016-08-20 09:44:30.847’) insert into @table values (2, 1111, ‘north’ , ‘2016-08-21 09:44:30.847’) insert into @table values (3, 1111, ‘north’ […]

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