0

I need to write procedure Textprocedure (Table_txt varchar(200)) that scans data from table My_table For ex: I have table 

My_table (Id  int,  Name varchar(200))

I need result of procedure execution like output text (script) like 

DELETE FROM My_table 
INSERT INTO My_table (Id, Name) values (1, 'Tropico') 
INSERT INTO My_table (Id, Name) values (2, 'Bus') 
INSERT INTO My_table (Id, Name) values (4, 'Africa') 
INSERT INTO My_table (Id, Name) values (8, 'Arrival')

Can I do it with procedure? I do

CREATE TABLE My_table (Id  int,  Name varchar(200))

DELETE FROM My_table 
INSERT INTO My_table (Id, Name) values (1, 'Tropico') 
INSERT INTO My_table (Id, Name) values (2, 'Bus') 
INSERT INTO My_table (Id, Name) values (4, 'Africa') 
INSERT INTO My_table (Id, Name) values (8, 'Arrival')

ALTER PROCEDURE Textprocedure (@Table_txt varchar(200))
AS
BEGIN
DECLARE @Id1 NVARCHAR(40)
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = 'INSERT INTO '+ CAST(@Table_txt AS NVARCHAR(250)) + --@Result 
+'() values ' + CAST([name] AS NVARCHAR(250)) + ' ' FROM My_table --where Id<5
PRINT (@Result)
END
GO

EXEC Textprocedure My_table

My output: INSERT INTO My_table() values Arrival But I need result in many rows. Can I do cycle? And I need print table params like id, name? But how can I pick them?

streamc
  • 676
  • 3
  • 11
  • 27
  • What should Table_txt param do? – Szymon Oct 16 '13 at 04:57
  • 4
    Please don't just ask us to solve the problem for you. Show us how _you_ tried to solve the problem yourself, then show us _exactly_ what the result was, and tell us why you feel it didn't work. See "[What Have You Tried?](http://whathaveyoutried.com/)" for an excellent article that you _really need to read_. – John Saunders Oct 16 '13 at 05:14
  • OK. I would show you. – streamc Oct 16 '13 at 06:23
  • In generating the insert statements I suggest using the `QuoteName()` function around the table name, and you'll need single quotes around the value since it's a varchar. You can escape single quotes with another single quote. – Bridge Mar 19 '14 at 11:58

2 Answers2

0

Check this to generate insert statements:

https://stackoverflow.com/a/982576/590741

http://vyaskn.tripod.com/code/generate_inserts.txt

Community
  • 1
  • 1
BertrandJ
  • 29
  • 4
0

You can use dynamic sql with parameters then print or return the result.

Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26