-1

I have a stored procedure that produces a script of insert statements as result. I want to execute those statements so that I can fill the data in my table

For example :

SET NOCOUNT ON

INSERT INTO table ([UID], [Name])
VALUES ('1000002', 'name'),
       ('1000004', 'name2')

The stored procedure return this as result, I want to execute this script how do I do it?

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Zain Abbas
  • 111
  • 3
  • 14
  • What does your code have to do with a stored procedure? – Gordon Linoff Apr 23 '17 at 12:12
  • Probably you are returning string from stored proc. like `print @qyery`. Instead of this do this `exec @query` – Lali Apr 23 '17 at 12:13
  • Actually i want to run a job that generates script of the table with data and create that table to another database ... i am able to generate the script through store procedure used this example http://stackoverflow.com/questions/5065357/how-to-generate-an-insert-script-for-an-existing-sql-server-table-that-includes – Zain Abbas Apr 23 '17 at 12:14
  • Seems odd - why don't you just **make the `INSERT`** inside your stored procedure? Why returns a SQL statement that then needs to be executed ..... seems superfluous .... – marc_s Apr 23 '17 at 13:33

2 Answers2

0

If I understand correctly, the above code is printed out when the stored procedure runs.

I would start by modifying the stored procedure to take an output parameter. Stored procedures "return" integer values, not messages. They happen to print things out, but it is not good to depend on that behavior.

Then, you can simply do:

declare @str nvarchar(max);

exec <stored procedure> @str output;

exec @str;

I suspect there might be a better way to structure the code. Having stored procedures return code should only be done when you really understand what you are doing. I don't think I've ever written code where a stored procedure would return an insert statement. It seems better to run dynamic SQL in the stored procedure itself.

EDIT:

You can capture the output of the stored procedure as well:

declare @t table (id int identity, line nvarchar(max));

insert into @t(line)
    exec(<stored procedure>);

Then, you can concatenate the values. You can use XML for this purpose, but I think this will work:

declare @str nvarchar(max);
set @str = '';

select @str = @str + line + ' '
from @t
order by id;

exec @str;
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • http://github.com/drumsta/sql-generate-insert this returns the message @Gordon Linoff – Zain Abbas Apr 23 '17 at 12:21
  • @ZainAbbas . . . Why not just capture the output and run it in a SSMS window? – Gordon Linoff Apr 23 '17 at 12:37
  • @ZainAbbas, `PRINT` statement output cannot be easily captured in T-SQL. You would need to jump through hoops like a SQLCLR wrapper. Consider changing the proc to return the entire script as an nvarchar(MAX) output parameter. – Dan Guzman Apr 23 '17 at 12:44
0

Thanx All of you but following is the way i should have done 

USE db1;

SELECT *
INTO [table]
FROM [db2].[dbo].[table]

it helps me to copy a table

Zain Abbas
  • 111
  • 3
  • 14