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;

Import data from Excel to SQL Server using C#.NET

Introduction

In this example  I am going to explain how to import data from Microsoft Excel to SQL Server. And, in the next blog post we will see how to export data from SQL Server back to Microsoft Excel. First, launch Visual studio and create a new project. Give it a name (ie, ImportExcel2SQL).

Create a database in the SQL Server, ExcelDataDB and create a table with the columns that matches to the columns in the Excel file.


CREATE TABLE [dbo].[Books](
[ID] [int] NOT NULL,
[BookName] [varchar](200) NOT NULL,
        [Author] [varchar](200) NOT NULL,
 CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


Drop in a couple of controls from the Toolbox on to the page, FileUpload and a Button. Re-name the Button Text and say "Import".

Default.aspx

<h1>Import data from Excel to SQL server</h1>
<asp:FileUpload ID="FileUpload1" runat="server" />
&nbsp;
<asp:Button ID="Import" runat="server" Text="Import" onclick="Import_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>

Create a temporary folder(eg: TempExcelFiles) in the root of the directory to save the excel file before being imported to the SQL Server. Delete the Excel file in the temporary folder after the data has been imported.

For Microsoft office Excel 2007, please use the following connection string,

string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);

and for other versions of Excel use,

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

Default.aspx.cs

using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;


SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Import_Click(object sender, EventArgs e)
    {
        string excelContentType = "application/vnd.ms-excel";
        string excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string contentType = FileUpload1.PostedFile.ContentType;
        if (FileUpload1.HasFile)
        {
            if (contentType == excelContentType || contentType == excel2010ContentType)
            {
                try
                {
                    string path = string.Concat(Server.MapPath("~/TempExcelFiles/"), FileUpload1.FileName);
                    FileUpload1.SaveAs(path);
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    using (OleDbConnection connection =
                                 new OleDbConnection(excelConnectionString))
                    {
                        OleDbCommand command = new OleDbCommand
                                ("Select * FROM [Sheet1$]", connection);

                        connection.Open();
                        using (DbDataReader dr = command.ExecuteReader())
                        {
                            using (SqlBulkCopy bulkCopy =
                                       new SqlBulkCopy(sqlcon))
                            {
                                bulkCopy.DestinationTableName = "Books";
                                bulkCopy.WriteToServer(dr);
                                Label1.Text = "The data has been exported successfully from Excel to SQL";
                            }
                        }
                    }
                }

                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
            }
        }
    }

Upload images to SQL Server Database using asp.net

There are several ways to upload images from an asp.net application. Uploading images or pictures to the directory is quite simple and easy in asp.net but another approach is to use SQL server database. To save an image file in the database, first create a table with columns to hold the ImageName, ImageSize, Picture. The datatype for the Picture column should be image and for other columns  use nvarchar. The image is saved in the database in binary format. In order to save an image file in the database it should be first converted to byte.

Create a Pictures table in the database Album.

CREATE TABLE [dbo].[Pictures](
[ImageId] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [nvarchar](100) NOT NULL,
[ImageSize] [int] NULL,
[Picture] [image] NULL,
 CONSTRAINT [PK_Pictures] PRIMARY KEY CLUSTERED
(
[ImageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Stored Procedure to save image to the database table Pictures.

CREATE PROCEDURE sprocSaveImage
@ImageName nvarchar(100),
@ImageSize int,
@Picture image
AS
BEGIN
INSERT INTO [Album].[dbo].[Pictures]
           (ImageName
           ,ImageSize
           ,Picture)
     VALUES
           (@ImageName
           ,@ImageSize
           ,@Picture)
END
GO

Default.aspx

<div>
    Image:&nbsp;
    <asp:FileUpload ID="fileImage" runat="server" />
    &nbsp;
    <asp:Button ID="btnUpload" runat="server" Text="Upload"
        onclick="btnUpload_Click" />
</div>

Default.aspx.cs

using System.Data;
using System.Data.SqlClient;

SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Album.MDF;Integrated Security=True;User Instance=True");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
protected void btnUpload_Click(object sender, EventArgs e)
    {
        HttpPostedFile _pfImage = fileImage.PostedFile;
        string imgName = _pfImage.FileName;
        int imgSize = _pfImage.ContentLength;
        byte[] imageOriginal = new byte[imgSize];
        _pfImage.InputStream.Read(imageOriginal, 0, imgSize);

        SaveImageToDatabase(imgName, imgSize, imageOriginal);
    }
    private void SaveImageToDatabase(string imgName, int imgSize, byte[] imageOriginal)
    {
        SqlCommand sqlcmd = new SqlCommand("sprocSaveImage", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.AddWithValue("ImageName", imgName);
        sqlcmd.Parameters.AddWithValue("ImageSize", imgSize);
        sqlcmd.Parameters.AddWithValue("Picture", imageOriginal);
        if (sqlcon.State == ConnectionState.Closed)
        {
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
        }
        if (sqlcon.State == ConnectionState.Open)
        {
            sqlcon.Close();
        }
    }

In the next post I will explain how to retrieve images from SQL server database and bind to gridview.

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