Copy tables and permissions from one database table to another

Hi,

I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.

Thanks

If I were you I would script all the objects using management Studio and then execute those scripts against your new server/database.

-Jamie

|||

Hi Jamie,

Thanks for your reply. But if you generate script, you are not able to get the data. I need data too. So Scriptng is not usefull in this.If you know any other way please let me know

phani

|||If your database is SQL Server, you can use a Transfer SQL Server Objects task in SSIS to move the objects. You can also use the Import/Export wizard (which generates SSIS packages) to accomplish the same thing.|||

Thread wrote:

Hi Jamie,

Thanks for your reply. But if you generate script, you are not able to get the data. I need data too. So Scriptng is not usefull in this.If you know any other way please let me know

phani

You can use SSIS dataflows to move the data.

The CREATE TABLE scripts can be run from inside the SSIS package using the Execute SQL Task.

Or use the Transfer SQL Server objects Task like John suggests - although I'm not a fan of that task myself.

-Jamie

|||

Jamie Thomson wrote:

Or use the Transfer SQL Server objects Task like John suggests - although I'm not a fan of that task myself.

-Jamie

I've used it fairly successfully for simple, straightforward schema and data transfers. However, judging from the number of posts where people are having issues with it, I may be in the minority.

For more complex scenarios, I wouldn't recommend it.

|||

Hi jamie,

Thanks for your reply.

I've used the SSIS package.

It is working fine with small databases.

But in large database (More than 2 GB) having privileges and rolls on D/b it is not working, generating errors.

May be that was the problem came from the deadlock.

For more information:

I have few tables, few users, Few rolls, Few user defined data types.

Tables are naturally having primary key and foreign keys.

Thanks In Advance

Phani

Copy table Structure including primary keys, index etc.

Hi all,

I was wondering if there is a SQL command to copy the table structure of a table that includes primary keys, foreign keys, indexes, etc.

Thanks and have a nice day to all

Not a SQL command, but you can script this stuff out using the tools by right clicking the table, or programatically using SMO.

|||can you post a sample script or SMO please or send me a link discuss this matter thanks|||

Hi,

The easiest way to create the script is to right click the original table within SQL Server Management Studio and select "Script Table As ...\ Create To\ ..." . This will create a script for the table and its indexes.

Then you need to run the script but with the new tablename. After that, you need to copy the records using a insert/select command. (it is best to set the constraints/indexes afterwards).

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

I was thinking to use that script in my SP, On my SP I add a linked server then i want to copy all the tables exactly the same

and on the linked server the table names and table count change everyday, but i dont have any problem with that.

I was thinking if there is a way to copy exactly the same table inside an SP in that case.

Thanks

|||You can use sys tables/views inside your SP|||

can you post your samples script please.

thanks

|||

SELECT * INTO NewEmployee FROM Employee WHERE 1 = 0

above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

SELECT *

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee')

SELECT *

FROM syscomments

WHERE id IN ( SELECT id

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee') )

|||thanks for the reply Dinesh. Nice sql Stmt, does this work when you have a MS Access linked Server, is there a sysobjects table on the linked server?

Copy SQL2k5 publisher/distributor to new server

I have seen a few threads asking about copying a complete server,
including all of replication (merge), to a new server. I notice that
the answers state that it should be possible as long as the server
name remains the same. We are in this situation today, and do not
have the luxury of recreating all of the subscribers, nor do we have
control over when they sync -( merge replication), so we want to shut
merging down, move to a new server, and restart, without our
subscribers missing a beat.
Has anyone actually accomplished this? Any gotcha's?
I appreciate any shared experiences.
Thanks,
john g.
In my experience provided you follow the guidelines for "Strategies for
Backing Up and Restoring Merge Replication" in BOL theere should be no
issues. The only extra thing I can think of is to be sure to do it all
within the retention period (history and transaction and subscription
deactivation).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com