fastest way

Question

Hi

I have a table with one column of datatype varchar(max)

and the data is insert statements

can anybody tell me the fastest way to execute all these statements in a single shot.

select * from table_name gives

col1

------

insert into table2 values('a','b','c')

insert into table2 values('a1','b1','c1')

insert into table2 values('a2','b2','c2')

insert into table2 values('a3','b3','c3')

i want to execute all the  statements in a single shot

sometime the records count may be greater than 90000

earlier help is highly appreciated

Regards

kalyan

Answer 1

you can use dynamic sql and cursors or loop to execute them 

Answer 2

i guess cursors and loops are time consuming

Answer 3

There are a few different ways.  One would be to adjust the logging ot the statements to insert the 3 values into a 3 column table so you can just insert them all in one go. 

If they all insert into table2, you can use a substring or PATINDEX, to extract the three values from each row into a temporary table, then insert them all in one go.

If they insert into different tables you're going to need to iterate through each in turn in a cursor and execute them one at a time.

Are they all inserts for table2?

Regards

James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 4

yes all inserts are into same table

Answer 5

It is not clear what do you want when you say...

<q>and the data is insert statements</q>

and what do you mean by Single Shot? do you mean Single statement?

---

Regards, Puneet Sharma Sr.Software Developer

Answer 6

You said there's only one column but you want to insert as 'a','b','c' ?

Answer 7

table_name has only one column that contains insert statements for table2 as data

Answer 8

You may use dynamic sql to generate those Insert statements. Please try somethink like this -

*Set the variable @rowscount to desired value

DECLARE @init INT=1
DECLARE @rowscount INT=1000;
DECLARE @sql NVARCHAR(MAX);
WHILE (@init < @rowscount)
BEGIN
SET @sql = 'INSERT INTO gen(col) VALUES
		('+''''+ 'a'+ CONVERT(VARCHAR(20),@init)++''''+',' 
		+''''+'b'+CONVERT(VARCHAR(20),@init)+''''+ ',' 
		+''''+ 'c'+CONVERT(VARCHAR(20),@init)+''''+')'
EXEC sp_executesql @sql
SELECT @init = @init + 1
END

Also, can you please post the table structure for Source and Target ?

Answer 9

for single record we can do this :

declare @table table (string varchar(max))
insert into @table values('insert into table2 values(''a'',''b'',''c'')')

DECLARE @SQL varchar(max)
SET @SQL = (SELECT string FROM @table)
 --print  

exec  @SQL

for row wise we will need loop 

Answer 10

@ naarasimha<abbr class="affil"></abbr>

what if the rowcount=1000000

Answer 11

I think that should work with that value.

Answer 12

Try this.

declare @v nvarchar(max); set @v=''
select @v = @v + ' ' + InsertValue from table_name
execute(@v)

---

Thanks & Regards,
Please do "Mark As Answer" if this helps you.

Answer 13

You can do the whole operation in one set by stipping out the individual values and performing a single insert (presuming there's no confilcting strings with [','] in them).  You can do the whole thing in one complex formatting statement, but I've split it into stages to show it one bit at a time.

Regards

James

with InsertStatements as 
(select
'insert into table2 values(''a'',''b'',''c'')' AS col1
union select
'insert into table2 values(''a1'',''b1'',''c1'')'
 union select
'insert into table2 values(''a2'',''b2'',''c2'')'
 union select	
'insert into table2 values(''a3'',''b3'',''c3'')'
)
,
stage1 as 
(
select
	SUBSTRING(col1,28,LEN(col1)-29) AS Val
from
	insertstatements
)
,
stage2 as 
(
select 
	left(val,PATINDEX('%'',''%', val)-1) AS firstVal, val
from
	stage1
)
,
stage3 as 
(
select 
	firstVal, RIGHT(val,len(val)-len(firstVal)-3) AS Rest
from
	stage2	
),
stage4 as 
(
select
	firstVal, left(rest,PATINDEX('%'',''%', rest)-1) AS secondVal, rest
from
	stage3
)	

insert into table2
select
	firstVal,secondVal, RIGHT(rest,len(rest)-len(secondVal)-3) AS thirdVal
from
	stage4

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 14

Hi kalyan.

You can use below code  that in 1 shot bulit 90000 row with row number (Its Useful for more operations).

Same below:

create table pt(id int identity    ,
                 a varchar(4) null ,
				 b varchar(4) null , 
				 c varchar(4) null )
--====================================
declare @count int
set @count = (select count(*) from pt)
if @count = 0
begin
insert into pt values('a','b','c')
end
--===================================
declare @ident bigint
set @ident=@@identity
while @ident < 90000
begin

declare @a varchar(4)
declare @b varchar(4)
declare @c varchar(4)
set @a= 'a'+ cast (@ident as varchar(4) )
set @b= 'b'+ cast (@ident as varchar(4) )
set @c= 'c'+ cast (@ident as varchar(4) )
insert into pt values(@a,@b,@c)
set @ident=@ident+1
end
--===================================
select * from pt

result is :

id          a    b    c
----------- ---- ---- ----
1           a    b    c
2           a1   b1   c1
3           a2   b2   c2
4           a3   b3   c3
5           a4   b4   c4
.           .    .     .
.           .    .     .
.           .    .     .

Answer 15

declare @SQL nvarchar(max)

set @SQL = (select ';

' + col1 from table_name for XML PATH(''),type).value('.','nvarchar(max)')

print @SQL

execute sp_executeSQL @SQL

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 16

Hard to believe the incredible power and speed of dynamic SQL as suggested by Naomi.

Can actually dynamic SQL execute 100,000 INSERT statements in one shot?

Here is the proof:

use tempdb;

SELECT TOP (0) SID=CONVERT(INT,SalesOrderDetailID), 
	OrderQty, ProductID, LineTotal
INTO SOD FROM AdventureWorks2012.Sales.SalesOrderDetail;
GO
/*   drop table SOD  */


declare @SQL nvarchar(max)
 
set @SQL = (select 'INSERT SOD VALUES (' + 
	convert(varchar,SalesOrderDetailID)+
	', '+convert(varchar, OrderQty)+', '+convert(varchar, ProductID)+
	', '+convert(varchar, LineTotal)+');'+char(13) 
from AdventureWorks2012.Sales.SalesOrderDetail
for XML PATH(''),type).value('.','nvarchar(max)')
 
print @SQL
/* INSERT SOD VALUES (1, 1, 776, 2024.994000);
INSERT SOD VALUES (2, 3, 777, 6074.982000);
INSERT SOD VALUES (3, 1, 778, 2024.994000);
INSERT SOD VALUES (4, 1, 771, 2039.994000);
INSERT SOD VALUES (5, 1, 772, 2039.994000);.......*/
 
execute sp_executeSQL @SQL

select count(*) from SOD;
go
-- 121317
-- 7 seconds

Dynamic SQL blog:

http://www.sqlusa.com/bestpractices/dynamicsql/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012