Script to copy tables and from one database to ano

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 2000 Forums

Transact-SQL (2000)

Script to copy tables and from one database to ano

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2011-02-08 : 18:07:16

I would like to compose a generic script that would copy tables from one database to another database that do not already exist in the new database.
I would also like to compare the table structure and identify any changes that have been made between the databses, load the initial data from one database to the other and load incrementals loads there after.

From what I have read on the forums etc dynamic sql would be the best option besides redgate and apexsql. However I am not that familiar with dynamic sql and would like to follow this route.

Is it possible for someone to give me a start script or point to some sites where i could get more information on how I could do this in dynamic sql. Ultimately I would like to run the script on anyone of my databases.

any help would be much appreciated

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-09 : 01:32:47

Did you think about backup/restore instead of scripting a copy?

No, you're never too old to Yak'n'Roll if you're too young to die.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2011-02-09 : 03:20:35

You can also use to generate SQL Script for the selected database.
1.) select and right click the database.
2.) select "All task" then "Generate SQL Script".
3.) click the button "Show all".
4.) explore and select the appropriate object you want to be scripted.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

sauce1979
Starting Member

47 Posts

Posted - 2011-02-09 : 11:03:30

Hi,

Thanks for the information. I have actually explored most of the options provided. I didn't knwo how to make the sql dynamic. It is more of an academic exrecise for me. Just to practice dynamic sql.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-09 : 13:03:10

Then you can start your exercise here:
http://www.sommarskog.se/dynamic_sql.html

No, you're never too old to Yak'n'Roll if you're too young to die.

sauce1979
Starting Member

47 Posts

Posted - 2011-02-10 : 05:04:33

thanks

sauce1979
Starting Member

47 Posts

Posted - 2011-02-10 : 10:57:46

I have tried to construct a script of my own. I am able to locate the tables that do not exist in on of the datatbase. My problem is now, how would I copy the tables that exist in one database to the other database where they do no exist.

The script i have witten so far is show below:


ALTER   PROCEDURE SP_ZCOMPARE_AND_LOAD
(
@DBNAME1 SYSNAME,
@DBNAME2 SYSNAME
)
AS 
BEGIN

-- Check if table exists exists. If so show error



	IF @DBNAME1 IS NULL
	BEGIN 
		RAISERROR( 'First database entered is null', 16 , 1)
		RETURN
	END

	IF @DBNAME2 IS NULL
	BEGIN 
		RAISERROR( 'Second database entered is null', 16 , 1)
		RETURN
	END

	IF DB_ID(@DBNAME1) IS NULL
	BEGIN
		RAISERROR( 'First database does not exist', 16 , 1)
		RETURN
	END

	IF DB_ID(@DBNAME2) IS NULL
	BEGIN
		RAISERROR( 'Second database does not exist', 16 , 1)
		RETURN
	END
		

--===========================================================================         
--GET TABLES THAT DO NOT EXIST IN OTHER DATABASE

DECLARE @command VARCHAR(2048)

set @command = 'SELECT A.[id]
		,B.[id]
		,a.[name]
		,b.[name]
		from
		(Select * from [' + @DBNAME1 + '].dbo.sysobjects where xtype = ''u'') as A
		FULL OUTER JOIN
		(SELECT * from [' + @DBNAME2 + '].dbo.sysobjects where xtype = ''u'') as B
		ON B.[name] = A.[name]
		and B.[id] = A.[id]
		WHERE B.name is null
		or A.name is null'
PRINT @command
EXEC (@Command)


RETURN 
END

Any help would be much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources