October 18, 2013 at 7:13 am
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
3. Expected results based on the sample data
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
1. Sample DDL in the form of CREATE TABLE statements
CREATE TABLE My_table (Id int, Name varchar(200))
DELETE FROM My_table
2. Sample data in the form of INSERT INTO statements
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')
4. My Actions
CREATE 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?
October 18, 2013 at 8:08 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2013 at 8:13 am
I reedit my post with description
October 18, 2013 at 8:29 am
OK I see what you are trying to do now. Why do you need to generate the insert statements like this? Wouldn't it be easier to just insert the data in your proc? What you are asking for is no small feat because you have to take into account various datatypes and such. Yes it can be done but I would ask what you are really trying to do and see if there is a better approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2013 at 8:37 am
Sean Lange, hi.
This is my job.
How can I realize output as many rows with all id but not one last id output that I have?
And if I do not know that params of table is id and name how can I retrieve them?
October 18, 2013 at 8:44 am
wwwarp (10/18/2013)
Sean Lange, hi.This is my job.
How can I realize output as many rows with all id but not one last id output that I have?
And if I do not know that params of table is id and name how can I retrieve them?
I am not sure what you are asking here. Can you try to clarify?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2013 at 11:56 am
Found this post :
Might help you.
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply