Move column data from one table to another

There may be situations when you want to move MYSQL column data from one table to another. 

MySQL Query: 

UPDATE accounts_users, accounts_membership  SET accounts_users.username=accounts_membership.username WHERE accounts_users.user_id=accounts_membership.user_id AND accounts_users.user_id >= 1;

Retrieve duplicate column values in MySQL

How to find duplicate column values in MySQL

Let's see some of the many ways to retrieve duplicate column value from MySQL table. In this example, we are going to find and return duplicate contact names from the contacts table. It is up to you to decide which is applicable according to the scenario.

Approach 1:

SELECT customer_name FROM contacts group by customer_name having count(*) > 1;

OR

SELECT 

    customer_name, 

    COUNT(customer_name)

FROM

    contacts

GROUP BY customer_name

HAVING COUNT(customer_name) > 1;

Result:

Approach 2:

SELECT contact_id, 

   contact_type, 

   contacts.customer_name 

FROM contacts

   INNER JOIN (SELECT customer_name

               FROM   contacts

               GROUP  BY customer_name

               HAVING COUNT(contact_id) > 1) dup

           ON contacts.customer_name = dup.customer_name;

Result:

Sum columns in multiple tables - MySQL

How to sum columns in multiple tables in MySQL

We will see two methods to sum columns from multiple tables. The first method is more preferred than the second method. Therefore consider using first approach.

METHOD 1:

SELECT credit.contact_id, (IFNULL(credit, 0) - IFNULL(debit, 0)) as balance
     FROM (
    (SELECT SUM(credit) as credit, contact_id FROM table_a GROUP BY contact_id) as credit
       LEFT JOIN
    (SELECT SUM(debit) as debit, contact_id FROM table_b GROUP BY contact_id) as debit
     ON debit.contact_id = credit.contact_id);

METHOD 2:

SELECT contact_id, SUM(balance) FROM
    (SELECT contact_id ,credit as balance FROM table_a
       UNION ALL
    SELECT contact_id,- debit as balance FROM table_b
   ) as x
  GROUP BY contact_id;

Retrieve images from SQL Server database and bind to Gridview

In the previous blog post I have explained how to upload images to SQL Server Database. In this post I am going to show you how you can retrieve images stored in SQL Server Database and bind it to gridview. I am going to handler to bind image in the gridview. Start by adding a handler at the root directory of your project and call it as ShowPhoto.ashx. We are going to use the same table that we used to store images in the database in the previous post. Additionally, a couple of stored procedures are needed to bind images to gridview. Refer previous post for tables

Stored procedure to get picture by Id.

CREATE PROCEDURE [dbo].[sprocGetPictureById]
@ImageId int
AS
BEGIN
SELECT [Picture]
  FROM [Pictures] WHERE ImageId = @ImageId
END

Stored procedure to get all pictures.

CREATE PROCEDURE [dbo].[sprocGetPictures]

AS
BEGIN
SELECT [ImageId]
      ,[ImageName]
      ,[ImageSize]
  FROM [Pictures]
END

ShowPhoto.ashx

<%@ WebHandler Language="C#" Class="ShowPhoto" %>

