April 25, 2012 at 4:12 am
I have a table which contains 5 records in it.Now i want to see the insert query used in inserting the records.
I am new to sql & want to know whether there is any way by which sql generates that query automatically for me
.Thanks in advance
April 25, 2012 at 5:08 am
You can get a generated query. Within SQL Server Management Studio (SSMS) right click on the table and select "Script Table As" from the context menu. You'll see a second menu with "INSERT To." Select that and you'll get another context menu for the destination of your script. Output it to the new query window and you'll see the generated INSERT statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2012 at 5:11 am
I don't want that insert generate script.I want to see which insert query is used in inserting data into the table.
April 25, 2012 at 5:11 am
Yes you can do that. Right click on database, then go to 'tasks' and then 'generate script'
There at one of the screen you can specify, generate insert script. it will create insert statement for all records.I think this feature is applicable since sql 2005 onwards...
Another option is to write a query, that generates the statemnt for you. If you are not able, let me know and i'll write that.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 5:14 am
I want to see the insert script used along with the data that is used in the creation of that table.Can you please
write any query or procedure for to solve my query.
April 25, 2012 at 5:14 am
How does it matter how it was written? I think the only probability of getting it is if it still lies in your cache.
manoj_lanser (4/25/2012)
I don't want that insert generate script.I want to see which insert query is used in inserting data into the table.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 5:18 am
http://www.codeproject.com/Articles/15778/Generating-Insert-or-Update-statements-from-table
__________________________
Allzu viel ist ungesund...
April 25, 2012 at 5:19 am
manoj_lanser (4/25/2012)
I want to see the insert script used along with the data that is used in the creation of that table.Can you pleasewrite any query or procedure for to solve my query.
You can't get the actual script used to insert the data or to create the table
The only option is to get it manually from the person who did the action, if he/she still has it
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2012 at 5:23 am
This was removed by the editor as SPAM
April 25, 2012 at 5:37 am
Yes, and it will only work if plan for your query still lies there....
If it is flushed out, you won't getanything and then there is no way to get the query back.
Stewart "Arturius" Campbell (4/25/2012)
The simplest way to see which query inserts data into a table is by running SQL Profiler.To see what queries have already been executed , try using DMV's, e.g.
select * from sys.dm_exec_cached_planscross apply sys.dm_exec_query_plan(Plan_handle)
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 5:38 am
manoj_lanser (4/25/2012)
I don't want that insert generate script.I want to see which insert query is used in inserting data into the table.
Totally different.
If the inserts have already happened, the only thing you can do is query the cache through the dynamic management objects (DMO) such as sys.dm_exec_query_stats. If you want to watch the inserts as they occur, yeah, you can use Profiler, but don't point the Profiler gui at a production environment, ever. Instead look up how to generate a server-side trace. But, since you're working in SQL Server 2008, an even better option is to use the extended events. They're safer even than a trace, but the output is hard to read.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2012 at 5:48 am
Mr. Holio (4/25/2012)
http://www.codeproject.com/Articles/15778/Generating-Insert-or-Update-statements-from-table
I think that this is what the OP is after - not some complex forensic solution. Time will tell.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply