CODEWITHSUNDEEP
sql-server-2005optimizationinsert
Scott Chamberlain
Scott Chamberlain

Reputation: 127553

Inserting very many rows in to SQL 2005

I have a project where I need to build a two tables in sql 2005 and fill them with data (they are look-up tables for weather a name is a male's name or a female's name) I have 7354 male names to insert and 7776 female names to insert.

currently I do it

CREATE TABLE #NamesMale (
[FirstName] [varchar](50) NOT NULL,
   [MaxRank] [smallint] NULL)

CREATE TABLE #NamesFemale (
[FirstName] [varchar](50) NOT NULL,
   [MaxRank] [smallint] NULL)

insert into #NamesMale select 'AADEN',343
insert into #NamesMale select 'AAPELI',1000
insert into #NamesMale select 'AAPO',1000
... (snip ~7300 lines)
insert into #NamesMale select 'ZVONIMIR',1000
insert into #NamesMale select 'ZVONKO',1000
insert into #NamesMale select 'ZYGMUNT',1000

insert into #NamesFemale select 'AALIYAH',64
insert into #NamesFemale select 'AAREN',1000
insert into #NamesFemale select 'AARON',873
... (snip ~7700 lines)
insert into #NamesFemale select 'ZUZANA',1000
insert into #NamesFemale select 'ZUZANNA',1000
insert into #NamesFemale select 'ZVONIMIRA',1000

It currently takes a one minute fourteen seconds to load the names on my machine and where this will be used in the field the servers will often be much less powerful than my dev machine.

using a separate bulk copy file is not a option, the solution must be entirely transact-sql and in one file. Preferably I would like a solution that does not break compatibility with sql2000 but that is not a requirement.

Any help would be greatly appreciated.

Upvotes: 0

Views: 151

Answers (2)

Scott Chamberlain
Scott Chamberlain

Reputation: 127553

I found the answer to my own question. while trying to give data to the progress bar for the program that is running this code (it uses GOs passed/total GOs) by peppering in 10 or 11 GO commands in the inserts it now finishes in a few seconds.

I don't know why it fixed it but if anyone does I would appreciate the explanation in the comments.

Upvotes: 0

Mark Ransom
Mark Ransom

Reputation: 308158

I don't know if the select is slowing you down. I would do it:

insert into NamesMale (FirstName,MaxRank) values ('AADEN',343)

Upvotes: 2

Related Questions