Copying users from one database to another

how do i copy all the users, roles, permissions etc from on database to
another database?JT
Not easy task. If you have the same structure of the database the best thing
will be restoring the database with a different name
Otherwise, you will have to generate a script for adding users to be mapped
with the logins
sp_grantdbaccess @.login
sp_addrolemember 'db_owner',@.login
"JT" <col@.newsgroup.nospam> wrote in message
news:ec9iL$pNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> how do i copy all the users, roles, permissions etc from on database to
> another database?

Copying structure of table form one database to another

I am wondering if there is a simple way, like a stored procudure or
using some type of table object, to copy a table form one database to
another. Just the structure , index, triggers, I don't want the data.kevin.jonas@.gmail.com wrote:
> I am wondering if there is a simple way, like a stored procudure or
> using some type of table object, to copy a table form one database to
> another. Just the structure , index, triggers, I don't want the data.
You can generate a script for the whole schema with EM and execute it on
the target db. Maybe there's also a solution involving DTS - I'm not sure.
robert|||Using DMO library also is possible and using DTS
--
Current location: Alicante (ES)
"Robert Klemme" wrote:

> kevin.jonas@.gmail.com wrote:
> You can generate a script for the whole schema with EM and execute it on
> the target db. Maybe there's also a solution involving DTS - I'm not sure
.
> robert
>

Copying from one field to another in the same row

Is there a simple method within SQL to copy the contents of one field to
another field in the same row?
TGSYes,
UPDATE Sometable Set DestColumn = SourceColumn
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Good Son" <good-son@.freeuk.com> schrieb im Newsbeitrag
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||UPDATE <Table>
SET column1 = column2
thanks and regards
Chandra
"The Good Son" wrote:

> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS
>|||UPDATE Table SET col1=col2
WHERE ..........
"The Good Son" <good-son@.freeuk.com> wrote in message
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||Many thanks to all those who replied.
TGS

Copying data between database tables

Hi. I need to move data from one database table to

another across database instances. A simple example of the typical

move would be:

[CODE]

INSERT into destination_db.dbo.table1

SELECT column1, column2, column3, column4 from source_db.dbo.table2

[/CODE]

My options are:

1. Create an SSIS package to perform the move.

2. Create sprocs and schedule the data move as jobs.

3. Write .NET code using sprocs to perform the move.

I'll have to move hundreds of thousands of records, so I want the

option that provides the best performance. I'm guessing that option 3

will be the slowest.

Thanks for the help!
To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.|||

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

|||

Jamie Thomson wrote:

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

I concur with Jamie. Write a simple SSIS job to extract and then load the data. I can pull 100,000 records a second from SQL Server 2005 on my laptop into a flat file. Look on my website for details on how I do this and just substitute a table for the flat file in my demo.

copying a table from one database to another "last" doesnt appear the same

In enterprise manager I am copying a table from one database to
another. I am using the dts wizard to import the data. After I
successfully import the data, I open both tables to compare the
records to make sure they are the same. I right click on a field and
click "last" for both tables. However, the record is different for
both. If I do a query the record is still there but they do not show
up in the same order. Why does'nt the import wizard import the
records in the same order? Any help would be greatly appreciated.Rows in a table are not ordered. The Wizard does an INSERT... SELECT from
one table to another and unless there's a clustered index on the table
there's a good chance that the rows will be physically stored in a different
order.

Don't worry about the physical order - just use SELECT... ORDER BY if you
want to see the rows ordered.

--
David Portas
----
Please reply only to the newsgroup
--|||Great! Thank you very much for your help! After submitting this
question I noticed that microsoft mentions this on their site:

"Because SQL Server can use parallel scans to retrieve data, the data
bulk copied from an instance of SQL Server is not guaranteed to be in
any specific order unless you bulk copy from a query and specify an
ORDER BY clause."

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||But note that even if you manage to insert the rows in a specific order
there is no guarantee that they will be sorted in that order when you view
the data. If you require a specific order then you need to use ORDER BY
every time you want to view the data.

--
David Portas
----
Please reply only to the newsgroup
--

Copying a column names from one Database to another Database

Hello to everyone,
I am trying to check scripts when copying column names from one Database to
another Database.
If the Trigger is the answer. I don't know how to write the script properly
calling out the Database name.
Please help.
Thanks so much,Use the threepart name:
Select * from Database.Owner.Objectname
HTH, Jens Suessmeyer.|||Jens wrote:
>Use the threepart name:
>Select * from Database.Owner.Objectname
>HTH, Jens Suessmeyer.
Hi Jens,
Thanks for the attention. However I am not sure what does the "owner" means.
In my example, I need to copy the standard cost column from DB01 to standard
cost column of DB02. The table is Item master.
In this the correct syntax:
Update DB02.dbo(just guessing).stdost
Set DB02.dbo.stdcost=db1.dbo.stdcost
where dbo2.item no. = dbo1.item no.
Please let me if the above is right, else would like to request if you can
correct the above, many thanks!|||Close:
Update DB02.dbo.<tableName>.stdost
Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
Assuming that the owner is dbo (if not just leave it out -->
DB02..<tableName>.stdost) and the database is on the local server
otherwise you need a linked server entry.
HTH, jens Suessmeyer.|||Jens wrote:
>Close:
>Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
>Assuming that the owner is dbo (if not just leave it out -->
>DB02..<tableName>.stdost) and the database is on the local server
>otherwise you need a linked server entry.
>HTH, jens Suessmeyer.
Hi Jens
Thanks so much for your support,
May I further ask if I still using triggers? or the below is the whole synta
x
already?
Also I have question on DB01 of the 3rd line, please see below
Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo."Should this be <tablename> instead
of DB02".item_no.
At any rate, kindly correct me. I hope I have the last questions on this
matter.
What a great world is this, thanks so much Jens God Bless
>
Message posted via http://www.webservertalk.com|||Sure, just a copy&paste error, sorry for that ;-)
But at the end you learned the syntax that fast, that you could even
find the errors :-)
HTH, Jens Suessmeyer.|||Jens wrote:
>Sure, just a copy&paste error, sorry for that ;-)
>But at the end you learned the syntax that fast, that you could even
>find the errors :-)
>HTH, Jens Suessmeyer.
I have you as my good tutor that's why, :-) Thanks so much ^.^
Message posted via http://www.webservertalk.com

copy table to another database

Hi folks
Trying to copy a single table from database (contains many tables) to
another database (new DB) with just that one table. Trying to do this with
sql statements if possible?
Any help would be appreciated.
Dan
Danny,
You can create a table with all of its contents by the following command:
SELECT * INTO NewDatabase.dbo.NewTable FROM OldDatabase.dbo.OldTable
However, this will not recreate indexes, constraints, and so forth. So, a
more complete method would be to:
First, in the Object Explorer, right click on the table in question and
Script Table as Create. This will give you a create script wtih indexes,
constraints, etc. (If you do not get all the components you need, check
your scripting options to make sure that nothing you need is turned off.)
Second, use that script to create the table in your new database.
Third, run code like this:
INSERT INTO NewDatabase.dbo.NewTable (Column1, Column2, ... etc )
SELECT Column1, Column2, ... etc
FROM OldDatabase.dbo.OldTable
If you have an IDENTITY column defined, then consider whether you want to
keep the old values (SET IDENTITY_INSERT ON) or generate new values (leave
the identity column our of the column lists).
RLF
"Danny" <lane.dj@.gmail.com> wrote in message
news:uUWaCxHJIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi folks
> Trying to copy a single table from database (contains many tables) to
> another database (new DB) with just that one table. Trying to do this with
> sql statements if possible?
> Any help would be appreciated.
> Dan
>

copy table from a server to another server

hi guys,
I have a question about copying a table's data from one server to
another server. I want the fastest way to do it. basically, I'm using DTS
wizard to copy but it takes more than 10 minutes because there are a lot of
rows. I wonder if I use BCP by copy data to a file and do bulk insert from a
file, then will it be faster? I 'm not sure because it seems like I'm doing
2 steps instead of 1 step via DTS wizard.
I want to copy data over as fast as possible. Wondering what you guys do in
my situation.
thanks
KevinIs the server on the same network? If so, maybe try this:
INSERT [server].[database].[owner].[table]
SELECT [columns] FROM [table]
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23O7r4yEwFHA.2864@.TK2MSFTNGP10.phx.gbl...
> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using
> DTS wizard to copy but it takes more than 10 minutes because there are a
> lot of rows. I wonder if I use BCP by copy data to a file and do bulk
> insert from a file, then will it be faster? I 'm not sure because it
> seems like I'm doing 2 steps instead of 1 step via DTS wizard.
> I want to copy data over as fast as possible. Wondering what you guys do
> in my situation.
> thanks
> Kevin
>|||BCP should be faster
I mean try it out see what the time difference is
http://sqlservercode.blogspot.com/
"Kevin" wrote:

> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using DT
S
> wizard to copy but it takes more than 10 minutes because there are a lot o
f
> rows. I wonder if I use BCP by copy data to a file and do bulk insert from
a
> file, then will it be faster? I 'm not sure because it seems like I'm doi
ng
> 2 steps instead of 1 step via DTS wizard.
> I want to copy data over as fast as possible. Wondering what you guys do
in
> my situation.
> thanks
> Kevin
>
>|||yes, the same network.
but why is your way different from dts or better than DTS?
I don't think it's faster.
"Yosh" <yoshi@.nospam.com> wrote in message
news:eTpbw1EwFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Is the server on the same network? If so, maybe try this:
> INSERT [server].[database].[owner].[table]
> SELECT [columns] FROM [table]
>
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23O7r4yEwFHA.2864@.TK2MSFTNGP10.phx.gbl...
>|||I thought there are some kinds of table setting I can tweak so I can avoid
transaction log. I know one thing, I can remove indexes to speed up the
copying, but what other tricks do you have?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:487F4412-7B52-4B5A-A8C4-8B57A6512E15@.microsoft.com...
> BCP should be faster
> I mean try it out see what the time difference is
> http://sqlservercode.blogspot.com/
>
> "Kevin" wrote:
>

Copy Stored Procedures (2005)!

Hi,
I am trying to import stored procedures from one db on one server to
another db on another server.
I have been able to get the tables & data accross using the import wizard,
but don't appear to be given any options to copy stored procedures accross.
Using SQL Server 2005
Cheers,
Adam
http://communities.microsoft.com/new...=sqlserver2005
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adam Knight" wrote:

> Hi,
> I am trying to import stored procedures from one db on one server to
> another db on another server.
> I have been able to get the tables & data accross using the import wizard,
> but don't appear to be given any options to copy stored procedures accross.
> Using SQL Server 2005
> Cheers,
> Adam
>
>

Copy Stored Procedures (2005)!

Hi,
I am trying to import stored procedures from one db on one server to
another db on another server.
I have been able to get the tables & data accross using the import wizard,
but don't appear to be given any options to copy stored procedures accross.
Using SQL Server 2005
Cheers,
Adamhttp://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adam Knight" wrote:

> Hi,
> I am trying to import stored procedures from one db on one server to
> another db on another server.
> I have been able to get the tables & data accross using the import wizard,
> but don't appear to be given any options to copy stored procedures accross
.
> Using SQL Server 2005
> Cheers,
> Adam
>
>

Copy SPROCs to different database

Is there a manual or code way to copy stored procedures from 1one sql db to
another without opening each one, copying, creating a new SPROC in target db
and pasting?
Can DTS accomplish this?You can use DTS. If you have enterprise manager, you can also just
right-click on a database and choose to import or export data. When you get
into the wizard, choose to "Copy objects and data betwen SQL Server
databases". You can then save this entire thing as a DTS package, which you
can manipulate, schedule, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"scott" wrote:

> Is there a manual or code way to copy stored procedures from 1one sql db t
o
> another without opening each one, copying, creating a new SPROC in target
db
> and pasting?
> Can DTS accomplish this?
>
>|||You can use Enterprise Manager and right click on the db and choose "All
Tasks - Generate SQL Script". Then choose all the sp's and you will get one
script for all the sps. Just run that in the other db.
Andrew J. Kelly SQL MVP
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>|||You can use DTS to copy database objects from one database to another.
Are you using Sql Server 2000 or Sql Server 2005? I think, if I rememer
correctly, there is a copy database or copy database objects task in 2000.
2005 makes it a whole lot easier.
-Tim
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>