December 27, 2015 at 2:26 pm
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
December 27, 2015 at 4:07 pm
Does database B already have data in it? If not, it would be easier to restore A over B.
December 27, 2015 at 5:28 pm
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
December 27, 2015 at 10:46 pm
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
Change is inevitable... Change for the better is not.
December 27, 2015 at 10:58 pm
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.
December 27, 2015 at 11:11 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply