Sql-server – Script to generate insert statements

sql server

I am trying to write an SQL script to create an insert statement for each row of table TEST which has field A (int) and field B varchar(30), in my MS SQL database.

I would like to save those insert statements into a txt file to be able to apply that data back to another database.

Any idea about how to do it?

Best Answer

Right-click on your Database in Object Explorer
Select Tasks -> Generate Scripts...
In that wizard you will be able to select the desired object(s) that you'd like to script out
The next portion has an Advanced button, click that
Under General, you will see the option called Types of data to script. The options are Schema only, Schema and data, or Data only. I believe you want the last option (Data Only)

Related Solutions

Sql-server – SSIS Script Component – How to modify the Output0Buffer

Everything in a Transformation is done within Input0_ProcessInputRow The solution would essentially be

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData 
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime
         });
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
}

You may be able to perform the output steps in PostExecute, CreateNewOutputRows is not run in Transformation, only in Destination scripts.

Sql-server – sp_executesql adds statements to executed dynamic script

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx

Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking.

http://msdn.microsoft.com/en-us/library/bb630284.aspx

Best Answer

Related Solutions

Sql-server – SSIS Script Component – How to modify the Output0Buffer

Sql-server – sp_executesql adds statements to executed dynamic script

Related Question