Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Query to use Top Keyword Clause with Insert Statement in SQL Server

Aug 7, 2012
Introduction:

Here I will explain how to write SQL query to use top keyword or clause with with insert statement in SQL Server.

Description:

In previous post I explained use distinct keyword with top statement, Replace function example and Substring function example in SQL Server and many more articles relating to SQL Server. Now I will explain how to use top clause or keyword with insert statement in SQL Server.

Before write query to use top keyword or clause with insert statement we need to design table UserInformation in database like as shown below

Column Name
Data Type
Allow Nulls
UserId
Int (set Identity=true)
No
UserName
varchar(50)
Yes
Location
Varchar(50)
Yes
Here we can use Top keyword or clause with insert statement in two different ways

First Method

Syntax to use top keyword with insert statement


INSERT INTO Table1
SELECT TOP (N) Column1, Column2 FROM Table2
Example

-- Declare temp table
CREATE TABLE #temp(name VARCHAR(50),place VARCHAR(50))
INSERT INTO #temp(name,place) VALUES('test','Chennai')
INSERT INTO #temp(name,place) VALUES('test1','Hyderabad')
INSERT INTO #temp(name,place) VALUES('test2','Guntur')
-- insert temp table values into UserInformation table
INSERT INTO UserInformation(UserName,Location)
SELECT TOP 2 name,place FROM #temp
DROP TABLE #temp
Once we run above query our table UserInformation will contain data like as shown below

Output

Second Method

Syntax to use top keyword with insert statement


INSERT TOP(N) INTO Table1
SELECT Column1,Column2 FROM Table2
Example


-- Declare temp table
CREATE TABLE #temp(name VARCHAR(50),place VARCHAR(50))
INSERT INTO #temp(name,place) VALUES('test','Chennai')
INSERT INTO #temp(name,place) VALUES('test1','Hyderabad')
INSERT INTO #temp(name,place) VALUES('test2','Guntur')
-- insert temp table values into UserInformation table
INSERT TOP(2) INTO UserInformation(UserName,Location)
SELECT name,place FROM #temp
DROP TABLE #temp
Once we run above query our table UserInformation will contain data like as shown below

Output

By using above two methods we can insert data into tables with top keyword in SQL Server

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

3 comments :

Manoj said...

You Can write like this also..

INSERT INTO UserInformation(UserName,Location)
SELECT TOP 2 name,place FROM #temp

Anonymous said...

i want to known insert query using where

Anonymous said...

by using this it work like where....?

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.