Correct way to create indices? SQLServer Express 2005

Hi,

Apologies if this has been asked before, i've done a search but can't find a definitive answer.

I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.

My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.

Thanks

Chris

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

|||

Motley wrote:

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

Thanks very much for your reply

I've read up quite a lot of posts and articles regarding the use of surrogate vs natural keys. My primary reason for choosing surrogate keys is the inability to guarantee access to values which would create a natural key that didn't need changing. For example a head injured patient arrives at hospital and is assessed by the team i am working for. This is common as i work for critical care services which include Neurologically impared patients. They need a unique id for that patient but have no access to a possible natural key, the patients NHS number. In this case it's easier to generate a surrogate PK like a GUID which can be used on all the joins required in the database and use an incremental value from a store to replace the NHS number and keep the record unique until the NHS number can be assertained. Even then if the patient is non-uk resident they will never have an NHS number. However if the NHS number is found at a later date the PK can remain intact it's just a case of updating the NHS number field rather than cascading a changing PK through the database.

Anyways,

If i've got this correct the purpose of clustering an index is to create some form of sorting on sequentially related values which helps with retrieving records i.e. dates

If that's correct then as a surrogate key doesn't [or shouldn't] contain data relating to the contents of the record it indentifies it wouldn't matter if it is clustered or not. I think that this would particularly be the case with GUIDs as there is no logical sequence to their creation that would be worth clustering as compared to a sequential integer that might provide a pseudo order of entry index.

The second index i've created i've set as a clustered index as that is the one that holds the data which is used for retrieval i.e. finding all patient episodes within a given date range, so sorting would be beneficial and the unique index constraint should prevent duplication of records.

I've tried to follow the advice given on msdn regarding creating indicies i just wasn't sure if i'd actually done it correctly in Express05. Of course i may have missed the point entirely

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8185.asp

Thanks again

Chris.

copying tables from 2000 to 2005 express

I have a rather sizeable SQLServer 2000 database. To work on an issue, I would like to copy just a couple tables into SQL Server 2005 Express. How does one go about this efficiently?

I have a rather sizeable SQLServer 2000 database. To work on an issue, I would like to copy just a couple tables into SQL Server 2005 Express. How does one go about this efficiently?

Sorry, I should have mentioned that I have used DTS in SQLServer 2000 to copy from tables from one DB to another, but in SQL Server Management Studio Express (or in SQL Server Configuration Manager) I do not see any DTS-like tools available.

|||You are right, there are no tools for that. What about either doing a backup / restore on the databases OR do a manally SELECT INTO with using linked servers ? Another option would be to script the structure and then script the data from the table and execute the data script on the other server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I would like to do a SELECT INTO... but could you enlighten me on how to link servers?|||

Sure, but might have a look in the BOL about adding linked servers. In common if you don′t have the GUI to administer linked servers you can use the procedure sp_addlinkedserver and others around it, mentioned in the BOL.

If you are not sure about that, do not hesitate to come back. :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I was hoping to get a bit more detail. Plus I do not understand what your last message was trying to communicate.|||

In case you're still looking, you can find a discussion of Linked Servers here. There are several links at the bottom of the topic that will take you to the specific topics on creating and using Linked Servers.

Mike

sql

Copying SqlServer database from web host

Hi,

I have a hard time copying my db (or instance?) from a SQL Server 2000 db which resides at my web host. I have Sql Server 2005 Express and Sql Server Management Studio Express on my computer and, well, there is no "ftp-like" option so I don't have a clue about how to proceed. I've read many posts on the net on this matter but nothing seems to apply to this, in my mind, rather common, configuration.

I have receieved Excel docs that should be appended to tables in my db. I have successfully installed DTS (seehttp://mobiledeveloper.wordpress.com/ for details - it's really simple but takes two hours) so I have a wizard for the actual import from Excel. My plan is now to

1. download/detach/whatever my table/db/part of the web host's db and download it to my computer. Then

2. fill the tables with the data from Excel. And finally, to

3. upload/attach/whatever my local db or table to the web host again.

I'm sooo confused, please, please help me out here!

Pettrer

Here's how:

http://forums.asp.net/1621926/ShowThread.aspx#1621926

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
Ellis
Ellis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan users.
> How can I avoid this? or anyone know article teach people to do this step by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
EllisEllis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another
?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan user
s.
> How can I avoid this? or anyone know article teach people to do this step
by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
EllisEllis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan users.
> How can I avoid this? or anyone know article teach people to do this step by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying Database in same SqlServer with different name

hii
follow this

1)stop sql server
2) take a copy of the two MDF and LDF files and NDF files if any.
3) Change the Name of the files
eg db2_data.mdf and db2_log.mdf
4)copy these in the data folderof MSSQL
5) start the SQL Server
6) from your query analyser
run sp_attach_db (please find the parameters in the Books online) procedure (if u have sql2000 u can do this from the enterprise manager)
7) in query anlayser go to db2 database , query the sysusers and drop all the users using sp_dropuser
8) recreate the users using Enterprise manage or other wise

