SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Following three questions are many time asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO


SQL SERVER – 2005 – 2008 – Delete Duplicate Rows

I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.

This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.

/* Create Table with 7 entries - 3 are duplicate entries */

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)

INSERT INTO DuplicateRcordTable

SELECT 1, 1

UNION ALL

SELECT 1, 1 --duplicate

UNION ALL

SELECT 1, 1 --duplicate

UNION ALL

SELECT 1, 2

UNION ALL

SELECT 1, 2 --duplicate

UNION ALL

SELECT 1, 3

UNION ALL

SELECT 1, 4

GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */

SELECT *

FROM DuplicateRcordTable

GO

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created  DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */

WITH CTE (COl1,Col2, DuplicateCount)

AS

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */

SELECT *

FROM DuplicateRcordTable

GO

This method is a breeze and we can use this for SQL Server version 2005 and the later versions.

Insert Multiple Records Using One Insert Statement – Use of UNION ALL

Update: For SQL Server 2008 there is even better method of Row Construction, please read it here : SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor

This is very interesting question I have received from new developer. How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Second’,2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Third’,3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Fourth’,4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Fifth’,5);
GO
The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. He was quite pleased.

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

The effective result is same.

Export SQL Data to Excel Sheet

First Run This Procedure

 EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

GO

RECONFIGURE;

GO

Create Excel sheet with two column

Column 1 is : FirstName

Column 2 is : LastName

Save Excel File as contact.xls

Create On Data Table with same column name

FirstName,LastName and save table as Contact

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,

‘SELECT * FROM [Sheet1$]‘)

SELECT TOP 5 FirstName, LastName

FROM Contact

GO

Dynamic Falsh File in C#

User Control Code : ASCX File

<%@ control language=”C#” autoeventwireup=”true” inherits=”ucFlash” CodeFile=”ucFlash.ascx.cs” %>
<asp:Label ID=”lblMessage” runat=”server” Text=”Fail puudub”></asp:Label>
<asp:Panel runat=”server” ID=”pnlFlash” Visible=”false”>
<OBJECT <%# WIDTH %> <%# HEIGHT %> classid=”clsid:D27CDB6E-AE6D-11cf-96B8-444553540000″
codebase=”http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,40,0″>
<param name=”src” value=’<%# SRC %>’>
<param name=”CONTROLLER” value=”False”>
<param name=”LOOP” value=”True”>
<param name=”PLAY” value=”True”>
<param name=”quality” value=”high”>
<param name=”allowScriptAccess” value=”never”>
<embed src=’<%# SRC %>’ <%# WIDTH %> <%# HEIGHT %> type=”application/x-shockwave-flash” pluginspage=”http://www.macromedia.com/go/getflashplayer”></embed>
</OBJECT>
</asp:Panel>

C# Code :

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partial class ucFlash : System.Web.UI.UserControl
{
public string SRC = “”;
public string WIDTH = “”;
public string HEIGHT = “”;

/// <summary>
/// Flashi objekti nähtavaks tegemine ning source lehe määramine
/// </summary>
/// <param name=”src”></param>
public void InitForm(string src)
{
InitForm(src,800,600);
}

public void InitForm(string src,int width, int height)
{
SRC = src;
if (width > 0)
WIDTH = “width=’”+width.ToString()+”‘”;
if (height > 0)
HEIGHT = “height=’”+height.ToString()+”‘”;
this.DataBind();
lblMessage.Visible = false;
pnlFlash.Visible = true;
}

}

Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

create table

USE Northwind
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

BULK
INSERT CSVTest
FROM ‘c:\Test1.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
–Check the content of the table.
SELECT *
FROM CSVTest
GO
–Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

Image upload in FCKEditor

1. Add key in web.config

<add key=”FCKeditor:UserFilesPath” value=”~/userfiles/”/>

2. ContentEditor(FCkeditor) ->editor->filemanager-> connectors-> aspx-> config.ascx->

Change : UserFilesPath = “/userfiles/”;

UserFilesAbsolutePath = “”;

3. Create Folder in root directory with name userfiles (wher FCKEditor is there)

LINQ Gridview Operation

Good links to learn LINQ for beginner

http://www.codeproject.com/KB/linq/LINQNewbie.aspx

http://www.codeproject.com/KB/linq/LINQquery.aspx

http://www.codeproject.com/KB/linq/LINQFAQPart2.aspx

Get Data using Stored procedure in gridview

var Emp = db.GetEmployeeByID(10);

GridView1.DataSource = Emp;

GridView1.DataBind();

foreach (var item in Emp)

{

string strName = item.Name;

string Designation = item.Designation;

string Department = item.Department;

string Salary = item.Salary;

}

Display Specific column Name in gridview

NorthwindDataContextDataContext db = new NorthwindDataContextDataContext();

var cust = (from p in db.Customers

where p.CustomerID == “test”

select new

{

ID = p.CustomerID,

Name = p.ContactName,

Phone = p.Phone

}

);

GridView1.DataSource = cust;

GridView1.DataBind();

Calculation of price in Gridview

NorthwindDataContextDataContext db = new NorthwindDataContextDataContext();

var product = (from p in db.Products

where p.CategoryID == 1

select new

{

ProductName = p.ProductName,

Total = p.Order_Details.Count,

Revenu = p.Order_Details.Sum(o=>o.UnitPrice * o.Quantity)

}

);

GridView1.DataSource = product;

GridView1.DataBind();

Gridview Paging

protected void Page_Load(object sender, EventArgs e)

{

int intPageNumber = Convert.ToInt32(Request.QueryString["StartRow"]);

BindGridviewView(intPageNumber);

}

public void BindGridviewView(int StartRow)

