Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer
.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:[vbcol=seagreen]
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you ca
n
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyz
er.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
>|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:[vbcol=seagreen]
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the tab
le
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
>
Showing posts with label maintain. Show all posts
Showing posts with label maintain. Show all posts
Thursday, March 29, 2012
database import question
Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
Friday, February 24, 2012
Database Design Issue
Hello Friends,
I have to develop a database for website where Advertisements are displayed, Now i have to maintain number of clicks on an Advertisement on hourly basis so that report can be generated. That is number of click on Advertisement 789 between 10 AM and 11 AM.
What i am trying to do now is that i have a seperate tabel for clicks on Ad. where i store Ad_ID and nuber of clicks, and time duration.
Now my challenge is that is i store like that than for every ad there will be 24 rows per day. There can be as many as 1000 ads on that site so there will be 24*1000 rows added per day on that table alone.
Is there a better way of designing it. Because If the database becomes bulky it will take lots of time while retrieving the data.
thanks in advance
saddysanYou might consider putting this table in it's own database.
After each hour the rows for that row will be static so you could have an active table and an archive table. During slow times on the server copy the expired hours to the archive table to keep the active table small. This will give less likelyhood of corruption too.
You could make single rows with 24 fields for the hours but that wouldn't give much benefit and would cause more difficult coding.
Instead of updating the row you could insert an entry in a table then create the aggregate overnight.
This should cause less contention over locks as an update is relatively slow compared to insert.
Your current design will give around 8 million very small static rows per year which isn't too bad.
Just make sure that you create aggregate tables for any queries and don't try to query the active table.|||Do you really want data for each click? Think about what the final goal is. Maybe you can collect / evaluate the data as it comes in and store it in a summary row. Less data, more information--maybe.
--jfp|||Dumping data into a database is not nearly as recource-comsuming as reading data out from the database so you can easily fill it with plenty of rows without meeting any performance-problems. However, when you need your reporst on this you might want to look into some datawarehousing-techniques (i.e. look up OLAP in BOL). And I would also suggest that you only store Ad_ID and the date/time (GETDATE()) it was hit in the database, if you do that then you have all you need. Then you can aggregate the data into day-by-day and hour-by-hour reports say on a weekly or a monthly basis...
I have to develop a database for website where Advertisements are displayed, Now i have to maintain number of clicks on an Advertisement on hourly basis so that report can be generated. That is number of click on Advertisement 789 between 10 AM and 11 AM.
What i am trying to do now is that i have a seperate tabel for clicks on Ad. where i store Ad_ID and nuber of clicks, and time duration.
Now my challenge is that is i store like that than for every ad there will be 24 rows per day. There can be as many as 1000 ads on that site so there will be 24*1000 rows added per day on that table alone.
Is there a better way of designing it. Because If the database becomes bulky it will take lots of time while retrieving the data.
thanks in advance
saddysanYou might consider putting this table in it's own database.
After each hour the rows for that row will be static so you could have an active table and an archive table. During slow times on the server copy the expired hours to the archive table to keep the active table small. This will give less likelyhood of corruption too.
You could make single rows with 24 fields for the hours but that wouldn't give much benefit and would cause more difficult coding.
Instead of updating the row you could insert an entry in a table then create the aggregate overnight.
This should cause less contention over locks as an update is relatively slow compared to insert.
Your current design will give around 8 million very small static rows per year which isn't too bad.
Just make sure that you create aggregate tables for any queries and don't try to query the active table.|||Do you really want data for each click? Think about what the final goal is. Maybe you can collect / evaluate the data as it comes in and store it in a summary row. Less data, more information--maybe.
--jfp|||Dumping data into a database is not nearly as recource-comsuming as reading data out from the database so you can easily fill it with plenty of rows without meeting any performance-problems. However, when you need your reporst on this you might want to look into some datawarehousing-techniques (i.e. look up OLAP in BOL). And I would also suggest that you only store Ad_ID and the date/time (GETDATE()) it was hit in the database, if you do that then you have all you need. Then you can aggregate the data into day-by-day and hour-by-hour reports say on a weekly or a monthly basis...
Subscribe to:
Posts (Atom)