other way
is detaching and attaching in a new name.Why are you even considering that *unsafe* route. A simple "backup database <dbname> to disk='c:\tmp.bak'" will do the backup. Then a simple "restore database <newdbname> from disk='c:\tmp.bak' with move 'newlogicalname' to 'c:\xyz.mdf',
move 'newlogicallogname' to 'c:\xyz.ldf'" will restore it.|||Perhaps you should get the Syntax correct:

restore database MyNewDB from disk='d:\MyDb.bak' with recovery, move 'MyDb_Data' to 'c:\MyNewDB.mdf', move 'MyDb_Log' to 'c:\MyNewDB.ldf'

Note: Use the following to discover files in backup.

RESTORE FILELISTONLY from disk='d:\MyDb.bak'

copying data across databases

I have a basic question:
We have a database db1 on SQLServer 2005 that gets updated v frequently.
We would to like use a different database db2 (on a different SQL Server
instance) that is a snapshot of db1. db2 is a read-only database that would
be used for reporting purposes. On a daily basis, the new data from db1 must
get copied to db2.
Is there a SQLServer Tool that would help? I tried using Copy data from SQL
Server management Studio; it copied most of the data (not all), but did not
create the indexes that existed for the tables in db1.
Option 1: DTS Package (SSIS in 2005) to update the tables
Option 2: Replication
Thanks & Rate the Postings.
-Ravi-
"Jason Manfield" wrote:

> I have a basic question:
> We have a database db1 on SQLServer 2005 that gets updated v frequently.
> We would to like use a different database db2 (on a different SQL Server
> instance) that is a snapshot of db1. db2 is a read-only database that would
> be used for reporting purposes. On a daily basis, the new data from db1 must
> get copied to db2.
> Is there a SQLServer Tool that would help? I tried using Copy data from SQL
> Server management Studio; it copied most of the data (not all), but did not
> create the indexes that existed for the tables in db1.
|||option 3 = LogShipping
Greg Jackson
PDX, Oregon
|||There is a slight change is our requirements:
Data does NOT have to copied on an incremental basis. A full copy needs to
be done from the database db1 that is periodically updated to the database
db2 that is a read-only database. In that case, what are the options
available on SqlServer 2005?
"pdxJaxon" wrote:

> option 3 = LogShipping
>
> Greg Jackson
> PDX, Oregon
>
>

copying data across databases

I have a basic question:
We have a database db1 on SQLServer 2005 that gets updated v frequently.
We would to like use a different database db2 (on a different SQL Server
instance) that is a snapshot of db1. db2 is a read-only database that would
be used for reporting purposes. On a daily basis, the new data from db1 must
get copied to db2.
Is there a SQLServer Tool that would help? I tried using Copy data from SQL
Server management Studio; it copied most of the data (not all), but did not
create the indexes that existed for the tables in db1.Option 1: DTS Package (SSIS in 2005) to update the tables
Option 2: Replication
--
Thanks & Rate the Postings.
-Ravi-
"Jason Manfield" wrote:
> I have a basic question:
> We have a database db1 on SQLServer 2005 that gets updated v frequently.
> We would to like use a different database db2 (on a different SQL Server
> instance) that is a snapshot of db1. db2 is a read-only database that would
> be used for reporting purposes. On a daily basis, the new data from db1 must
> get copied to db2.
> Is there a SQLServer Tool that would help? I tried using Copy data from SQL
> Server management Studio; it copied most of the data (not all), but did not
> create the indexes that existed for the tables in db1.|||option 3 = LogShipping
Greg Jackson
PDX, Oregon|||There is a slight change is our requirements:
Data does NOT have to copied on an incremental basis. A full copy needs to
be done from the database db1 that is periodically updated to the database
db2 that is a read-only database. In that case, what are the options
available on SqlServer 2005?
"pdxJaxon" wrote:
> option 3 = LogShipping
>
> Greg Jackson
> PDX, Oregon
>
>

