Mass insert question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Mass insert question

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-01-09 : 15:33:58

Hi, I want to generate several million records on a test table so that I can test the performance of my queries before implemented on production. Say the table is -

Create table TEST (
ID int identity(1,1) primary key not null ,
Content varchar(256) null,
Crdate datetime default current_timestamp
)

Is there any way to do some kind of bulk record insert into the table (I can use a loop, but it takes quite while to complete)? For example, something can do this -

select 1000000 'This is a test' into TEST.Content

Thanks,
George

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-09 : 15:54:33

Here is an example script that I wrote:



declare @comp_id int
declare @terminal_ID int
declare @unified_address bigint
declare @counter int
declare @another_counter int
declare @yet_another_counter int 
declare @not_another int

set @comp_id = 11958
set @counter = 0
set @yet_another_counter = 0
set @not_another = 2

-- This will add 6000 terminals to 1 company
while @comp_id < 11959
BEGIN
	while @counter < 6001
	BEGIN
		SELECT @unified_address = 59800000 + @counter

		INSERT INTO gt.dbo.TERMINAL_GT 
		(	Terminal_UA, 
			AssetID, 
			company_id, 
			LastStatusChangeDate, 
			RecordCreate_DT,
			RegistrationStatus,
			TerminalPowerState,			
			Deactivated,
			isTerminalOwner	)
		VALUES 
		(	@unified_address, 
			0, 
			@comp_id, 
			getdate(), 
			getdate(),
			@not_another,
			@yet_another_counter,			
			0,
			1	)
	
		SELECT @terminal_ID = terminal_GT_ID   
		FROM gt.dbo.TERMINAL_GT
		WHERE Terminal_UA = @unified_address

		EXEC gt.dbo.isp_InsertParmValues @terminal_ID
		
		SET @counter = @counter + 1

		IF @yet_another_counter = 5
			SET @yet_another_counter = 0
		ELSE
			SET @yet_another_counter = @yet_another_counter + 1

		IF @not_another = 5
			SET @not_another = 0
		ELSE
			SET @not_another = @not_another + 1
	END
	SELECT @comp_id = @comp_id + 1
END

-- This will add 10 terminals to 399 companies
set @another_counter = 1

while @comp_id < 12358
BEGIN
	while @another_counter < 11
	BEGIN
		SELECT @unified_address = 59800000 + @counter

		INSERT INTO gt.dbo.TERMINAL_GT 
		(	Terminal_UA, 
			AssetID, 
			company_id, 
			LastStatusChangeDate, 
			RecordCreate_DT,
			RegistrationStatus,
			TerminalPowerState,		
			Deactivated,
			isTerminalOwner	)
		VALUES 
		(	@unified_address, 
			0, 
			@comp_id, 
			getdate(), 
			getdate(),
			@not_another,
			@yet_another_counter,
			0,
			1	)
	
		SELECT @terminal_ID = terminal_GT_ID   
		FROM gt.dbo.TERMINAL_GT
		WHERE Terminal_UA = @unified_address

		EXEC gt.dbo.isp_InsertParmValues @terminal_ID
		
		SET @counter = @counter + 1
		SET @another_counter = @another_counter + 1

		IF @yet_another_counter = 5
			SET @yet_another_counter = 0
		ELSE
			SET @yet_another_counter = @yet_another_counter + 1

		IF @not_another = 5
			SET @not_another = 0
		ELSE
			SET @not_another = @not_another + 1
	END
	set @another_counter = 1
	select @comp_id = @comp_id + 1
END

Tara

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-01-09 : 16:49:09

Figure out a way -

set nocount on
declare @i = 0
insert into Test(Content) Values('This is a test')

while @i < 20
begin
insert into Test(Content) select Content from Test
select @i = @i + 1
end

set nocount off

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-10 : 03:48:36

Looping will get slower and slower as the number of iterations increases...this is what you need: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30427

OS

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-10 : 13:48:33

I usually use ms access for this purpose. Then DTS or bulk insert.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-01-10 : 14:32:04

Do a search for Tally Tables on this sites and you shall find what you're looking for.

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-01-13 : 17:21:47

Thanks all!

George