{

NorthwindDataContextDataContext db = new NorthwindDataContextDataContext();

var Customers = (from p in db.Customers

select new

{

Name = p.ContactName,

Phone =p.Phone,

Fax = p.Fax

}

);

GridView1.DataSource = Customers.Skip(StartRow).Take(10);

GridView1.DataBind();

}

USE [ControlLibrary]
GO
/****** Object:  Table [dbo].[cms_contentdetails]    Script Date: 01/13/2010 09:07:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cms_contentdetails](
[intContentDetailId] [int] IDENTITY(1,1) NOT NULL,
[intNodeID] [int] NULL,
[txtDefaultURL] [varchar](500) NULL,
[flgCustomURL] [int] NULL,
[txtCustomURL] [varchar](500) NULL,
[txtTitle] [varchar](500) NULL,
[txtMetaKeyword] [text] NULL,
[txtMetaDescription] [text] NULL,
[txtMainContent] [text] NULL,
[flgPublish] [int] NULL,
[flgDeleted] [int] NULL,
[txtCreatedBy] [varchar](50) NULL,
[dtDateCreated] [datetime] NULL,
[txtModifiedBy] [varchar](500) NULL,
[dtDateModified] [datetime] NULL,
[txtPageName] [varchar](500) NULL,
[txtNodeName] [varchar](500) NULL,
[intNodeParentID] [int] NULL,
[intNodeOrder] [int] NULL,
[flgActive] [int] NULL,
CONSTRAINT [PK_cms_contentdetails] PRIMARY KEY CLUSTERED
(
[intContentDetailId] 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
SET ANSI_PADDING OFF

LINQ Crude Operation

Login

NorthwindDataContextDataContext db = new NorthwindDataContextDataContext();

var Login = (from p in db.LoginTables

where p.UserName == txtuserName.Text && p.Password == txtPassword.Text

select p

);

int intCount = Login.Count();

if (intCount > 0)

Response.Write(“Welcome”);

else

Response.Write(“Login Failed”);

Insert

EmployeeLinqDataContext db = new EmployeeLinqDataContext();

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

BindDropDownData();

if (Request.QueryString["ID"] != null)

{

int intID = Convert.ToInt32(Request.QueryString["ID"]);

var getCust = (from p in db.EmployeeInfors

where p.ID== intID

select p

);

foreach (var item in getCust)

{

txtDesignation.Text = item.Designation;

txtName.Text = item.Name;

txtSalary.Text = item.Salary;

ddlDepartment.SelectedValue = item.Department;

}

}

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

var query = (from p in db.Logins

where p.UserName == txtUserName.Text

select p);

int totalCount = query.Count();

if (totalCount > 0)

Response.Write(“This username already exist”);

else

{

if (FileUpload1.HasFile)

{

string strFileName = FileUpload1.FileName;

FileUpload1.PostedFile.SaveAs(Server.MapPath(“~/Files” + strFileName));

EmployeeInfor claEmployee = new EmployeeInfor();

claEmployee.Name = txtName.Text;

claEmployee.Salary = txtSalary.Text;

claEmployee.Designation = txtDesignation.Text;

claEmployee.Department = ddlDepartment.SelectedValue;

claEmployee.FileName = strFileName;

db.EmployeeInfors.InsertOnSubmit(claEmployee);

db.SubmitChanges();

}

else

{

string strFileName = “NoFile”;

EmployeeInfor claEmployee = new EmployeeInfor();

claEmployee.Name = txtName.Text;

claEmployee.Salary = txtSalary.Text;

claEmployee.Designation = txtDesignation.Text;

claEmployee.Department = ddlDepartment.SelectedValue;

claEmployee.FileName = strFileName;

db.EmployeeInfors.InsertOnSubmit(claEmployee);

db.SubmitChanges();

}

}

}

public void BindDropDownData()

{

var Dropdown = (from p in db.Designations

orderby p.DesignationName

select p);

ddlDepartment.DataSource = Dropdown;

ddlDepartment.DataTextField = “DesignationName”;

ddlDepartment.DataValueField = “DesignationID”;

ddlDepartment.DataBind();

}

Update Data

NorthwindDataContextDataContext db = new NorthwindDataContextDataContext();

Customer ClaCustomer = db.Customers.Single(p => p.CustomerID == “Test”);

ClaCustomer.Country = “UK”;

ClaCustomer.ContactName = “Manoj”;

db.SubmitChanges();

Data in Gridview

EmployeeLinqDataContext db = new EmployeeLinqDataContext();

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindGridviewData();

}

}

public void BindGridviewData()

{

var gridviewData = (from p in db.EmployeeInfors

select p);

GridView1.DataSource = gridviewData;

GridView1.DataBind();

}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName == “Modify”)

{

int index = Convert.ToInt32(e.CommandArgument);

GridViewRow row = GridView1.Rows[index];

ListItem item = new ListItem();

item.Text = Server.HtmlDecode(row.Cells[2].Text);

Response.Redirect(“registration.aspx?ID=” + item.Text);

}

else if (e.CommandName == “Remove”)

{

int index = Convert.ToInt32(e.CommandArgument);

GridViewRow row = GridView1.Rows[index];

ListItem item = new ListItem();

item.Text = Server.HtmlDecode(row.Cells[2].Text);

int intID = Convert.ToInt32(item.Text);

var DeleteDat = (from p in db.EmployeeInfors

where p.ID == intID

select p

);

db.EmployeeInfors.DeleteAllOnSubmit(DeleteDat);

db.SubmitChanges();

BindGridviewData();

}

}

Select Statement LINQ

NorthwindDataContextDataContext db = new NorthwindDataContextDataContext();
var Customers = from p in db.Customers
//If you need to filter data
//where p.CustomerID == “Test”
select p;
GridView1.DataSource = Customers;
GridView1.DataBind();