copying data across databases

I have a basic question:
We have a database db1 on SQLServer 2005 that gets updated v frequently.
We would to like use a different database DB2 (on a different SQL Server
instance) that is a snapshot of db1. DB2 is a read-only database that would
be used for reporting purposes. On a daily basis, the new data from db1 must
get copied to db2.
Is there a SQLServer Tool that would help? I tried using Copy data from SQL
Server management Studio; it copied most of the data (not all), but did not
create the indexes that existed for the tables in db1.Option 1: DTS Package (SSIS in 2005) to update the tables
Option 2: Replication
Thanks & Rate the Postings.
-Ravi-
"Jason Manfield" wrote:

> I have a basic question:
> We have a database db1 on SQLServer 2005 that gets updated v frequently.
> We would to like use a different database DB2 (on a different SQL Server
> instance) that is a snapshot of db1. DB2 is a read-only database that woul
d
> be used for reporting purposes. On a daily basis, the new data from db1 mu
st
> get copied to db2.
> Is there a SQLServer Tool that would help? I tried using Copy data from SQ
L
> Server management Studio; it copied most of the data (not all), but did no
t
> create the indexes that existed for the tables in db1.|||option 3 = LogShipping
Greg Jackson
PDX, Oregon|||There is a slight change is our requirements:
Data does NOT have to copied on an incremental basis. A full copy needs to
be done from the database db1 that is periodically updated to the database
db2 that is a read-only database. In that case, what are the options
available on SqlServer 2005?
"pdxJaxon" wrote:

> option 3 = LogShipping
>
> Greg Jackson
> PDX, Oregon
>
>

copying a database in the same SQLserver instance

Hi

Im using SQL server express and Management studio express, and i have a database attached called database1.

Now i would like to have an exact copy of this database, named database2.

I would only need the tables, and not the data in them, is there somehow i can do this?

There is no copy button in managment studio...

Create a new database called Database2, make Script of the database1 and open a query analyser window, change the database in the dropdown combo to datbase 2 (or run Use database2) and run the script

Steps to script database

(a) Right Click on DB

(b) Tasks Generate SQL Script and follow the instruction. You will get script without data.

Madhu

|||

Thank you for that..

I have no idea what it did with those scripts and such, but it made a perfect copy like i wanted..

Copy userdata

Hi,
how can I copy the userdata from one sqlserver to another sqlserver ?
Best regards
Axel Lanser
Exactly what do you mean by "userdata"? Does the database exists? Does the tables exists? Do you
want to remove the rows in the destination tables if they exists? For all tables in a database? I
suggest you check out the DTS tool. This is probably the right tool for the job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> Hi,
> how can I copy the userdata from one sqlserver to another sqlserver ?
> Best regards
> Axel Lanser
|||You can use DTS if it's just the straight data that you care about and the
destination
server already has the schema defined. However, you may have to drop
any foreign keys on the destination server and then re-apply them after
the data has been loaded.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> Hi,
> how can I copy the userdata from one sqlserver to another sqlserver ?
> Best regards
> Axel Lanser
|||Hi,
with dts I can copy only tables and views from a database. In the sql server
I define much sql server specific und windows specific users with their right.
That users and their rights I had defined in one SQL-server. My problem is
now how can I copy them to a 2nd SQL-server.
Best refards
Axel Lanser
"Armando Prato" wrote:

> You can use DTS if it's just the straight data that you care about and the
> destination
> server already has the schema defined. However, you may have to drop
> any foreign keys on the destination server and then re-apply them after
> the data has been loaded.
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
>
>
|||You don't have to build tables. You can choose to append to existing
tables.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:02C317FA-CD0E-451C-87D7-89CDCD9CEB3F@.microsoft.com...
> Hi,
> with dts I can copy only tables and views from a database. In the sql
server
> I define much sql server specific und windows specific users with their
right.[vbcol=seagreen]
> That users and their rights I had defined in one SQL-server. My problem is
> now how can I copy them to a 2nd SQL-server.
> Best refards
> Axel Lanser
> "Armando Prato" wrote:
the[vbcol=seagreen]
|||Hi,
thank you very much. Today I found the right way to copy the userdata with
dts.
Best regards
Axel Lanser
"Armando Prato" wrote:

