what is the best way to do this....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

what is the best way to do this....

Author

Topic

demausdauth
Starting Member

17 Posts

Posted - 2008-08-15 : 12:15:28

What I need to do is move the some of the data from one database to a newly created database. I need to copy all the tables schema but only some of the data from old to new. Ex: DB has 3 customers, I want to split out customer number 2 from the database and put it into a new database.

I want the new database to have the same permissions, etc... as the first database.

My original thoughts had been to programatically create a new database, however I am wondering if it might not be easier to do something like a file copy and then just delete everything from the copy.

How does that sound? Or is there a way to make a new database by just using the schema of the old one? And then would all the permissions, passwords, etc... be there?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 12:19:07

why you wanna do like this? How many tables you gotta move?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 12:20:03

You've something called transfer database task in ssis if you want to copy entire database.
Alternatively you can script out schema from current db and apply it also.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-15 : 13:19:00

I would just copy the DB then run the bellow to truncate all tables.

Once that is done you can start importing the records as you see fit.



USE MYDatabase
DECLARE @objName varchar(80), @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM sys.Tables
WHERE type = 'U'

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN


	Exec ('TRUNCATE TABLE ' +  @ObjName)
	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 13:28:35

quote:

Originally posted by Vinnie881

I would just copy the DB then run the bellow to truncate all tables.

Once that is done you can start importing the records as you see fit.



USE MYDatabase
DECLARE @objName varchar(80), @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM sys.Tables
WHERE type = 'U'

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN


	Exec ('TRUNCATE TABLE ' +  @ObjName)
	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

This can be done by simply Generate Script task.

demausdauth
Starting Member

17 Posts

Posted - 2008-08-15 : 14:15:09

Thanks to all for responding

sodeep,
The database has 200 + tables and I need to split the database up based upon a particular customer number. Some of the tables will have 1 or 2 records others will have thousands of records. I need to get all the records that relate to a particular customer and move them into a separate database (the new one). So what i will end up with is 2 databases DB1 with customers 1,3 and DB2 with customer 2.

Also what is Generate Script task?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:19:40

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 14:20:58

Any specific reason for doing this? Generate script task is available in Database-task-Generate script. Its a wizard that lets you scripts your whole body of databases.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 14:23:28

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

I need to Performance-tune my typing speed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:29:40

quote:
Originally posted by sodeep

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

I need to Performance-tune my typing speed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources