Have Geek Brain

Copy table data from stored procedure

I am learning how to use SQL and stored procedures. I know the syntax is wrong:

Copy data from one table to another table to another database with a stored procedure. The problem is I don't know which table or which database to copy. I want it to use parameters and not specify specific columns.

I have 2 databases (Master_db and Master_copy) and the same table structure for each DB.

I want to quickly select a table in Master_db and copy the table data to the Master_copy table with the same name. I came 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. Now the problem is that I have a Timestamp column and this made an error:

"Unable to insert an explicit value into a timestamp column. Use INSERT with a list of columns to exclude a timestamp column or insert a DEFAULT value into a 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 get around this error with this SP?

+2


a source to share


4 answers


Object names in SQL queries cannot be parameterized the way you are trying.

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;
+3


a source


My suggestion is to write a function that returns a table than to use the select insert statement



Difference between procedure and function than you cannot write   select procedure

, but you can write select function

+1


a source


Only dynamic SQL resolves variables as table names. For instance:

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
+1


a source


I agree with @EdHarper that creating a string and then running sp_executesql is probably the best answer.

Two things to keep in mind that I don't see in the previous answers:

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

2) If you have identity columns in Master_copy tables (because you used the same Sql script to generate Master_db and Master_copy tables, for example), you need to make sure that you can overwrite identity values ​​(this can be done by setting the identity_insert command in ) ...

So, as an alternative (there is no point in repeating the other answer), and since you know the database names, you can simply denormalize this approach and write a simple stored procedure for each table.

Say you have a table called "person" that 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

... etc. for each table.

+1


a source






More articles:


All Articles