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.