using System;
using System.Web;
using System.IO;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class ShowPhoto : IHttpHandler
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    object img;
    public void ProcessRequest(HttpContext context)
    {
        int imageId, newWidth = 100, newHeight = 100;
        double scale = 0;
        if (context.Request.QueryString["id"] != null)
            imageId = Convert.ToInt32(context.Request.QueryString["id"]);
        else
            throw new ArgumentException("No parameter specified");

        //Code to Read Thumbnail Image from the database
        System.Drawing.Image image = System.Drawing.Image.FromStream(ShowPicture(imageId));
        if (image.Height < image.Width)
        {
            scale = ((double)newHeight) / image.Width;
        }
        else
        {
            scale = ((double)newWidth) / image.Height;
        }

        // Set new width and height.
        newWidth = (int)(scale * image.Width);
        newHeight = (int)(scale * image.Height);

        // create a new image from original
        System.Drawing.Image thumbnail = new Bitmap(image, newWidth, newHeight);
        System.Drawing.Graphics graphic = System.Drawing.Graphics.FromImage(thumbnail);

        graphic.InterpolationMode = InterpolationMode.HighQualityBicubic;
        graphic.SmoothingMode = SmoothingMode.HighQuality;
        graphic.PixelOffsetMode = PixelOffsetMode.HighQuality;
        graphic.CompositingQuality = CompositingQuality.HighQuality;

        graphic.DrawImage(image, 0, 0, newWidth, newHeight);

        ImageCodecInfo[] info;
        info = ImageCodecInfo.GetImageEncoders();
        EncoderParameters encoderParameters;
        encoderParameters = new EncoderParameters(1);
        encoderParameters.Param[0] = new EncoderParameter(Encoder.Quality, 100L);
        context.Response.ContentType = "image/jpeg";
        thumbnail.Save(context.Response.OutputStream, info[1], encoderParameters);
    }

    public Stream ShowPicture(int imageId)
    {
        SqlCommand cmd = new SqlCommand("sprocGetPictureById", sqlcon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ImageId", imageId);
        sqlcon.Open();
        try
        {
            img = cmd.ExecuteScalar();
            return new MemoryStream((byte[])img);
        }
        catch
        {
            return null;
        }
        finally
        {
            sqlcon.Close();
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}

Next, right-click the root of your project and click Add new item -> Web Form and give it a name (in our case RetrieveImage.aspx)

RetrieveImage.aspx

<div>
    <asp:GridView ID="gvPictures" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ImageId" HeaderText="ID" />
            <asp:BoundField DataField="ImageName" HeaderText="Image Name" />
            <asp:BoundField DataField="ImageSize" HeaderText="Image Size(Kb)" />
            <asp:TemplateField HeaderText="Picture">
                <ItemTemplate>
                    <img id="Img1" alt='<%#Eval("ImageName") %>' runat="server" src='<%# "~/ShowPhoto.ashx?id=" + Eval("ImageId") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>

RetrieveImage.aspx.cs

SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            gvPictures.DataSource = BindPictures();
            gvPictures.DataBind();
        }
    }


private DataTable BindPictures()
    {
        DataTable dtPictures = new DataTable();
        SqlDataAdapter adpPictures = new SqlDataAdapter("sprocGetPictures", sqlcon);
        adpPictures.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpPictures.Fill(dtPictures);
            return dtPictures;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Pivot Table - Example #2

CREATE TABLE #tempRecords
(
Record int,
BookNo int,
BookValue varchar(50)
)

INSERT INTO #tempRecords VALUES (1,1,'One')
INSERT INTO #tempRecords VALUES (1,2,'Two')
INSERT INTO #tempRecords VALUES (1,3,'Three')
INSERT INTO #tempRecords VALUES (1,4,'Four')

INSERT INTO #tempRecords VALUES (2,1,'One')
INSERT INTO #tempRecords VALUES (2,2,'Two')
INSERT INTO #tempRecords VALUES (2,3,'Three')
INSERT INTO #tempRecords VALUES (2,7,'Seven')
INSERT INTO #tempRecords VALUES (2,8,'Eight')

INSERT INTO #tempRecords VALUES (3,9,'Nine')

INSERT INTO #tempRecords VALUES (4,2,'Two')
INSERT INTO #tempRecords VALUES (4,5,'Five')
INSERT INTO #tempRecords VALUES (4,10,'Ten')

/** Create crosstab using PIVOT **/
SELECT *
FROM #tempRecords
PIVOT
(
MAX(BookValue)
FOR [BookNo]
IN ([1],[2],[3],[4],[5],[6],[7],[8])
)
AS p

DROP TABLE #tempRecords

Pivot Table - Example #1

CREATE TABLE #tempProducts
(
Supplier varchar(15),
Product varchar(20),
Product_Quantity integer
)
INSERT INTO #tempProducts VALUES ('HP','Software',300000)
INSERT INTO #tempProducts VALUES ('HP','Hardware',150000)
INSERT INTO #tempProducts VALUES ('HP','Monitor',250000)
INSERT INTO #tempProducts VALUES ('HP','Keyboard',500000)
INSERT INTO #tempProducts VALUES ('Apple','Software',20000)
INSERT INTO #tempProducts VALUES ('Apple','Hardware',125000)
INSERT INTO #tempProducts VALUES ('Apple','Monitor',800000)
INSERT INTO #tempProducts VALUES ('Apple','Keyboard',60000)
INSERT INTO #tempProducts VALUES ('Dell','Software',120000)
INSERT INTO #tempProducts VALUES ('Dell','Hardware',250000)
INSERT INTO #tempProducts VALUES ('Sony','Software',900000)
INSERT INTO #tempProducts VALUES ('Sony','Hardware',450000)
INSERT INTO #tempProducts VALUES ('Sony','Monitor',100000)
/** Create crosstab using PIVOT **/
SELECT *
FROM #tempProducts
PIVOT
(
SUM(Product_Quantity)
FOR [Product]
IN ([Software],[Hardware],[Monitor],[Keyboard])
)
AS p
DROP TABLE #tempProducts