Copying tables, MSDE

Hi!

I've got a very simple problem I can't find an answere to.

I've got an MSDE database and I want to copy a table.

I've tried something like:

create table2 as select * from table1

with and without the "as", but I can't get it to work and I can't find a good answere on the internet.

very thankful for an answere!

/Jon

hello..

use SELECT INTO statement:
from MSDN:

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
sample code:
SELECT * INTO table2
FROM table1

|||ok, almost there...
it works, except for the keys.
how do I make the primary keys be primary keys in the copied table aswell?
Thanx!
/J|||

(sorry if this reply got posted twice)

thanx,

is it possible to get the old primary keys to be primary keys in the new table aswell?

The newly created table does not contain any kesy at this moment.

/jon

Copying my existing database to another server

Hi

I am running my sql 2000 database server and I just want to copy that database and paste or put on another server which has also run same version of sql 2000 database. I tried to copy and paste the ldf & mdf extensioned files which are located in "C:\Program Files\Microsoft SQL Server\MSSQL\Data" into my 2 server, but when I open up the enterprise manager, I cant see the new database. So this way dont work, I tried to do something with export and import wizard but it just creates another database and copies the data in that database in the same server. I want to copy that data and put onto another system, please guide me. I'm confused, Thanks a lot

Use the copy database wizard-http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_onmigwiz_0z50.asp|||

That's it,

Thanks thanks thanks :)

|||One thing to keep in mind: Copy Database Wizard is only available in SQL2005, and the destination data source must be SQL2005. For SQL2000 you can use Backup/Restore to move databases. You may take a look at:Copying Databases to Other Servers|||Actually, it's available in SQL Server 2000 as well, see the link in my first post.|||Thanks Mudface, you teach me a lotsql

copying large SQL SERVER tables between DB's

Hi
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
aviIf you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>

copying large SQL SERVER tables between DB's

Hi
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
avi
If you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>
|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>

Copying inicial snapshot to the subscriber via CD

Hi!
I have a very large database that I replicate to 40 subscribers. The problem is that where I make a new push subscription and SQL is applying the Msmerge_contens, I obtain the message "The subscriber didn't respond in 10 minutes. Agent is suspect." I have
already increment de 10 minutes to 2 hours but the problem continues to exist. How can I put the snapshot in the subscriber via CD in order to avoid all then data transference over a slow network?
Regards
This message does not mean that your snapshot will not be applied
successfuly, it just means that it takes more than 10 minutes to transfer
some article data. This status of merge agent looks like an error (in EM),
but it is not. Just check if the merge agent job is runing - if it is, then
everything is fine, it just takes time..
If you want to avoid copying all the data to subscribers you can make a
backup of your publisher and restore it in remote location. Then create
subscription in such a way, that it would not synchronize data - after
restore data is identical anyway. But doing it in such a way you have to
make sure that during the interval between publisher backup and restore on
subscriber, no data changes are made on publisher.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Pedro" <pedro.lopes@.sqps.ivn.pt> wrote in message
news:C7D02833-9B35-4798-94F2-9B6FDED8808D@.microsoft.com...
> Hi!
> I have a very large database that I replicate to 40 subscribers. The
problem is that where I make a new push subscription and SQL is applying the
Msmerge_contens, I obtain the message "The subscriber didn't respond in 10
minutes. Agent is suspect." I have already increment de 10 minutes to 2
hours but the problem continues to exist. How can I put the snapshot in the
subscriber via CD in order to avoid all then data transference over a slow
network?
> Regards
>

Copying a SQL Server database

Hi

I am new to SQL Server. I am required to make a copy of a live database, or bring the database down and then make a copy of it.

I need to create a webpage to query the database, but another scheduling program also connects to the database, so that shceduling can be performed

Any helpBy all means refer to this http://vyaskn.tripod.com/moving_sql_server.htm link about copying to another server though it relates to moving to another server.|||Originally posted by Satya
By all means refer to this http://vyaskn.tripod.com/moving_sql_server.htm link about copying to another server though it relates to moving to another server.

Thank you so much fro the quick reply, I will go and have a look at the site right now...thanks

copying a DB from one server to another

Hi

I have a PC with MSDE2000 and need to copy the data to another PC running SQL2005.

I have registered the MSDE server on my SQL2005 PC and can see the tables etc.

Can this be done automatically, say every day?

Is there a way of mirroring the DB?

Cheers

Eugene

Hello,

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx. You can find more info from this about copying the database from MSDE to SQL2005.

Thanks

|||

If your SQL Server 2005 machine is running Standard Edition or higher, you can create a package in SQL Server Integration Services to automate moving data. You can schedule running the package using SQL Server Agent.

Database replication (publisher/subscriber) requires higher SKUs (Workgroup and up) for the publisher. SQL Server 2005 Express can be a subscriber though. You can see a comparison of features here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Hope this helps,
Steve

|||

Hi Steve

I Am using 2005 Std, I have also just installed SP1

I was hoping to use the mirroring function but it looks like it only works with sql 2005 DB! The same is true of Log shipping I think?

So is there any samples of how you use this integration service?

I am not that familiar with SQL as a whole and it would be good if there we're simple ways around the problems.

I would of thought there is an easy way to automatically copy a DB to another Server , like wizards....

Any Help Would be Appreciated

Cheers

Eugene

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - dave
Hi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
ontario, canadaFirst, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 20
05
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:

> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
>|||access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
"db" wrote:
[vbcol=seagreen]
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 200
0.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
>|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...[vbcol=seagreen]
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
>|||db wrote:
> access data from a remote server from within a query. Should a linked serv
er
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and
I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed que
ry
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039
)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
ontario, canada
First, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 2005
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>
|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:

> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
>
|||access data from a remote server from within a query. Should a linked server
be a good idea.
ontario, canada
"db" wrote:
[vbcol=seagreen]
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 2000.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...[vbcol=seagreen]
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
|||db wrote:
> access data from a remote server from within a query. Should a linked server
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff
|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>
|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed query
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff
|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>
|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy table record as new record

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

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

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

Copy Table + data from database A to database B

Hi:
I'm really new to this DBA stuff and I need an explanation for what just hap
pened.
I just used the copy wizard to copy Table A from database A to database B.
All the data and the table structure were successfully created in database B
with one exception: the first column had
the IDENTITY (1,1) attribute set in database A. But after the copy to datab
ase B the IDENTITY attribute was missing!!!
Fortunately, I'm one of those inveterate "testers" so I discovered the probl
em before it made it into Production.
Eventually, I had to generate a script for the table to create that table in
database B with the IDENTITY in place and
then import the data.
Anyone have any idea what the problem is and how to solve it' I don't like
the idea of having to generate a script for
each table that I need to move and then import the data!!!! The copy wizar
d is a gem if it works as advertised!
Using SQL Server 7.0.
cathyThe copy table option only copies the data. If you want to copy all the
attributes you should use the Copy objects and data between SQL Server
databases.
The copy table option was designe to allow data transfers between
heterogenous data sources.
Rand
This posting is provided "as is" with no warranties and confers no rights.

copy struture of tables without loosing data

HI
I need to copy the table structures from my production database to development database but not loose the data in developement. Is there a way to achive this by creating some scripts.
thanksYou want to copy some tables from your production environment to devleopment? If so have you tried DTS?|||In your approach, you need to prevent to re-create your tables, and you simply have to append the data. DTS is the most suitable tool, there are other ETL tools as well, and, not recommended, you can write your transformation process in a varity of languages.

I'd be glad to hear your approach.