We have some fast growing tables in our SQL Server 2000 db
which content 13 million records already. So, we going to
periodically archive them base on datetime field and allow
user to retrieve archive data via our application by
checking corresponding checkbox. The problem is that, we
do not know what to do with FK's. How to re-create FK's
after archiving to restore referal integrity.
Is there some standard solution?
Any comments will be appreciated.
Merry chistmas and happy new year to everybody.Vitalik
http://vyaskn.tripod.com/sql_archive_data.htm
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:08ee01c3c964$5faa1340$a401280a@.phx.gbl...
> We have some fast growing tables in our SQL Server 2000 db
> which content 13 million records already. So, we going to
> periodically archive them base on datetime field and allow
> user to retrieve archive data via our application by
> checking corresponding checkbox. The problem is that, we
> do not know what to do with FK's. How to re-create FK's
> after archiving to restore referal integrity.
> Is there some standard solution?
> Any comments will be appreciated.
> Merry chistmas and happy new year to everybody.
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Tuesday, March 27, 2012
Archiving logging db
I'm fairly new to SQL. I've inherited a logging db that records the actions taken on a web service. One of the first questions I'm being asked to solve is to explain how I should concatenate the db and save older entries to a archived db.
I'm sure this is a pretty simple action - just identify the date I want to archive from and develop a query that copies all entries from before that time period to a separate db. I'm looking for some guidlines for this kind of action, but there's so much info on SQL out there, I'm having trouble parsing out the noise and finding the answers I'm looking for. Can anyone point me in the right direction, or give me advice?
Thanks
Levi
One way to do this is to create an SSIS package:
http://msdn2.microsoft.com/en-us/library/ms169917.aspx
|||Create a archiving table (Arch_Table) in the same db or different db with same schema.
insert into dbname..Arch_Table
select * from Table where dtColumn <= dateadd(mm, -3, getdate()) -- two archive 3months and older data
while 1 = 1
begin
set rowcount 100 delete 100 rows at a time... if you are using sql 2005 you can use top clause check bol for details
delete from Table where dtColumn <= (select max(dtColumn ) from dbname..Arch_Table )
If @.@.rowcount = 0
Break
end
sqlArchiving Large table
Hi,
I am having problems with archiving. Any help will be greatly appreciated.
I have a table with 12 million records, and it needs to be archived to another table.
Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards.
Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!!
Does anyone have a better idea of doing this? I am really stuck...RE: I am having problems with archiving. Any help will be greatly appreciated. I have a table with 12 million records, and it needs to be archived to another table. Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards. Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!! Does anyone have a better idea of doing this? I am really stuck...
Q1 [It used to take 1 second to process 1000 records. Now, it takes 2 minutes to 14 minutes. Why?]
A1 There may be many different issues, (insufficient information to suggest a reasonably good guess and / or answer).
Q2 [Does anyone have a better, i.e.(FASTER?) idea of doing this?]
A2 Archiving may be accomplished efficiently. What is "Better" really depends on existing overall constraints and designs, available resources, and the details of the circumstances. An example that should be fairly quick (but not overly 'user friendly') would be Archiving a Test table in a Demo DB to an ArchiveDB database table named ArchiveTest.
Demo..Test To ArchiveDB..ArchiveTest
Use Demo
Go
INSERT INTO
[ArchiveDB].[dbo].[ArchiveTest]
([Parent], [Child])
SELECT
[Parent], [Child]
FROM
[Demo].[dbo].[Test]
GO
Alter Database Demo
Set Restricted_User
With
RollBack Immediate
Go
Alter Database Demo
Set Single_User
With
RollBack Immediate
Go
Alter Database Demo
Set Recovery Simple
With
RollBack Immediate
Go
-- drop and recreate, or truncate, delete, etc.
Drop TABLE [Test]
Go
CREATE TABLE [Test] (
[Parent] [varchar] (50) NOT NULL ,
[Child] [varchar] (50) NOT NULL)
Alter Database Demo
Set Recovery Full
With
RollBack Immediate
Go
Alter Database Demo
Set Multi_User
With
RollBack Immediate
Go|||how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.|||Originally posted by Paul Young
how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.
Thank you for your replies,
Actually, there is only primary index with identity on. That's it.
The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table.
Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table.
Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.|||RE: Thank you for your replies, Actually, there is only primary index with identity on. That's it. The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table. Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table. Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.
Q1 [I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse.]
A1 You are probably not updating your indexes at a suitable interval (to ensure optimal performance).
Q2 What can I do to keep the old record + new records sorted at all times?
A2 Cluster both TABLES on the desired column.|||my first inclination is that you have a corrupted index. the overall sorting should not change (aside from changes in data) due to inserting, updateing or deleting data.
During a one week prieod I rebuilt all my index once if not twice on very dynamic tables. Are you doing this?
If your primary index is clustered, you are reordering some part of your data everytime you insert, update or delete. This can lead to slow performance at times. If you must have this index then you just live with it, if you don't need it then change to non-clustered.|||Thank you, Paul Young.
Actually, I never rebuilt indexes on any of the tables. My bad...
What do I have to do to rebuild indexes? I tried DBCC DBREINDEX, and it didn't improve the performance of the archiving.
Can you guide me step-by-step what has to be done?
Thank you again.|||Generally I use maintinance plans to rebuild indexes and statistics along with other things however to answer your question DBCC DBREINDEX will do the trick.
Even if you haven't EVER rebuilt your index(s) they still should produce a result set correctly sorted. Again My hunch is that you have a corrupt index. To fix this you will need to drop the index an re-create it. You can do this while other are using the system but I would NOT advise it.|||One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.|||Thank you, Paul.
I tried to rebuild the index using 'DROP EXISTING'. It took about 5 minutes, and I opened the table, and it still looks messy.
But now, the index seems to be functioning faster. The problem is that, I don't use the primary key as query condition. Usually, my query condition is the 'CreationTime' which gets filled with default values getdate().
Basically, I query all the data created during a time period.
Should I create another index on CreationTime?
Thank you,|||If this table is used in an OLTP environment you want to keep the number of indexes to a minimum because evryting you inset/update/delete a row you also have to update ALL indexes. In your case you only have one index so adding one more shouldn't cause you a noticable slowdown and will GREATLY improve the prformance of your select.
before adding any indexes drop your select statement into Query Analyzer, turn on Show Execution Plan, Show Server Trace and Show Client Statistics and execute your select. Look at the "Execution Plan" tab and you will get a diagram representing what your select is doing.
Next click on Index Tunning Wizard and let SQL server suggest indexes to be built. Concider the suggestions and implament whatever you tinks looks good. Now rerun your select and look at the differences on the "Execution Plan" tab.
All of this is covered in Books Online, an excelent source of info once you know what to look up.|||Q1 Usually, my query condition is the 'CreationTime' Should I create another index on CreationTime?
A1 If you are looking for good performance, Yes. Generally, one wants a (well maintained) index available for the query parser to take advantage of for any column that is frequently queried.
I am having problems with archiving. Any help will be greatly appreciated.
I have a table with 12 million records, and it needs to be archived to another table.
Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards.
Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!!
Does anyone have a better idea of doing this? I am really stuck...RE: I am having problems with archiving. Any help will be greatly appreciated. I have a table with 12 million records, and it needs to be archived to another table. Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards. Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!! Does anyone have a better idea of doing this? I am really stuck...
Q1 [It used to take 1 second to process 1000 records. Now, it takes 2 minutes to 14 minutes. Why?]
A1 There may be many different issues, (insufficient information to suggest a reasonably good guess and / or answer).
Q2 [Does anyone have a better, i.e.(FASTER?) idea of doing this?]
A2 Archiving may be accomplished efficiently. What is "Better" really depends on existing overall constraints and designs, available resources, and the details of the circumstances. An example that should be fairly quick (but not overly 'user friendly') would be Archiving a Test table in a Demo DB to an ArchiveDB database table named ArchiveTest.
Demo..Test To ArchiveDB..ArchiveTest
Use Demo
Go
INSERT INTO
[ArchiveDB].[dbo].[ArchiveTest]
([Parent], [Child])
SELECT
[Parent], [Child]
FROM
[Demo].[dbo].[Test]
GO
Alter Database Demo
Set Restricted_User
With
RollBack Immediate
Go
Alter Database Demo
Set Single_User
With
RollBack Immediate
Go
Alter Database Demo
Set Recovery Simple
With
RollBack Immediate
Go
-- drop and recreate, or truncate, delete, etc.
Drop TABLE [Test]
Go
CREATE TABLE [Test] (
[Parent] [varchar] (50) NOT NULL ,
[Child] [varchar] (50) NOT NULL)
Alter Database Demo
Set Recovery Full
With
RollBack Immediate
Go
Alter Database Demo
Set Multi_User
With
RollBack Immediate
Go|||how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.|||Originally posted by Paul Young
how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.
Thank you for your replies,
Actually, there is only primary index with identity on. That's it.
The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table.
Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table.
Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.|||RE: Thank you for your replies, Actually, there is only primary index with identity on. That's it. The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table. Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table. Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.
Q1 [I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse.]
A1 You are probably not updating your indexes at a suitable interval (to ensure optimal performance).
Q2 What can I do to keep the old record + new records sorted at all times?
A2 Cluster both TABLES on the desired column.|||my first inclination is that you have a corrupted index. the overall sorting should not change (aside from changes in data) due to inserting, updateing or deleting data.
During a one week prieod I rebuilt all my index once if not twice on very dynamic tables. Are you doing this?
If your primary index is clustered, you are reordering some part of your data everytime you insert, update or delete. This can lead to slow performance at times. If you must have this index then you just live with it, if you don't need it then change to non-clustered.|||Thank you, Paul Young.
Actually, I never rebuilt indexes on any of the tables. My bad...
What do I have to do to rebuild indexes? I tried DBCC DBREINDEX, and it didn't improve the performance of the archiving.
Can you guide me step-by-step what has to be done?
Thank you again.|||Generally I use maintinance plans to rebuild indexes and statistics along with other things however to answer your question DBCC DBREINDEX will do the trick.
Even if you haven't EVER rebuilt your index(s) they still should produce a result set correctly sorted. Again My hunch is that you have a corrupt index. To fix this you will need to drop the index an re-create it. You can do this while other are using the system but I would NOT advise it.|||One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.|||Thank you, Paul.
I tried to rebuild the index using 'DROP EXISTING'. It took about 5 minutes, and I opened the table, and it still looks messy.
But now, the index seems to be functioning faster. The problem is that, I don't use the primary key as query condition. Usually, my query condition is the 'CreationTime' which gets filled with default values getdate().
Basically, I query all the data created during a time period.
Should I create another index on CreationTime?
Thank you,|||If this table is used in an OLTP environment you want to keep the number of indexes to a minimum because evryting you inset/update/delete a row you also have to update ALL indexes. In your case you only have one index so adding one more shouldn't cause you a noticable slowdown and will GREATLY improve the prformance of your select.
before adding any indexes drop your select statement into Query Analyzer, turn on Show Execution Plan, Show Server Trace and Show Client Statistics and execute your select. Look at the "Execution Plan" tab and you will get a diagram representing what your select is doing.
Next click on Index Tunning Wizard and let SQL server suggest indexes to be built. Concider the suggestions and implament whatever you tinks looks good. Now rerun your select and look at the differences on the "Execution Plan" tab.
All of this is covered in Books Online, an excelent source of info once you know what to look up.|||Q1 Usually, my query condition is the 'CreationTime' Should I create another index on CreationTime?
A1 If you are looking for good performance, Yes. Generally, one wants a (well maintained) index available for the query parser to take advantage of for any column that is frequently queried.
Archiving database for reporting
HI,
I have a database on sql server 2005 ent edition with about 300 000
new records every day. I must keep data in production database about
two months, but there are a lot of reporting activities which are
interrupting normal functioning of production server and there is also
requirement to query historical data(about 1 year) with reports. I
must add new data to the archive database every day. Additionally two
months old data in the production database can change (delete, update)
and that also must be reflected in archive database. So basically I
must insert new data, update changed date and delete deleted data from
production database into archive database but also keep about 1 year
of data in archive database. Then I can separately optimize archive
database for reporting. What is the best way to satisfy all that
requirements. I am thinking about SSIS.SSIS will work. You could also consider a small trigger on the main table
that will put the key values into a holding table when
inserts/updates/deletes occur so you can very quickly make the necessary
changes to the historical records when <2mth old data is modified.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())
>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||On Feb 7, 9:03=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
> >also keep about 1 year
> > of data in archive database.
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> > I must keep data in production database about
> > two months, b
> =A0 =A0 Create a job and schedule it on day period
> =A0I must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AN=D
> dt<=3DDATEADD(month,-2,GETDATE())
> >Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database.
> Here, I'd suggest =A0you to create a trigger or (take a look at OUTPUT cla=use)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
> > HI,
> > I have a database on sql server 2005 ent edition with about 300 000
> > new records every day. I must keep data in production database about
> > two months, but there are a lot of reporting activities which are
> > interrupting normal functioning of production server and there is also
> > requirement to query historical data(about 1 year) with reports. I
> > must add new data to the archive database every day. Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database. So basically I
> > must insert new data, update changed date and delete deleted data from
> > production database into archive database but also keep about 1 year
> > of data in archive database. Then I can separately optimize archive
> > database for reporting. What is the best way to satisfy all that
> > requirements. I am thinking about SSIS.- Hide quoted text -
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.
>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AND=
>dt<=3DDATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.
I have a database on sql server 2005 ent edition with about 300 000
new records every day. I must keep data in production database about
two months, but there are a lot of reporting activities which are
interrupting normal functioning of production server and there is also
requirement to query historical data(about 1 year) with reports. I
must add new data to the archive database every day. Additionally two
months old data in the production database can change (delete, update)
and that also must be reflected in archive database. So basically I
must insert new data, update changed date and delete deleted data from
production database into archive database but also keep about 1 year
of data in archive database. Then I can separately optimize archive
database for reporting. What is the best way to satisfy all that
requirements. I am thinking about SSIS.SSIS will work. You could also consider a small trigger on the main table
that will put the key values into a holding table when
inserts/updates/deletes occur so you can very quickly make the necessary
changes to the historical records when <2mth old data is modified.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())
>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||On Feb 7, 9:03=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
> >also keep about 1 year
> > of data in archive database.
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> > I must keep data in production database about
> > two months, b
> =A0 =A0 Create a job and schedule it on day period
> =A0I must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AN=D
> dt<=3DDATEADD(month,-2,GETDATE())
> >Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database.
> Here, I'd suggest =A0you to create a trigger or (take a look at OUTPUT cla=use)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
> > HI,
> > I have a database on sql server 2005 ent edition with about 300 000
> > new records every day. I must keep data in production database about
> > two months, but there are a lot of reporting activities which are
> > interrupting normal functioning of production server and there is also
> > requirement to query historical data(about 1 year) with reports. I
> > must add new data to the archive database every day. Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database. So basically I
> > must insert new data, update changed date and delete deleted data from
> > production database into archive database but also keep about 1 year
> > of data in archive database. Then I can separately optimize archive
> > database for reporting. What is the best way to satisfy all that
> > requirements. I am thinking about SSIS.- Hide quoted text -
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.
>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AND=
>dt<=3DDATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql
Sunday, March 25, 2012
Archive data before deletion
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris
Sunday, March 11, 2012
Apply policy to restrict records
Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regards
CC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards
|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>
>.
>
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regards
CC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards
|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>
>.
>
Apply policy to restrict records
Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>> Hello,
>> It is possible to restrict the records viewed by an user
>> using policies instead of creating a view?
>> If it is possible, how can i do it?
>> Best regards
>
>.
>
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>> Hello,
>> It is possible to restrict the records viewed by an user
>> using policies instead of creating a view?
>> If it is possible, how can i do it?
>> Best regards
>
>.
>
Apply policy to restrict records
Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx
.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cead01c45399$fdffadb0$a301280a@.phx
.gbl...
>
>.
>
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx
.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cead01c45399$fdffadb0$a301280a@.phx
.gbl...
>
>.
>
Wednesday, March 7, 2012
application retrieve same records, sometimes fast, sometimes extreme slow
Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florence
Could it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>
|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message[vbcol=seagreen]
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
it
>
>.
>
|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> of isolation levels?
> the next time your
> its acceptable by your
> take that long, so you
> cache when the app
> Lite, free!
> in message
> it
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florence
Could it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>
|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message[vbcol=seagreen]
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
it
>
>.
>
|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> of isolation levels?
> the next time your
> its acceptable by your
> take that long, so you
> cache when the app
> Lite, free!
> in message
> it
application retrieve same records, sometimes fast, sometimes extreme slow
Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florenceCould it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
>> Hi,
>> Sometimes, in my application, when i browse a
>> record, it takes very fast 1/2 seconds but sometimes
it
>> seems like hang there and take a very long time around
>> 10 minutes to finish,could it be deadlock ? Any other
>> suggestion?
>> How to check and prevent from deadlock?
>> thanks.
>> regards,
>> florence
>
>.
>|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> >--Original Message--
> >Could it be possible you are blocked due to your choice
> of isolation levels?
> >You could run sp_who and see if anyone is blocking you
> the next time your
> >app slows down. You could set to READ UNCOMMITTED if
> its acceptable by your
> >app. Then again, a commit by another app should not
> take that long, so you
> >might want to look at fixing the other app instead.
> >
> >Another possibility is that the data was in the buffer
> cache when the app
> >was fast.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> >"florencelee" <florencelee@.visualsolutions.com.my> wrote
> in message
> >news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> >> Hi,
> >> Sometimes, in my application, when i browse a
> >> record, it takes very fast 1/2 seconds but sometimes
> it
> >> seems like hang there and take a very long time around
> >> 10 minutes to finish,could it be deadlock ? Any other
> >> suggestion?
> >>
> >> How to check and prevent from deadlock?
> >>
> >> thanks.
> >>
> >> regards,
> >>
> >> florence
> >>
> >
> >
> >.
> >
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florenceCould it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
>> Hi,
>> Sometimes, in my application, when i browse a
>> record, it takes very fast 1/2 seconds but sometimes
it
>> seems like hang there and take a very long time around
>> 10 minutes to finish,could it be deadlock ? Any other
>> suggestion?
>> How to check and prevent from deadlock?
>> thanks.
>> regards,
>> florence
>
>.
>|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> >--Original Message--
> >Could it be possible you are blocked due to your choice
> of isolation levels?
> >You could run sp_who and see if anyone is blocking you
> the next time your
> >app slows down. You could set to READ UNCOMMITTED if
> its acceptable by your
> >app. Then again, a commit by another app should not
> take that long, so you
> >might want to look at fixing the other app instead.
> >
> >Another possibility is that the data was in the buffer
> cache when the app
> >was fast.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> >"florencelee" <florencelee@.visualsolutions.com.my> wrote
> in message
> >news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> >> Hi,
> >> Sometimes, in my application, when i browse a
> >> record, it takes very fast 1/2 seconds but sometimes
> it
> >> seems like hang there and take a very long time around
> >> 10 minutes to finish,could it be deadlock ? Any other
> >> suggestion?
> >>
> >> How to check and prevent from deadlock?
> >>
> >> thanks.
> >>
> >> regards,
> >>
> >> florence
> >>
> >
> >
> >.
> >
Friday, February 24, 2012
Application for "Replaying" a Table
I'm on a quest to find a way to "replay" a selected time range of records
from a table. The purpose is to simulate the pace and content of the records
in the table exactly as they were parsed during run-time. The ideal solution
would read records from a source table and write them to a destination table
as per a specified pace, i.e clock speed, 1/2 clock speed, 4x clock speed.
Just looking to see if there's something available currently before I jump
in and develop my own solution.
Thanks,
ChrisYou can get about 85% of what you want from SQL Profiler, although it doesn't quite get you there without some help in the form of a small app... You might even be able to do this with a SQL script, but that would be much more of a challenge.
-PatP|||SQL Profiler isn't going to help him replay the activity. He wants some kind of load simulator.|||Yeah, a tool something like LoadRunner (http://www.mercury.com/us/products/performance-center/loadrunner/) would be ideal, but you can get better than 85% of the way there using SQL Profiler to record the data into a table, then a small app to play the captured activity into another database. Not everybody has deep enough pockets to make load testing tools a snap decision... Most of us need to budget for them, sometimes for a couple of years before we get the cool toys to play with. Having a simple kludge like I've described to show what you can do with this kind of tool makes it a lot easier to sell!
-PatP
from a table. The purpose is to simulate the pace and content of the records
in the table exactly as they were parsed during run-time. The ideal solution
would read records from a source table and write them to a destination table
as per a specified pace, i.e clock speed, 1/2 clock speed, 4x clock speed.
Just looking to see if there's something available currently before I jump
in and develop my own solution.
Thanks,
ChrisYou can get about 85% of what you want from SQL Profiler, although it doesn't quite get you there without some help in the form of a small app... You might even be able to do this with a SQL script, but that would be much more of a challenge.
-PatP|||SQL Profiler isn't going to help him replay the activity. He wants some kind of load simulator.|||Yeah, a tool something like LoadRunner (http://www.mercury.com/us/products/performance-center/loadrunner/) would be ideal, but you can get better than 85% of the way there using SQL Profiler to record the data into a table, then a small app to play the captured activity into another database. Not everybody has deep enough pockets to make load testing tools a snap decision... Most of us need to budget for them, sometimes for a couple of years before we get the cool toys to play with. Having a simple kludge like I've described to show what you can do with this kind of tool makes it a lot easier to sell!
-PatP
Appending Records to the Existing MS SQL EXPRESS SERVER Table.
Dear All,
I am Using MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site.
Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,
Now I want to append 1 more access Table(5500 records) to the existing table having same fields.
How to do this.Can any body tell me?
Thanks and Regards
mukesh
Hi mukesh,
You can append the imported records into the existing table using SQL Server import & Export Wizard.
1.Select Your database from SQL Server Management Studio
2.Right Click on Database and go to the Task->Import Data menu Item
3. SQL Server Import & Export Wizard will be open., choose ur data source. as MicrosoftAccess, and select the MDB file
4.Press Next to Move onChoose a Destination Page, and select your Database Name from dropdown
5.Press Next to Move onSpecity Table Copy or QueryPage, and selectcopy data from one or more tables or viewradio button.
6. Press Next to Move onSelect Source Table and View , Select your Source Table , and Destination Table and then pressEdit button,Column Mappings dialog box will be open, chooseAppend rows to the destination tabelradion button option ( it will append the new records with existing records, in your case ur new 5500 records will be apended with existing 10,000 records )
7Press Next, and then Press Finish.Import Process will be started.
Thanks
Best Regards,
Muhammad AKhtar Shiekh
SQL Server Import & Export Wizard
|||Hi mukesh,
The way you imported the first table just load the second table but with a different name i.e Table2 to the same database and then you can use the query
Lets Table_1 is having 10000
Table_2 is having 5500
----------------
Insert into Table_1
Select * from Table_2
----------------
the simplest way to do the stuff...
Satya
Thanks Mr.Akhhttar
But sir in management studio I can't find "Import and Export option" there r these option,"detach","shrink","backup","restore"& generate scripts.
thanks and regards
mukesh
|||Thanks a lot,Mr.Satya
Sunday, February 19, 2012
Appending Records + Update
Hello, I have a need to refresh a table called Employees each day from Activ
e
Directory. This table will be used in a training database. When employees
terminate, they disappear from AD. So I would like to run a query that woul
d
select all of today's employees, and selectively append to the Employees
table. New employees would be added. If the employee no longer exists in
the AD data source, I'd like to set a value from 1 to 0 in the Active column
,
indicating that the employee is no longer active but keeping the record for
historical searches. Any suggestions would be appreciated. Thanks, Pancho.Hi
Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
compare Employees
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
> Hello, I have a need to refresh a table called Employees each day from
> Active
> Directory. This table will be used in a training database. When
> employees
> terminate, they disappear from AD. So I would like to run a query that
> would
> select all of today's employees, and selectively append to the Employees
> table. New employees would be added. If the employee no longer exists in
> the AD data source, I'd like to set a value from 1 to 0 in the Active
> column,
> indicating that the employee is no longer active but keeping the record
> for
> historical searches. Any suggestions would be appreciated. Thanks,
> Pancho.|||Sorry
Should be IF EXISTS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eM2JtevUGHA.5108@.tk2msftngp13.phx.gbl...
> Hi
> Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
> compare Employees
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
>|||On Tue, 28 Mar 2006 08:50:01 -0800, Pancho wrote:
>Hello, I have a need to refresh a table called Employees each day from Acti
ve
>Directory. This table will be used in a training database. When employees
>terminate, they disappear from AD. So I would like to run a query that wou
ld
>select all of today's employees, and selectively append to the Employees
>table. New employees would be added. If the employee no longer exists in
>the AD data source, I'd like to set a value from 1 to 0 in the Active colum
n,
>indicating that the employee is no longer active but keeping the record for
>historical searches. Any suggestions would be appreciated. Thanks, Pancho.[/color
]
Hi Pancho,
UPDATE Employees
SET Active = 0
WHERE Active = 1
AND NOT EXISTS
(SELECT *
FROM AD
WHERE AD.KeyColumn = Employees.KeyColumn)
--
INSERT INTO Employees (KeyColumn, OtherColumn, Active)
SELECT KeyColumn, OtherColumn, 1
FROM AD
WHERE NOT EXISTS
(SELECT *
FROM Employees
WHERE AD.KeyColumn = Employees.KeyColumn)
Hugo Kornelis, SQL Server MVP
e
Directory. This table will be used in a training database. When employees
terminate, they disappear from AD. So I would like to run a query that woul
d
select all of today's employees, and selectively append to the Employees
table. New employees would be added. If the employee no longer exists in
the AD data source, I'd like to set a value from 1 to 0 in the Active column
,
indicating that the employee is no longer active but keeping the record for
historical searches. Any suggestions would be appreciated. Thanks, Pancho.Hi
Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
compare Employees
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
> Hello, I have a need to refresh a table called Employees each day from
> Active
> Directory. This table will be used in a training database. When
> employees
> terminate, they disappear from AD. So I would like to run a query that
> would
> select all of today's employees, and selectively append to the Employees
> table. New employees would be added. If the employee no longer exists in
> the AD data source, I'd like to set a value from 1 to 0 in the Active
> column,
> indicating that the employee is no longer active but keeping the record
> for
> historical searches. Any suggestions would be appreciated. Thanks,
> Pancho.|||Sorry
Should be IF EXISTS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eM2JtevUGHA.5108@.tk2msftngp13.phx.gbl...
> Hi
> Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
> compare Employees
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
>|||On Tue, 28 Mar 2006 08:50:01 -0800, Pancho wrote:
>Hello, I have a need to refresh a table called Employees each day from Acti
ve
>Directory. This table will be used in a training database. When employees
>terminate, they disappear from AD. So I would like to run a query that wou
ld
>select all of today's employees, and selectively append to the Employees
>table. New employees would be added. If the employee no longer exists in
>the AD data source, I'd like to set a value from 1 to 0 in the Active colum
n,
>indicating that the employee is no longer active but keeping the record for
>historical searches. Any suggestions would be appreciated. Thanks, Pancho.[/color
]
Hi Pancho,
UPDATE Employees
SET Active = 0
WHERE Active = 1
AND NOT EXISTS
(SELECT *
FROM AD
WHERE AD.KeyColumn = Employees.KeyColumn)
--
INSERT INTO Employees (KeyColumn, OtherColumn, Active)
SELECT KeyColumn, OtherColumn, 1
FROM AD
WHERE NOT EXISTS
(SELECT *
FROM Employees
WHERE AD.KeyColumn = Employees.KeyColumn)
Hugo Kornelis, SQL Server MVP
Appending data to a table but not duplicates
Hiya everyone,
I have two tables in SQL 2000. I would like to append the contents of
TableA to TableB.
Table A has around 1.1 Million Records.
Table B has around 1 Million Reocords.
Basically TableA has all of the data held in TableB plus 100,000
additional records. I would only like to import or append these new
additional records. I have a unique index already setup on Table B.
Any ideas pretty pretty please?
Paul.
Ps. (Have been messing around with DTS but get a unique violation error
- Which is kinda what I want I guess, but would like SQL to ignore the
error and only copy the new data - if only)<paul@.domainscanners.com> wrote in message
news:1119371481.949023.149970@.g43g2000cwa.googlegr oups.com...
> Hiya everyone,
> I have two tables in SQL 2000. I would like to append the contents of
> TableA to TableB.
> Table A has around 1.1 Million Records.
> Table B has around 1 Million Reocords.
> Basically TableA has all of the data held in TableB plus 100,000
> additional records. I would only like to import or append these new
> additional records. I have a unique index already setup on Table B.
> Any ideas pretty pretty please?
> Paul.
> Ps. (Have been messing around with DTS but get a unique violation error
> - Which is kinda what I want I guess, but would like SQL to ignore the
> error and only copy the new data - if only)
insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol = b.keycol)
Simon|||Simon,
how is your sql different from
insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where keycol in (select keycol from dbo.tableb)
??
TIA
Rob|||rcamarda (rcamarda@.cablespeed.com) writes:
> Simon,
> how is your sql different from
> insert into dbo.TableB
> (col1, col2, col3,...)
> select col1, col2, col3...
> from dbo.TableA a
> where keycol in (select keycol from dbo.tableb)
That's one hell of a difference - you are inserting the duplicates only. :-)
But, OK, put in the NOT, and your query is the same as Simon's. In SQL 6.5
there was a difference in performance, NOT IN usually executed slower. I
think that in SQL 2000, the optimizer rewrites the query internally.
Anyway, there is still an advantage with the style that Simon used.
Consider this query:
insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol1 = b.keycol1
and a.keycol2 = B.keycol2)
That is not easily recast to NOT IN.
So NOT EXISTS is simply an operation you need to master.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||er. sorry. missed the not. I'm interesting in "exists" vs. in (select
... )
so. are you saying that the advantage is when you are looking for
something that does not exist? Otherwise "..exists (select .." is the
same as "in (select ..." ?|||rcamarda (rcamarda@.cablespeed.com) writes:
> er. sorry. missed the not. I'm interesting in "exists" vs. in (select
> ... )
> so. are you saying that the advantage is when you are looking for
> something that does not exist? Otherwise "..exists (select .." is the
> same as "in (select ..." ?
Same thing there, EXISTS is the only that works when your condition
is more than a single column. There is also a gotcha there are NULL
values involved.
It's partly a matter of style, but I use (NOT) EXISTS far more often
then (NOT) IN. (With subqueries, that is. (NOT) IN a list of values
is another matter.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes - for a NOT IN condition, it's almost always better to use NOT
EXISTS. The main issue, as Erland pointed out, is the possibility of a
NULL in the subquery. Consider this simplified example:
IF 1 IN (1, 2, 3, NULL) PRINT 'True'
Obviously the condition is TRUE, but now consider this:
IF 1 NOT IN (2, 3, NULL) PRINT 'True'
Now we don't know if the condition is TRUE or not - the NULL has an
unknown value, so in principle it could be a 1, and therefore the whole
condition evaluates to UNKNOWN. So in the case of a correlated
subquery, any NULLs in the subquery mean that the whole query returns
no rows. Using NOT EXISTS avoids this trap.
Admittedly, you often use primary key columns in the correlation, so
there could never be a NULL in the subquery, but I think it's better to
have a 'safer' habit of using NOT EXISTS. And as Erland also mentioned,
there is some personal taste involved - I find that EXISTS/NOT EXISTS
expresses the intention of the query more clearly, especially when
someone is quickly looking through the code.
Simon|||Thanks Guys!|||Hi Guys,
Thanks very much for your answers to my questions. I ran the query you
supplied and it worked fine although I now have another problem and was
wondering if you would be able to helpo me out again?
Basically I now have a table containing all the data I need but the new
data has left a gap in the Identity column that im using.
Basically the original data's identity column went up to 1,000,000. I
was hoping that the new data that was appended would be inserted as
1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
However the new appended data went in as 1,254,324 then 1,254,325 etc.
Is there a command I can run to resnycronise my identity column? so
that the ID's run smoothly from 0 through to 1,1000,000?
Hope you can help me out again,
Paul.|||(paul@.domainscanners.com) writes:
> Basically the original data's identity column went up to 1,000,000. I
> was hoping that the new data that was appended would be inserted as
> 1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
> However the new appended data went in as 1,254,324 then 1,254,325 etc.
> Is there a command I can run to resnycronise my identity column? so
> that the ID's run smoothly from 0 through to 1,1000,000?
If you want contiguous ids, or at least control over them, don't
use the IDENTITY property. When you attempt to insert a row into
a table with the IDENTITY property, you consume one number, even if
the INSERT fails. This may seem stupid, but it is actually a feature,
because it speeds up concurrency. If the number would be reused in
case of failure, SQL Server would need to lock the number, and no
other process had been able to insert until the INSERT have completed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Append to a field in a database
Hey guys,
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?In update statement you should use:
Personally, I would create a notes table, and add records. This way, you can also store things like date/time, user id, etc. You can then just display them in sequential order. I do this with ASP all the time. Just did it yesterday, as a matter of fact. The client loved it.
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?In update statement you should use:
Update table_name set
column_name = column_name + new_data
where ....|||
Quote:
Originally Posted by nikhil86
Hey guys,
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?
Personally, I would create a notes table, and add records. This way, you can also store things like date/time, user id, etc. You can then just display them in sequential order. I do this with ASP all the time. Just did it yesterday, as a matter of fact. The client loved it.
Good luck,
Michael C. Gates
Thursday, February 16, 2012
Append query from Access Table to Linked SQL Server Table Failing
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
BradI'd guess that the SQL Server db you think you are linking to in dbo_Colors isn't really the one you think. Perhaps the login/password in your datasource is connecting to a different database than the one you expect?
To check, get the name of the constraint being violated and check in the sql server table to see if that constraint exists
Also, try running the sql server profiler to see what sql server db ms access is trying to insert data into|||mattrevs,
It does appear that I am linking the right table. There is only one Colors table and only one database that has a colors table.
Could you tell me how to check which constraint is being violated? After I manually pasted the data into dbo_Colors, I ran checkconstraints() and no error were reported.
I also tried running the query with implicit_transaction OFF and still no joy.
I ran the trace and here are the last two lines. The first one was simply duplicated for each record:
RPC:Completed exec sp_executesql N'INSERT INTO "dbo"."Colors" ("NameColorID","Application","Red","Green","Blue") VALUES (@.P1,@.P2,@.P3,@.P4,@.P5)', N'@.P1 nvarchar(38),@.P2 nvarchar(30),@.P3 int,@.P4 int,@.P5 int', N'{FFC28EAD-1134-40BB-9723-7D88A0B0AC7A}', N'Tile1', 197, 183, 156 Microsoft Access sa 0 11 0 0 2564 54 2004-06-21 10:35:14.170
SQL:BatchCompleted IF @.@.TRANCOUNT > 0 ROLLBACK TRAN Microsoft Access sa 0 0 0 0 2564 54 2004-06-21 10:35:19.403
- Brad|||You say that pasting the data one row at a time from within ms access works ok?
If so, maybe you could also perform a sql trace on this and see what is different?|||I got it figured out. (BTW, I did the trace with the pasting but the results were ... confusing)
Apparently a bit field in SQL server can be Null?
There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.
When I checked Allow Nulls on the bit field and re-linked, my query worked.
I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.
Thanks for your help,
Brad
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
BradI'd guess that the SQL Server db you think you are linking to in dbo_Colors isn't really the one you think. Perhaps the login/password in your datasource is connecting to a different database than the one you expect?
To check, get the name of the constraint being violated and check in the sql server table to see if that constraint exists
Also, try running the sql server profiler to see what sql server db ms access is trying to insert data into|||mattrevs,
It does appear that I am linking the right table. There is only one Colors table and only one database that has a colors table.
Could you tell me how to check which constraint is being violated? After I manually pasted the data into dbo_Colors, I ran checkconstraints() and no error were reported.
I also tried running the query with implicit_transaction OFF and still no joy.
I ran the trace and here are the last two lines. The first one was simply duplicated for each record:
RPC:Completed exec sp_executesql N'INSERT INTO "dbo"."Colors" ("NameColorID","Application","Red","Green","Blue") VALUES (@.P1,@.P2,@.P3,@.P4,@.P5)', N'@.P1 nvarchar(38),@.P2 nvarchar(30),@.P3 int,@.P4 int,@.P5 int', N'{FFC28EAD-1134-40BB-9723-7D88A0B0AC7A}', N'Tile1', 197, 183, 156 Microsoft Access sa 0 11 0 0 2564 54 2004-06-21 10:35:14.170
SQL:BatchCompleted IF @.@.TRANCOUNT > 0 ROLLBACK TRAN Microsoft Access sa 0 0 0 0 2564 54 2004-06-21 10:35:19.403
- Brad|||You say that pasting the data one row at a time from within ms access works ok?
If so, maybe you could also perform a sql trace on this and see what is different?|||I got it figured out. (BTW, I did the trace with the pasting but the results were ... confusing)
Apparently a bit field in SQL server can be Null?
There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.
When I checked Allow Nulls on the bit field and re-linked, my query worked.
I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.
Thanks for your help,
Brad
Append only unique records in SQL table
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.
The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?
Thanks in advance.
SMc
Lets say your destination table is called Dest and you temporary table is called Temp.
Both tables have two fields, A and B where A is the one that needs to be unique in Dest, but it's not necesarily unique in Temp (or Temp contains rows with a value of A that already exists in Dest).
insertinto Dest
select Temp.A, Temp.Bfrom Tempwhere Temp.Anotin(select Dest.Afrom Dest)
||| I was just fighting with similar, but a lot of simpler problem. Maybe these posts will help.
http://forums.asp.net/t/1185782.aspx
And IF NOT EXISTS(SELECT * FROM table WHERE .....
Hope it helps
Leif
Subscribe to:
Posts (Atom)