query help needed - 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)

query help needed

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-13 : 01:44:19

hi all,

i have to copy all records from a table to a new table by copying only 100 records at a time.how can i do this ....

thanks in advance.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:00:58

100 records at a time? So you will be doing this copy process periodically?

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-13 : 02:07:06

hi visakh

no,i want to do it all at a time..but each insertion will insert only 100 records to the new table at a time and this will continue till all records are inserted.

ok tanx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:19:45

May be this:-

DECLARE @SourceCount int,@DestCount int

SELECT @SourceCount=COUNT(*),@DestCount=0
FROm SourceTable
WHILE @DestCount<=@SourceCount
BEGIN
INSERT INTO DestTable
SELECT TOP 100 * 
FROM SourceTable s
LEFT JOIN DestTable d
ON d.PKCol=s.PKCol
WHERE d.PKCol IS NULL

SELECT @DestCount=COUNT(*)
FROM DestTable
END

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-13 : 02:22:12

hi visakh
many tanx for ur sudden reply..
ok......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:31:16

quote:
Originally posted by soorajtnpki

hi visakh
many tanx for ur sudden reply..
ok......

Couple of small corrections

DECLARE @SourceCount int,@DestCount int

SELECT @SourceCount=COUNT(*),@DestCount=0
FROm SourceTable
WHILE @DestCount<@SourceCount
BEGIN
INSERT INTO DestTable
SELECT TOP 100 s.* FROM SourceTable s
LEFT JOIN DestTable d
ON d.PKCol=s.PKCol
WHERE d.PKCol IS NULL

SELECT @DestCount=COUNT(*)
FROM DestTable
END

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-13 : 02:37:31

hi visakh,
some changes in my requirement..
i have no primary column in my table..there is date field in table.
and i have to to do this copy process based on a date(query records which are less than a particular date),which is an input.

and also i have to delete the records from old table which are inserted into new table also..

ok tanx.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 03:09:19

Wow. Requirements changed from copy only to move!

-- Initialize user supplid datetime parameter
DECLARE @theDate DATETIME

SET	@theDate = '20080101'

-- Do the actual work
WHILE @@ROWCOUNT = 0
	DELETE	TOP (100)
		st
	OUTPUT	st.*
	INTO	TargetTable
	FROM	SourceTable AS st
	WHERE	st.DateTimeColumn < @theDate

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

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-13 : 04:30:54

hi
tanx peso

okk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 05:28:10

The real question is why you only want 100 records at a time?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources