Copy data from one MS SQL database to another

Hi
The old database layout is very slow on queries so I have created a new
layout and want to copy the data from the old database into the new layout.
How can this best be done?
Do I need an application do this or can it be done by the SQL Server running
a stored procedure located in one of the databases?
Can you give me an example of a script copying data from one table in one
database to a table in another database?
Most of the tables are the same as before but there is one table that have
been normalized. Text fields have been moved to their own tables and
referenced from the main table.
Thank You for your help.
Regards
Kjell Arne Johansenyou can simply use
Insert into database1.DBO.newtable
(Select * from database2.dbo.oldtable )
before this be sure that your Newtable is empty or does not contain
duplicated rows
hch
"Kjell Arne Johansen" wrote:

> Hi
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new layout
.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server runni
ng
> a stored procedure located in one of the databases?
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
> Most of the tables are the same as before but there is one table that have
> been normalized. Text fields have been moved to their own tables and
> referenced from the main table.
> Thank You for your help.
> Regards
> Kjell Arne Johansen
>
>|||Hello Kjell
To copy the data, probably the best thing to do is use Data Transformation
Services from within Enterprise Manager. Have you also considered adding
indexes to the old database instead of creating a new one. Also, is your
database setup to automatically update statistics? If the database has been
in use for a while, the statistics that the server uses for your database
may be out of date which may be causing the slow queries.
HTH
D.
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:SXihe.10020$SL4.226519@.news4.e.nsc.no...
> Hi
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new
> layout.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server
> running a stored procedure located in one of the databases?
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
> Most of the tables are the same as before but there is one table that have
> been normalized. Text fields have been moved to their own tables and
> referenced from the main table.
> Thank You for your help.
> Regards
> Kjell Arne Johansen
>|||Hi
My company deliver process control systems and a part of this system is
logging of alarms and events to an SQL alarms and events database.
We have just moved from Access to SQL Server and may have some tuning and
architecture problems, I think..
There is one application writing to the database and several applications
that will read from the database.
Existing records in the database will not be edited but new one will be
added as soon there is a not normal situation in the process control.
The old database layout is not fully normalized. Many of the text fields
can be moved to their own tables.
I am experimenting with tables, relations and indexes in different new
layout to make the database so fast as possible both for writinig and
reading.
It is very important that the writer application always have access to the
database. The main table must not be locked for a longer time be the
readers.
Also the readers must have fast access to the data in the database even when
the main table grow to houndred millions of records.
To make the testing as complite as possible the database must be filled with
relevant data. I have got a database from one of our customers where the
main table has several million records with relevant data from their
process. I want to copy these data into a now layout and test reading and
writing.
The requrement is that it shall be possible to write bursts of data into the
database, up to 8000 records a minute.
This will not be the normal situation. In a good tuned process control
there will be less than one record each ten minute.
-But during configuration and equipment tuning there will be a lot of
situations that will cause an alarm or event.
I will try to write 2000 - 8000 alarms and events each minute to the
database, at same time as I am reading records from the database filtering
on different fields.
The repsons from the SQL Server shall be fast and the load on the SQL Server
shall not be high.
"Fast" and "High" is not fully defined yet. What is a fast respons, what
is high load on the SQL Server?
Typically I can say that the user will not want to wait for a respons for
more than 10 seconds.
The load on the SQL Server should not be so high that the writing
application does not get access to it.
I think normailizing the the database by putting fields that will have much
duplicated data into their own tables should be done.
The clustered primary key in the main table will be a combination of an auto
number desc, time desc, milliseconds desc, sequence desc, station desc.
I do not know about database statistics setup. Is this important?
Regards
Kjell Arne Johansen
"DJP" <djp@.snotmail.com> skrev i melding
news:pNkhe.2481$E7.1770@.news-server.bigpond.net.au...
> Hello Kjell
> To copy the data, probably the best thing to do is use Data Transformation
> Services from within Enterprise Manager. Have you also considered adding
> indexes to the old database instead of creating a new one. Also, is your
> database setup to automatically update statistics? If the database has
> been in use for a while, the statistics that the server uses for your
> database may be out of date which may be causing the slow queries.
> HTH
> D.
> "Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
> news:SXihe.10020$SL4.226519@.news4.e.nsc.no...
>|||Sounds like you just need to hire a competent databasedesigner to work with
you for a few days to get the schema correct. What you are asking SQL
Server to do is quite reasonable and should not be a problem with proper
normalization and indexing. Your PK sounds a bit much. If you have an
autonumber then it should be unique on its own. And SQL Server only has a
Datetime datatype which includes values down to the millisecond so you don't
need several columns for this.
Andrew J. Kelly SQL MVP
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:lllhe.9690$ai7.234663@.news2.e.nsc.no...
> Hi
> My company deliver process control systems and a part of this system is
> logging of alarms and events to an SQL alarms and events database.
> We have just moved from Access to SQL Server and may have some tuning and
> architecture problems, I think..
> There is one application writing to the database and several applications
> that will read from the database.
> Existing records in the database will not be edited but new one will be
> added as soon there is a not normal situation in the process control.
> The old database layout is not fully normalized. Many of the text fields
> can be moved to their own tables.
> I am experimenting with tables, relations and indexes in different new
> layout to make the database so fast as possible both for writinig and
> reading.
> It is very important that the writer application always have access to the
> database. The main table must not be locked for a longer time be the
> readers.
> Also the readers must have fast access to the data in the database even
> when the main table grow to houndred millions of records.
> To make the testing as complite as possible the database must be filled
> with relevant data. I have got a database from one of our customers where
> the main table has several million records with relevant data from their
> process. I want to copy these data into a now layout and test reading and
> writing.
> The requrement is that it shall be possible to write bursts of data into
> the database, up to 8000 records a minute.
> This will not be the normal situation. In a good tuned process control
> there will be less than one record each ten minute.
> -But during configuration and equipment tuning there will be a lot of
> situations that will cause an alarm or event.
> I will try to write 2000 - 8000 alarms and events each minute to the
> database, at same time as I am reading records from the database filtering
> on different fields.
> The repsons from the SQL Server shall be fast and the load on the SQL
> Server shall not be high.
> "Fast" and "High" is not fully defined yet. What is a fast respons, what
> is high load on the SQL Server?
> Typically I can say that the user will not want to wait for a respons for
> more than 10 seconds.
> The load on the SQL Server should not be so high that the writing
> application does not get access to it.
> I think normailizing the the database by putting fields that will have
> much duplicated data into their own tables should be done.
> The clustered primary key in the main table will be a combination of an
> auto number desc, time desc, milliseconds desc, sequence desc, station
> desc.
> I do not know about database statistics setup. Is this important?
> Regards
> Kjell Arne Johansen
>
> "DJP" <djp@.snotmail.com> skrev i melding
> news:pNkhe.2481$E7.1770@.news-server.bigpond.net.au...
>|||Hi
Yes, I know that the autonumber is unique. The millisecond field is also
microseconds from our process stations.
I need an index with the combination of time, and two other fields. An
Oracle database man in our company says I should make them clustered.
It looks like You can have only one clustered index in the table. Should
the PK be the clustered index or can I decide to use the combination of
fields as a clustered index?
I am concidering hiring a database expert haveing a look at our existing
layout.
Thank You.
Regards
Kjell Arne Johansen
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i melding
news:ODz6QoIWFHA.3840@.tk2msftngp13.phx.gbl...
> Sounds like you just need to hire a competent databasedesigner to work
> with you for a few days to get the schema correct. What you are asking
> SQL Server to do is quite reasonable and should not be a problem with
> proper normalization and indexing. Your PK sounds a bit much. If you
> have an autonumber then it should be unique on its own. And SQL Server
> only has a Datetime datatype which includes values down to the millisecond
> so you don't need several columns for this.
> --
> Andrew J. Kelly SQL MVP
>
> "Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
> news:lllhe.9690$ai7.234663@.news2.e.nsc.no...
>|||Yes hiring someone who really knows what they are doing can save you a LOT
of time and aggravation down the road. As for which is the best to cluster
you really have to know how your data is used overall to properly determine
that.
Andrew J. Kelly SQL MVP
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:A6phe.9699$ai7.233873@.news2.e.nsc.no...
> Hi
> Yes, I know that the autonumber is unique. The millisecond field is also
> microseconds from our process stations.
> I need an index with the combination of time, and two other fields. An
> Oracle database man in our company says I should make them clustered.
> It looks like You can have only one clustered index in the table. Should
> the PK be the clustered index or can I decide to use the combination of
> fields as a clustered index?
> I am concidering hiring a database expert haveing a look at our existing
> layout.
> Thank You.
>
> Regards
> Kjell Arne Johansen
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i melding
> news:ODz6QoIWFHA.3840@.tk2msftngp13.phx.gbl...
>|||Try www.sqlscripter.com to copy your data.
Thomas
"Kjell Arne Johansen" wrote:

> Hi
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new layout
.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server runni
ng
> a stored procedure located in one of the databases?
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
> Most of the tables are the same as before but there is one table that have
> been normalized. Text fields have been moved to their own tables and
> referenced from the main table.
> Thank You for your help.
> Regards
> Kjell Arne Johansen
>
>