Copy all data from Database A. to Database B.

Question

Post reply

Copy all data from Database A. to Database B.

C@mel

SSC Rookie

Points: 28
More actions
December 27, 2015 at 2:26 pm

#303411

Hello Everyone...
Could you please help me with a query that can copy all data (tables, functions, procedures, etc) from Database A. to Database B. in the same instance?
Thanks in advance,
Bill

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63487 More actions · Answer 1

Does database B already have data in it? If not, it would be easier to restore A over B.

C@mel SSC Rookie Points: 28 More actions · Answer 2

No... Database B. is empty. I don't want to restore a backup of A. to B.

I want a script to copy all data from A. to B.

Maybe something like this...

declare @linkedServer sysname='B';

declare @SourceDbName sysname='A';

declare @sql varchar(8000)

declare @tableName sysname

declare Cur_tab Cursor

for

select Name from sys.tables t where t.type='U';

open cur_tab

fetch next FROM CUR_TAB into @tablename;

DECLARE @listStr VARCHAR(MAX)

WHILE @@FETCH_STATUS =0

begin

SET @listStr=null;

SELECT @listStr = COALESCE(@listStr+',' ,'') + Name

FROM sys.columns where object_id=object_id(@tableName);

SET @sql='SET IDENTITY_INSERT '+ @TABLENAME +' ON;INSERT INTO '+@TABLENAME+'('+@liststr+')SELECT '+@liststr+' FROM ['+

convert(varchar,@linkedServer )+'].['+ convert(varchar,@SourceDbName )+'].[DBO].['+@tableName+']

;SET IDENTITY_INSERT '+ @TABLENAME +' OFF;'

PRINT @SQL

EXEC(@SQL)

fetch next FROM CUR_TAB into @tablename;

end

CLOSE CUR_TAB

DEALLOCATE CUR_TAB

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 3

C@mel (12/27/2015)I don't want to restore a backup of A. to B.
I want a script to copy all data from A. to B.

WHY? To what end? What is the reason for doing it the most difficult way possible instead of the simplest?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 4

Jeff Moden (12/27/2015)
C@mel (12/27/2015)I don't want to restore a backup of A. to B.
I want a script to copy all data from A. to B.
WHY? To what end? What is the reason for doing it the most difficult way possible instead of the simplest?

Only reason I can think of for anyone wanting to do this is on the likes of Amazon's RDS where restoring a database from an external backup is not an option.

😎

"Quick" solution, in SSMS right click on the database, select Tasks->Generate Scripts... and include the data.

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 5

Eirikur Eiriksson (12/27/2015)
Jeff Moden (12/27/2015)
C@mel (12/27/2015)I don't want to restore a backup of A. to B.
I want a script to copy all data from A. to B.
WHY? To what end? What is the reason for doing it the most difficult way possible instead of the simplest?
Only reason I can think of for anyone wanting to do this is on the likes of Amazon's RDS where restoring a database from an external backup is not an option.
😎
"Quick" solution, in SSMS right click on the database, select Tasks->Generate Scripts... and include the data.

Gosh, no. Don't include the data unless there's very little. It creates one INSERT with a full list of column names and values per row. If you have a lot of data, it will take a month of Sundays to upload and run. Even RedGate datacompare makes the same kind of mess of things.

I also agree that the SSMS script generator works a treat except for one thing... it doesn't always follow the correct dependency order. There can also be circular references between tables (not a good idea but it happens) that throw a monkey wrench into the script, however it's generated.

It would be nice if the OP spoke up as to why the need, right about now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)