> You don't have to build tables. You can choose to append to existing
> tables.
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:02C317FA-CD0E-451C-87D7-89CDCD9CEB3F@.microsoft.com...
> server
> right.
> the
>
>

Copy userdata

Hi,
how can I copy the userdata from one sqlserver to another sqlserver ?
Best regards
Axel LanserExactly what do you mean by "userdata"? Does the database exists? Does the tables exists? Do you
want to remove the rows in the destination tables if they exists? For all tables in a database? I
suggest you check out the DTS tool. This is probably the right tool for the job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> Hi,
> how can I copy the userdata from one sqlserver to another sqlserver ?
> Best regards
> Axel Lanser|||You can use DTS if it's just the straight data that you care about and the
destination
server already has the schema defined. However, you may have to drop
any foreign keys on the destination server and then re-apply them after
the data has been loaded.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> Hi,
> how can I copy the userdata from one sqlserver to another sqlserver ?
> Best regards
> Axel Lanser|||Hi,
with dts I can copy only tables and views from a database. In the sql server
I define much sql server specific und windows specific users with their right.
That users and their rights I had defined in one SQL-server. My problem is
now how can I copy them to a 2nd SQL-server.
Best refards
Axel Lanser
"Armando Prato" wrote:
> You can use DTS if it's just the straight data that you care about and the
> destination
> server already has the schema defined. However, you may have to drop
> any foreign keys on the destination server and then re-apply them after
> the data has been loaded.
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> > Hi,
> > how can I copy the userdata from one sqlserver to another sqlserver ?
> >
> > Best regards
> >
> > Axel Lanser
>
>|||You don't have to build tables. You can choose to append to existing
tables.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:02C317FA-CD0E-451C-87D7-89CDCD9CEB3F@.microsoft.com...
> Hi,
> with dts I can copy only tables and views from a database. In the sql
server
> I define much sql server specific und windows specific users with their
right.
> That users and their rights I had defined in one SQL-server. My problem is
> now how can I copy them to a 2nd SQL-server.
> Best refards
> Axel Lanser
> "Armando Prato" wrote:
> >
> > You can use DTS if it's just the straight data that you care about and
the
> > destination
> > server already has the schema defined. However, you may have to drop
> > any foreign keys on the destination server and then re-apply them after
> > the data has been loaded.
> >
> > "ALN" <ALN@.discussions.microsoft.com> wrote in message
> > news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> > > Hi,
> > > how can I copy the userdata from one sqlserver to another sqlserver ?
> > >
> > > Best regards
> > >
> > > Axel Lanser
> >
> >
> >|||Hi,
thank you very much. Today I found the right way to copy the userdata with
dts.
Best regards
Axel Lanser
"Armando Prato" wrote:
> You don't have to build tables. You can choose to append to existing
> tables.
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:02C317FA-CD0E-451C-87D7-89CDCD9CEB3F@.microsoft.com...
> > Hi,
> > with dts I can copy only tables and views from a database. In the sql
> server
> > I define much sql server specific und windows specific users with their
> right.
> > That users and their rights I had defined in one SQL-server. My problem is
> > now how can I copy them to a 2nd SQL-server.
> >
> > Best refards
> >
> > Axel Lanser
> >
> > "Armando Prato" wrote:
> >
> > >
> > > You can use DTS if it's just the straight data that you care about and
> the
> > > destination
> > > server already has the schema defined. However, you may have to drop
> > > any foreign keys on the destination server and then re-apply them after
> > > the data has been loaded.
> > >
> > > "ALN" <ALN@.discussions.microsoft.com> wrote in message
> > > news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> > > > Hi,
> > > > how can I copy the userdata from one sqlserver to another sqlserver ?
> > > >
> > > > Best regards
> > > >
> > > > Axel Lanser
> > >
> > >
> > >
>
>

Copy userdata

