Copy a Table's data from a Stored Procedure

Question

I am learning how to use SQL and Stored Procedures. I know the syntax is incorrect:

Copy data from one table into another table on another Database with a Stored Procedure. The problem is I don't know what table or what database to copy to. I want it to use parameters and not specify the columns specifically.

I have 2 Databases (Master_db and Master_copy) and the same table structure on each DB.

I want to quickly select a table in Master_db and copy that table's data into Master_copy table with same name. I have come up with something like this:

USE Master_DB
CREATE PROCEDURE TransferData
DEFINE @tableFrom, @tableTo, @databaseTo;

INSERT INTO @databaseTo.dbo.@databaseTo
SELECT * FROM Master_DB.dbo.@tableFrom
GO;

---

The closest I could get was this. The problem now is that I have a Timestamp column and that rendered an error:

"Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column."

USE Kenneth_Integration
GO
CREATE PROCEDURE TransferData
  @table  SYSNAME
  ,@databaseTo SYSNAME
AS
DECLARE @sql NVARCHAR(MAX)

SET @sql = 'DELETE FROM ' + @databaseTo + '..' + @table + ' set identity_insert ' + @databaseTo + '.dbo.' + @table + 
    ' on INSERT INTO ' + @databaseTo + '.dbo.' + @table + ' SELECT * FROM Kenneth_Integration.dbo.' + @table

EXEC sp_executesql @sql
GO

How can I go around this error with this SP?

Solution 1

Object names in SQL queries cannot be parameterised in the way you are attempting.

To get this to work, you need to use dynamic SQL:

USE Master_DB
CREATE PROCEDURE TransferData
 @tableFrom   SYSNAME
 ,@tableTo    SYSNAME
 ,@databaseTo SYSNAME
AS
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'INSERT INTO ' + @databaseTo+ '.dbo.' + @tableTo + '
            SELECT * FROM Master_DB.dbo.' + @tableFrom

EXEC sp_executesql @sql
GO;

Solution 2

My suggestion is to write function which return table than use insert select statement

Difference between procedure and function than you cannot write select procedure but you can write select function

Solution 3

Only dynamic SQL allows variables as table names. For examp0le:

create procedure dbo.TransferData(
    @src varchar(50),
    @dest varchar(50))
as
declare @query varchar(500)
set @query = 'INSERT INTO ' + @src +
    'SELECT * FROM ' + @dest
exec @query
go

Solution 4

I would agree with @EdHarper that building a string and then running sp_executesql is probably your best answer.

Two things to bear in mind that I can't see in the previous answers:

1) You may have already copied the data into the Master_copy database tables previously, so you probably need to delete the data in the target table before you start your copy operation.

2) If you have any identity columns in the Master_copy tables (because you used the same Sql script to generate the Master_db and Master_copy tables for example) then you need to ensure that you can overwrite identity values (this can be done by the set identity_insert on command).

So as an alternative (there is no point to another repeated answer) and as you know the database names you could simply denormalize this approach and write a simple stored procedure for each table.

Say you have a table called 'person' you could write:

create procedure TransferDataPerson 
as

delete from Master_copy..Person

set identity_insert Master_copy..Person on

insert into Master_copy..Person
select * from Master_db..Person

... and so on for each table.

View more solutions