Brain stuck for following query

Hi,

I have 2 tables

TBL 1

CREATE TABLE [dbo].[a](
[col0] [int] NOT NULL,
[col1] [int] NOT NULL,
[col2] [int],
[col3] [int]
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[col0] ASC,
[col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

TBL 2

CREATE TABLE [dbo].(
[col0] [int] NULL,
[col1] [int] NULL,
[col2] [int] NULL,
[col3] [int] NULL
) ON [PRIMARY]

I want to insert all unique record from table b to table a (uniqueness is only based on first 2 columns). Keeping in mind i have 10 million records in table b and cannot insert 1 by 1 records.

insert into [a] select col0,col1,max(col2),max(col3) from group by col0,col1.
Will this query work?



________________________________________________
Be great in act, as you have been in thought.

I don't see any problem with your query but make sure you have right index and tlog configure with enough space....


MohammedU.
Moderator
SQL-Server-Performance.com

That would be long running query and may singificantly increase the size of log file. To avoid that you can try to insert rows in cycle using smaller batches. One possible solution is:


declare @max int
declare @step int
declare @first int

select @first = min(col0), @max = max(col0)
from a

set @step = 100

while (1=1) begin
insert into a
select col0, col1, max(col2), max(col3)
from b
where b.col0 between @first and @first + @step - 1

set @first = @first + @step

if @first > @max break

end -while

If database recovery model is full you may need frequent transaction log backups scheduled during the query execution.