Hi,
how can I copy the userdata from one sqlserver to another sqlserver ?
Best regards
Axel LanserExactly what do you mean by "userdata"? Does the database exists? Does the t
ables exists? Do you
want to remove the rows in the destination tables if they exists? For all ta
bles in a database? I
suggest you check out the DTS tool. This is probably the right tool for the
job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> Hi,
> how can I copy the userdata from one sqlserver to another sqlserver ?
> Best regards
> Axel Lanser|||You can use DTS if it's just the straight data that you care about and the
destination
server already has the schema defined. However, you may have to drop
any foreign keys on the destination server and then re-apply them after
the data has been loaded.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
> Hi,
> how can I copy the userdata from one sqlserver to another sqlserver ?
> Best regards
> Axel Lanser|||Hi,
with dts I can copy only tables and views from a database. In the sql server
I define much sql server specific und windows specific users with their righ
t.
That users and their rights I had defined in one SQL-server. My problem is
now how can I copy them to a 2nd SQL-server.
Best refards
Axel Lanser
"Armando Prato" wrote:

> You can use DTS if it's just the straight data that you care about and the
> destination
> server already has the schema defined. However, you may have to drop
> any foreign keys on the destination server and then re-apply them after
> the data has been loaded.
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:3039DE30-66B9-4058-8C94-76E0547B997A@.microsoft.com...
>
>|||You don't have to build tables. You can choose to append to existing
tables.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:02C317FA-CD0E-451C-87D7-89CDCD9CEB3F@.microsoft.com...
> Hi,
> with dts I can copy only tables and views from a database. In the sql
server
> I define much sql server specific und windows specific users with their
right.[vbcol=seagreen]
> That users and their rights I had defined in one SQL-server. My problem is
> now how can I copy them to a 2nd SQL-server.
> Best refards
> Axel Lanser
> "Armando Prato" wrote:
>
the[vbcol=seagreen]|||Hi,
thank you very much. Today I found the right way to copy the userdata with
dts.
Best regards
Axel Lanser
"Armando Prato" wrote:

> You don't have to build tables. You can choose to append to existing
> tables.
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:02C317FA-CD0E-451C-87D7-89CDCD9CEB3F@.microsoft.com...
> server
> right.
> the
>
>

copy some fields of table1 to an existing table2

I have two tables (table1 and table2) in a database of sqlserver 2000. I
want to add those fields of table1 who doesn't exist in table2, to table2.
How can I do that?
Thanks,
HugoEduco Gent wrote:
> I have two tables (table1 and table2) in a database of sqlserver 2000. I
> want to add those fields of table1 who doesn't exist in table2, to table2.
You will have to do this by hand unless you purchase a diff tool that is
designed to work against database schema. SQLCompare is a pretty popular
one.
Otherwise you will have to do it by hand.
ALTER TABLE table2
ADD COLUMN [NewColumn] <datatype>
See Books Online for more information.
http://msdn.microsoft.com/library/e..._aa-az_4e5h.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi Educo
Check if following solution helps you.
I took two tables Table1 and Table2 (Formatting need to be done)
create table Table1(a int , b int , c int,d varchar(100),e nvarchar(100),f
text , g money)
create table Table2(a int , b int)
This proc will do the job.
Create Procedure Proc_AddCols
@.Tab1 Varchar(1000),
@.Tab2 Varchar(1000)
As
declare
@.sqlString Varchar(8000),
@.ColString Varchar(8000)
Begin
Set @.ColString = ''
Select @.ColString = @.ColString + 'Alter Table ' + IsNull(@.Tab2,'') + ' Add
' + IsNull(Column_Name,'') + ' ' + Case upper(IsNull(Data_Type,''))
When 'INT' then ' INT'
When 'VARCHAR' then ' VARCHAR(' + cast(IsNull(Character_Maximum_Length,'')
as Varchar) + ')'
When 'NVARCHAR' then ' NVARCHAR(' +
cast(IsNull(Character_Maximum_Length,'')
as Varchar) + ')'
Else ' ' + upper(IsNull(Data_Type,''))
end + ' ' + Case IsNull(IS_Nullable,'') when 'YES' then '' else ' NotNull'
end
From Information_Schema.Columns Where Table_Name Like @.Tab1 And Column_Name
not in
(Select Column_Name from Information_Schema.Columns Where Table_Name like
@.Tab2)
Exec(@.ColString)
End
Go
Exec Proc_AddCols 'Table1','Table2'
Go
Exten this stored procedure for your solution
Tell me if it solves your problem.
Vishal Khajuria
Sungard SCT India
"Educo Gent" wrote:

> I have two tables (table1 and table2) in a database of sqlserver 2000. I
> want to add those fields of table1 who doesn't exist in table2, to table2.
> How can I do that?
> Thanks,
> Hugo
>
>