11:37 AM
Bhavik Shah
Today itself I encounter a situation
where I need to insert multiple data rows with single SQL query.
USE [Demo]
GO
GO
--Create Demo Table—
CREATE TABLE DemoTable(Data VARCHAR(50))
--The way we used to—
INSERT INTO DemoTable(Data)
VALUES ('DATA 1');
INSERT INTO DemoTable(Data)
VALUES ('DATA 2');
INSERT INTO DemoTable(Data)
VALUES ('DATA 3');
INSERT INTO DemoTable(Data)
VALUES ('DATA 4');
Solution 1:
CREATE TABLE DemoTable(Data VARCHAR(50))
INSERT INTO DemoTable(data)
VALUES ('DATA 1'),('DATA 2'),('DATA 3'),('DATA 4');
Solution 2:
CREATE TABLE DemoTable(Data VARCHAR(50))
INSERT INTO DemoTable(data)
SELECT 'DATA 1'
UNION ALL
SELECT 'DATA 2'
UNION ALL
SELECT 'DATA 3'
UNION ALL
SELECT 'DATA 4'
Posted in: SQL
6 comments:
Hi,
You can use BulkInsert Utility to insert thousand of records at single trip.
Try the following code in SQL Server. View the explanation here
http://www.cyberminds.co.uk/blog/articles/how-to-insert-multiple-rows-in-sql-server.aspx
declare @test nvarchar(max)
set @test = '
comment 1
comment 2
comment 3
'
declare @testxml xml
set @testxml = cast(@test as xml)
declare @answerTemp Table(dialogid int, answerid int, comment varchar(1000))
insert @answerTemp
SELECT ParamValues.ID.value('@id','int') ,
ParamValues.ID.value('@answerId','int') ,
ParamValues.ID.value('(comment)[1]','VARCHAR(1000)')
FROM @testxml.nodes('topic/dialog') as ParamValues(ID)
Thanks
Sorry formatting is not correct try this instead in SQL Server. View the explanation here
http://www.cyberminds.co.uk/blog/articles/how-to-insert-multiple-rows-in-sql-server.aspx
declare @test nvarchar(max)
set @test = '
<font color="#FF0000">'<topic><dialog id="1" answerId="41">
<comment>comment 1</comment>
</dialog>
<dialog id="2" answerId="42" >
<comment>comment 2</comment>
</dialog>
<dialog id="3" answerId="43" >
<comment>comment 3</comment>
</dialog>
</topic>'</font>
declare @testxml xml
set @testxml = cast(@test as xml)
declare @answerTemp Table(dialogid int, answerid int, comment varchar(1000))
insert @answerTemp
SELECT ParamValues.ID.value('@id','int') ,
ParamValues.ID.value('@answerId','int') ,
ParamValues.ID.value('(comment)[1]','VARCHAR(1000)')
FROM @testxml.nodes('topic/dialog') as ParamValues(ID)
Hi use INSERT SELECT statement
check http://msdn.microsoft.com/en-us/library/ms188263.aspx
I love solution 1, it's pretty straight forward.
Thanks Ogolla.
Will post some other ways to doing it also very soon.
The second method listed over here can be usefull if you want to have data from multiple table in to one.
Thanks
Bhavik
Post a Comment