Different ways to insert data into Sql Server table

Different-ways-to-insert-data-into-Sql-Server-table

Different ways to insert data into Sql Server table - You will learn here, How to use Sql Server Insert Query in different ways? So many ways to insert data into sql server tables. You will learn here most commonly used queries to insert data.


To learn more, you might visit following topics

SQL Server UNION, UNION ALL, EXCEPT and INTERSECT Operator

2 Minutes for types of commands in SQL Server

T-Sql to replace multiple spaces with single space


-- Create Temporary Table CREATE TABLE #tbStudent ( ID INT IDENTITY(1,1) PRIMARY KEY, StudentName NVARCHAR(200), CLASSID INT, ROLLNO NVARCHAR(20) ) Go

1). Simple Sql INSERT Query

INSERT #tbStudent VALUES('Johan',1, '12') Go SELECT * FROM #tbStudent Go

Output

ID StudentName CLASSID ROLLNO
1 Johan 1 12

(1 row affected)

2). Simple Sql INSERT INTO Query

INSERT INTO #tbStudent VALUES('prince',1, '13') Go SELECT * FROM #tbStudent Go

Output

ID StudentName CLASSID ROLLNO
1 Johan 1 12
2 prince 1 13

(2 rows affected)

3). Simple Sql INSERT INTO with selected columns Query

INSERT INTO #tbStudent(StudentName,CLASSID,ROLLNO) VALUES('Monica',1, '14') Go SELECT * FROM #tbStudent Go

Output

ID StudentName CLASSID ROLLNO
1 Johan 1 12
2 prince 1 13
3 Monica 1 14

(3 rows affected)

4). Simple Sql INSERT INTO with multiple values Query

INSERT INTO #tbStudent(StudentName,CLASSID,ROLLNO) VALUES('Maria',1, '15'),('Rock',1, '16'),('Raman',1, '17'),('Polar',1, '18') Go SELECT * FROM #tbStudent Go

Output

ID StudentName CLASSID ROLLNO
1 Johan 1 12
2 prince 1 13
3 Monica 1 14
4 Maria 1 15
5 Rock 1 16
6 Raman 1 17
7 Polar 1 18

(7 rows affected)

5). Sql INSERT Rows from another table

-- COPY Table Structure SELECT * INTO #tbStudent_dup FROM (SELECT TOP 0 * FROM #tbStudent) A GO SELECT * FROM #tbStudent_dup Go

Output

ID StudentName CLASSID ROLLNO

(0 rows affected)

SET IDENTITY_INSERT #tbStudent_dup ON Go INSERT INTO #tbStudent_dup(ID,StudentName,CLASSID,ROLLNO) (SELECT ID,StudentName,CLASSID,ROLLNO FROM #tbStudent) SET IDENTITY_INSERT #tbStudent_dup OFF Go SELECT * FROM #tbStudent_dup Go

Output

ID StudentName CLASSID ROLLNO
1 Johan 1 12
2 prince 1 13
3 Monica 1 14
4 Maria 1 15
5 Rock 1 16
6 Raman 1 17
7 Polar 1 18

(7 rows affected)

6). Sql INSERT INTO With Multiple Data Using UNION ALL Query

INSERT INTO #tbStudent(StudentName,CLASSID,ROLLNO) SELECT 'Maria',1, '15' UNION ALL SELECT 'Rock',1, '16' UNION ALL SELECT 'Raman',1, '17' UNION ALL SELECT 'Polar',1, '18' SELECT * FROM #tbStudent Go

Output

ID StudentName CLASSID ROLLNO
1 Johan 1 12
2 prince 1 13
3 Monica 1 14
4 Maria 1 15
5 Rock 1 16
6 Raman 1 17
7 Polar 1 18
8 Maria 1 15
9 Rock 1 16
10 Raman 1 17
11 Polar 1 18

(11 rows affected)

7). Sql Query to Move Rows From One Table into Another Row By Row Using While Loop

--CREATE TABLE FROM ANOTHER EXISTING TABLE WITHOUT DATA SELECT * INTO #tbStudent_dump FROM (SELECT TOP 0 * FROM ##tbStudent) AS A GO DECLARE @count int = 0 DECLARE @temp int = 0 WHILE @count<(SELECT COUNT(*) FROM #tbStudent) BEGIN SELECT TOP 1 @temp=ID FROM #tbStudent INSERT INTO #tbStudent_dump(StudentName,CLASSID,ROLLNO) SELECT TOP 1 StudentName,CLASSID,ROLLNO FROM #tbStudent DELETE FROM #tbStudent WHERE ID=@temp END SELECT * FROM #tbStudent Go SELECT * FROM #tbStudent_dump Go

Output

ID StudentName CLASSID ROLLNO

(0 rows affected)


ID StudentName CLASSID ROLLNO
1 Johan 1 12
2 prince 1 13
3 Monica 1 14
4 Maria 1 15
5 Rock 1 16
6 Raman 1 17
7 Polar 1 18
8 Maria 1 15
9 Rock 1 16
10 Raman 1 17
11 Polar 1 18

(11 rows affected)

If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.


Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.

 
 

{{c.Content}}

Comment By: {{c.Author}}  On:   {{c.CreatedDate|date:'dd/MM/yyyy'}} / Reply


Categories