Inserts in Batches - 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 2005 Forums

Transact-SQL (2005)

Inserts in Batches

Author

Topic

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-18 : 06:38:02

I need to insert 5 lakhs records in a table. I dont want to do it in a single go. Is there a way to insert it in ,say, 5 batches using a script. Any help will be much appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-18 : 06:44:06

Depends on the way you are inserting data. Are you copying records from one table to another?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-18 : 07:20:06

Yes, Harsh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:15:53

If you are having an identity column, you could try

insert table1
select * from table2
where table2.id % 5 = 0

insert table1
select * from table2
where table2.id % 5 = 1

insert table1
select * from table2
where table2.id % 5 = 2

insert table1
select * from table2
where table2.id % 5 = 3

insert table1
select * from table2
where table2.id % 5 = 4

E 12°55'05.25"
N 56°04'39.16"

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-18 : 08:23:39

Peso, Thnaks for the reply, But unfortunately i dont have Identity keys in the Tables. Is there any other way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:30:20

[code]insert table1 ( {col list here} )
select ( {col list here} ) from (
select {col list here}, ntile(5) over (ORDER BY {somecol}) AS Grp from table2
) AS d WHERE Grp = 1

insert table1 ( {col list here} )
select ( {col list here} ) from (
select {col list here}, ntile(5) over (ORDER BY {somecol}) AS Grp from table2
) AS d WHERE Grp = 2

insert table1 ( {col list here} )
select ( {col list here} ) from (
select {col list here}, ntile(5) over (ORDER BY {somecol}) AS Grp from table2
) AS d WHERE Grp = 3

insert table1 ( {col list here} )
select ( {col list here} ) from (
select {col list here}, ntile(5) over (ORDER BY {somecol}) AS Grp from table2
) AS d WHERE Grp = 4

insert table1 ( {col list here} )
select ( {col list here} ) from (
select {col list here}, ntile(5) over (ORDER BY {somecol}) AS Grp from table2
) AS d WHERE Grp = 5[/code]

E 12°55'05.25"
N 56°04'39.16"

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-28 : 08:35:15

sorry for the late reply, it took some time for me to test this, peso, thanx very much!! that did the trick!!

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 14:02:56

quote:
Originally posted by abhwhiz

I need to insert 5 lakhs

lakhs???

What the hell is that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 14:05:56

http://en.wikipedia.org/wiki/Lakh
One lakh is equal to a hundred thousand

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 14:12:43

and there you go

But 5 lakhs ain't that many

Why would you need to break it up?

iT'S NOT LIKE ITS 5000 LAKHS

I hate it when I hit caps lock

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-28 : 14:14:32

Is a lakh kin to a yak?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 14:17:09

Look at Mladen's link

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-28 : 14:19:35

Yea, I was just trying to be funny...obviously I was unsucessful.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 14:42:42

if it helps... i laughed

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

abhwhiz
Starting Member

37 Posts

Posted - 2007-10-11 : 10:42:29

me too :)

abhwhiz
Starting Member

37 Posts

Posted - 2007-10-11 : 10:42:50

sorry for that usage too...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources