0 Replies - 1737 Views - Last Post: 26 December 2008 - 10:10 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

MSSQL Stored Procedure to copy a table from one database to another

Posted 26 December 2008 - 10:10 PM

Description: Provide a source table and a destination table This is a snippet I've used quite often for copying a table from one database to another (generally from production to development so I'm working with fresh data)
CREATE PROCEDURE dbo.uspCopyWholeTable
	/*
		Create parameters for the destination table (table we want to copy into)
		and the source table (table we want to copy from). The 2 tables can even be
		in different databases, just specify that in the table name like
		DBName.dbo.TableName
	*/
	@destination VARCHAR(150), @source VARCHAR(150)
AS
	/*
		Declare @SQL_STR as this will be dynamic SQL
	*/
	DECLARE @SQL_STR nvarchar(100)

	/*
		Set it's value to copy into the destination table
		from our source table
	*/
	SET @SQL_STR = 'SELECT * INTO ' + @destination + ' FROM ' + @source

	/*
		Use EXEC to execute our dynamic SQL statement
	*/
	EXEC (@SQL_STR)


Is This A Good Question/Topic? 0
  • +

Page 1 of 1