Copying/ Deleting Huge Data from a table

Hi,
I have a problem about "userlogs" table . this table contains some
information about users actions .The table has nearly 800.000.000 record in
it and ~100Gb data , 50GB index.
i have tried to copy some data ( like 1M records) to a temporary database, i
couldnt manage it
This is the query , the table has a index contains only "id"
declare @.start_index int, @.end_index int
set @.start_index = 800000050
set @.end_index = 800000250
insert into userlogsbackup select * from userlogs where id > @.start_index
and id < @.end_index
but the select fails unless specifying "top 10" for example. What else can
i try ?
Thanks in advanceIf the index is not clustered then it can't do a range scan without also
doing bookmark lookups for each row found. So the more rows you will touch
the less the chances of using the index. But in your case the optimizer does
not know how many rows will be affected since it does not know the values of
the two variables. As such it guesses and the guess for a range is quite a
large percentage (can't remember off hand) which will negate the use of an
index s. Do you get the proper plan if you hard code the two values?
You can try creating a stored procedure that has the two values as
parameters. That way the optimizer will use the actual values passed the
first time to create the plan. Does it work with a TOP 250?
Andrew J. Kelly SQL MVP
"Hasan O." <hozavalsiz@.gmail.com> wrote in message
news:esQBHkKgGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a problem about "userlogs" table . this table contains some
> information about users actions .The table has nearly 800.000.000 record
> in it and ~100Gb data , 50GB index.
> i have tried to copy some data ( like 1M records) to a temporary database,
> i couldnt manage it
> This is the query , the table has a index contains only "id"
> declare @.start_index int, @.end_index int
> set @.start_index = 800000050
> set @.end_index = 800000250
>
> insert into userlogsbackup select * from userlogs where id >
> @.start_index and id < @.end_index
> but the select fails unless specifying "top 10" for example. What else
> can i try ?
> Thanks in advance
>|||Hi Andrew ,
thanks for your answer.
yes index is not clustered . it doesnt work with top 70 .
I think i am gonna write while loop for selecting , and inserting one by one
like this
while @.start_index and < @.end_index
begin
1
2 insert into userlogsbackup select * from userlogs where id =
@.start_index
3 delete from userlogs where id = @.start_index
4 set @.start_index = @.start_index + 1
5
end
i am also thinking to use "begin transaction and commit transaction" . Can
you tell me if which lines transactions contains
thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uaZyrgLgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> If the index is not clustered then it can't do a range scan without also
> doing bookmark lookups for each row found. So the more rows you will touch
> the less the chances of using the index. But in your case the optimizer
> does not know how many rows will be affected since it does not know the
> values of the two variables. As such it guesses and the guess for a range
> is quite a large percentage (can't remember off hand) which will negate
> the use of an index s. Do you get the proper plan if you hard code the
> two values? You can try creating a stored procedure that has the two
> values as parameters. That way the optimizer will use the actual values
> passed the first time to create the plan. Does it work with a TOP 250?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hasan O." <hozavalsiz@.gmail.com> wrote in message
> news:esQBHkKgGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||Maybe if you post the actual DDL for the table including all the indexes and
tell us exactly what you are tyring to accomplish we can suggest something
better. Do you want to move any 1 million rows or a specific set? Is this a
one time thing or will it be repeated? Can it be done in off hours?
Andrew J. Kelly SQL MVP
"Hasan O." <hozavalsiz@.gmail.com> wrote in message
news:eJs5eNMgGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi Andrew ,
> thanks for your answer.
> yes index is not clustered . it doesnt work with top 70 .
> I think i am gonna write while loop for selecting , and inserting one by
> one like this
> while @.start_index and < @.end_index
> begin
> 1
> 2 insert into userlogsbackup select * from userlogs where id =
> @.start_index
> 3 delete from userlogs where id = @.start_index
> 4 set @.start_index = @.start_index + 1
> 5
> end
> i am also thinking to use "begin transaction and commit transaction" . Can
> you tell me if which lines transactions contains
> thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uaZyrgLgGHA.4940@.TK2MSFTNGP05.phx.gbl...
>|||>but the select fails unless specifying "top 10" for example.
What is the exact nature of the failure? What error code and message
is returned? Or is it simply taking a long time?
Roy Harvey
Beacon Falls, CT
On Fri, 26 May 2006 12:44:41 +0300, "Hasan O." <hozavalsiz@.gmail.com>
wrote:

>Hi,
>I have a problem about "userlogs" table . this table contains some
>information about users actions .The table has nearly 800.000.000 record in
>it and ~100Gb data , 50GB index.
>i have tried to copy some data ( like 1M records) to a temporary database,
i
>couldnt manage it
>This is the query , the table has a index contains only "id"
>declare @.start_index int, @.end_index int
>set @.start_index = 800000050
>set @.end_index = 800000250
>
>insert into userlogsbackup select * from userlogs where id > @.start_index
>and id < @.end_index
>but the select fails unless specifying "top 10" for example. What else can
>i try ?
>Thanks in advance
>|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:cu3e72d978qc99vfo1mclnqhudtgh31r71@.
4ax.com...
> What is the exact nature of the failure? What error code and message
> is returned? Or is it simply taking a long time?
taking too much time so i cancel the query.
i am using one by one move operation now .
thanks for the answer.

Copying Records into a different table using triggers

I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:

> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:

> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:

>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP

copying a record within the same table...

Hi. I would like to copy some records within the same table. what happens is we get very similar new cases in our office and we want to apply the same information from one client to the new client.

i looked at:
INSERT INTO ClientSpecs (ClientID, Spec1, Spec2, etc.)
SELECT Spec1, Spec2 from ClientSpecs WHERE ClientID=1234
but i want to use the new client id in place of the old client id. is there a way to do this? does this make sense? we're basically copying one client's file to a new client's file, except we need to use the new client's id.INSERT INTO ClientSpecs (ClientID, Spec1, Spec2)
SELECT '123', Spec1, Spec2 from ClientSpecs WHERE ClientID=1234|||thank you for that cfr! i never thought it was going to be that easy. Now, I have another question.

How do I update a Spec from one client to another client. For example, I have

ClientID Spec1 Spec2
1234 1 23
1234 2 12

And I want to copy the first client's Spec2 information to another client.

ClientID Spec1 Spec2
4567 1 NULL
4567 2 NULL

But, I only want to copy the Spec2 information for the client. How would I do this? Does this even make sense?|||Untested:

UPDATE ClientSpecs destTble
SET destTble.Spec2 = (SELECT srcTbl.Spec2
FROM ClientSpecs srcTbl
WHERE srcTbl.ClientID = destTble.ClientID
AND srcTble.Spec1 = destTble.Spec1)
WHERE destTble.ClientID = '4567'

Copying a record from one table to another

What is the easiest way to copy a record from one table to another (with
identical structure)?

RegardsYou mean into a new table? if so you can use select * into newtable from
oldtable (input your where statement)

To input into an existing table

insert into table1 select * from table2 (insert where statement)

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy table record as new record

Hi
I want to create a copy of an existing record in the same table, except with
a different primary key value of course.
This is pretty easy if you specify each in turn like:
INSERT INTO TableA
(
Col1
Col2
...
)
SELECT Col1, Col2, ...
FROM TableA
WHERE PK = @.RecordtoCopy
However, in my situation the database structure is evolving. Therefore, if
the fields in the TableA, above, are changed then the associated SQL script
is no longer valid.
Is there some way of doing the equivalent of:
INSERT INTO TableA
SELECT * FROM TableA
WHERE PK = @.RecordtoCopy
but leaving out the primary key field, which is an identity field.
That is, I don't want to have to specify the column names each time. I
guess I could use 'sycolumns' to construct a query or is there an easier way
?
What I've shown here is a very simplistic query. Mine are much more
complex, and I want to copy all fields from several records in tables formin
g
an hierarchical structure.
Cheers
TimHi Tim
I dont imagine a situation where you need to keep duplicate copy in the same
table.
Can you explain why you want to do that so to as provide some thought on tha
t.
Regards
R.D
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||This is for a cost estimating system where you want to create a new version
of an existing estimate. This would happen when you want to be on a new job
,
which is very similar to one that was previously costed and you want to use
the old one as a template for a new one.
I think I've solved it anyway. The code below is a function that creates an
insert clause as a string.
CREATE FUNCTION dbo.fn_CreateInsert (@.@.Table varchar(50), @.@.PKey
varchar(20)) RETURNS varchar(1000) AS
BEGIN
DECLARE @.InsertClause varchar(1000),
@.@.TableName varchar(255),
@.ColName varchar(255),
@.ColsCursor CURSOR
SET @.ColsCursor = CURSOR FAST_FORWARD
FOR
SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
INNER JOIN (sysobjects INNER JOIN sysusers ON sysobjects.uid =
sysusers.uid)
ON syscolumns.id = sysobjects.id
WHERE sysobjects.name = @.@.Table
OPEN @.ColsCursor
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName, @.ColName
SET @.InsertClause = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.ColName NOT IN (@.@.PKey, 'DateCreated', 'DateModified')
BEGIN
IF @.InsertClause = ''
SET @.InsertClause = '(' + @.ColName
ELSE
SET @.InsertClause = @.InsertClause + ', ' + @.ColName
END
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName,@.ColName
END
CLOSE @.ColsCursor
DEALLOCATE @.ColsCursor
SET @.InsertClause = 'INSERT INTO ' + @.@.Table + ' ' + @.InsertClause + ')'
RETURN @.InsertClause
END
GO
"R.D" wrote:
> Hi Tim
> I dont imagine a situation where you need to keep duplicate copy in the sa
me
> table.
> Can you explain why you want to do that so to as provide some thought on t
hat.
> Regards
> R.D
> "Tim M" wrote:
>|||Tim, try,
-- DDL & sample data
create table dbo.t1
(
keycol int not null identity primary key,
col1 int,
col2 int,
col3 int
);
insert into dbo.t1 values(1, 2, 3);
go
-- usp_copyt1row proc
create proc usp_copyt1row
@.keyval as int
as
declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
set @.col_list = N'';
select @.col_list = @.col_list + ',[' + column_name + N']'
from information_schema.columns
where table_schema = N'dbo'
and table_name = N't1'
and column_name <> N'keycol';
set @.col_list = right(@.col_list, len(@.col_list) - 1);
set @.sql =
N'insert into dbo.t1('
+ @.col_list + N') select '
+ @.col_list + N' from dbo.t1 where keycol = @.keyval'
exec sp_executesql
@.sql, N'@.keyval as int', @.keyval;
go
-- test
exec usp_copyt1row 1;
exec usp_copyt1row 2;
select * from dbo.t1;
-- Output
keycol col1 col2 col3
-- -- -- --
1 1 2 3
2 1 2 3
3 1 2 3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||Thanks for that.
"Itzik Ben-Gan" wrote:
> Tim, try,
> -- DDL & sample data
> create table dbo.t1
> (
> keycol int not null identity primary key,
> col1 int,
> col2 int,
> col3 int
> );
> insert into dbo.t1 values(1, 2, 3);
> go
> -- usp_copyt1row proc
> create proc usp_copyt1row
> @.keyval as int
> as
> declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
> set @.col_list = N'';
> select @.col_list = @.col_list + ',[' + column_name + N']'
> from information_schema.columns
> where table_schema = N'dbo'
> and table_name = N't1'
> and column_name <> N'keycol';
> set @.col_list = right(@.col_list, len(@.col_list) - 1);
> set @.sql =
> N'insert into dbo.t1('
> + @.col_list + N') select '
> + @.col_list + N' from dbo.t1 where keycol = @.keyval'
> exec sp_executesql
> @.sql, N'@.keyval as int', @.keyval;
> go
> -- test
> exec usp_copyt1row 1;
> exec usp_copyt1row 2;
> select * from dbo.t1;
> -- Output
> keycol col1 col2 col3
> -- -- -- --
> 1 1 2 3
> 2 1 2 3
> 3 1 2 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim M" wrote:
>