Inserting a default Value

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value.

Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset.

Any suggestions or examples where I can do this.

Many thanks in advance

A DataColumn has a DefaultValue property - seeMSDN for usage:

private void MakeTable()
{
// Create a DataTable.
DataTable table = new DataTable("Product");

// Create a DataColumn and set various properties.
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Decimal");
column.AllowDBNull = false;
column.Caption = "Price";
column.ColumnName = "Price";
column.DefaultValue = 25;

// Add the column to the table.
table.Columns.Add(column);

// Add 10 rows and set values.
DataRow row;
for(int i = 0; i < 10; i++)
{
row = table.NewRow();
row["Price"] = i + 1;

// Be sure to add the new row to the
// DataRowCollection.
table.Rows.Add(row);
}
}

Inserting a dataset into an sql table

I've cleaned up and parsed a delimited file into a dataset.
Now I want to add that dataset to an sql table.
Simple, right?
My code is below.
It runs, but no records from the parsed file are added to the table.
Can anyone look at this code and make it work?
Thanks
Paul
PS Bulk Insert is a nice idea, and if anyone knows how to use it to insert
my dataset, I'm all ears.
========================================
=========
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
namespace Bot_01
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private Bot_01.DataSet1 dataSet11;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.dataSet11 = new Bot_01.DataSet1();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(0, 0);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(88, 8);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(200, 256);
this.dataGrid1.TabIndex = 1;
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"SEC_Index_01", new System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
new
System.Data.Common.DataColumnMapping("FormType", "FormType"),
new
System.Data.Common.DataColumnMapping("CIK", "CIK"),
new
System.Data.Common.DataColumnMapping("DateFiled", "DateFiled"),
new
System.Data.Common.DataColumnMapping("SECWebAddress", "SECWebAddress")})});
this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT ID, CompanyName, FormType,
CIK, DateFiled, SECWebAddress FROM SEC_Index_01" +
"";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "INSERT INTO
SEC_Index_01(CompanyName, FormType, CIK, DateFiled, SECWebAddress) VA" +
"LUES (@.CompanyName, @.FormType, @.CIK, @.DateFiled, @.SECWebAddress);
SELECT ID, Com" +
"panyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
WHERE (ID = " +
"@.@.IDENTITY)";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.FormType",
System.Data.SqlDbType.VarChar, 8000, "FormType"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
8000, "CIK"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = @."UPDATE SEC_Index_01 SET
CompanyName = @.CompanyName, FormType = @.FormType, CIK = @.CIK, DateFiled =
@.DateFiled, SECWebAddress = @.SECWebAddress WHERE (ID = @.Original_ID) AND (CI
K
= @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS NULL) AND (CompanyName =
@.Original_CompanyName OR @.Original_CompanyName IS NULL AND CompanyName IS
NULL) AND (DateFiled = @.Original_DateFiled OR @.Original_DateFiled IS NULL AN
D
DateFiled IS NULL) AND (FormType = @.Original_FormType OR @.Original_FormType
IS NULL AND FormType IS NULL) AND (SECWebAddress = @.Original_SECWebAddress O
R
@.Original_SECWebAddress IS NULL AND SECWebAddress IS NULL); SELECT ID,
CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01 WHERE
(ID = @.ID)";
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.FormType",
System.Data.SqlDbType.VarChar, 8000, "FormType"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
8000, "CIK"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_ID", System.Data.SqlDbType.Int
,
4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CIK",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_FormType",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.ID", System.Data.SqlDbType.Int, 4,
"ID"));
//
// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = @."DELETE FROM SEC_Index_01 WHERE (ID
= @.Original_ID) AND (CIK = @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS
NULL) AND (CompanyName = @.Original_CompanyName OR @.Original_CompanyName IS
NULL AND CompanyName IS NULL) AND (DateFiled = @.Original_DateFiled OR
@.Original_DateFiled IS NULL AND DateFiled IS NULL) AND (FormType =
@.Original_FormType OR @.Original_FormType IS NULL AND FormType IS NULL) AND
(SECWebAddress = @.Original_SECWebAddress OR @.Original_SECWebAddress IS NULL
AND SECWebAddress IS NULL)";
this.sqlDeleteCommand1.Connection = this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_ID", System.Data.SqlDbType.Int
,
4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CIK",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_FormType",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
System.Data.DataRowVersion.Original, null));
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=AMD;packet
size=4096;integrated security=SSPI;data source=AMD;pers" +
"ist security info=False;initial catalog=SEC_XBRL_10";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
dataGrid1.DataSource=null; //Clear the
datagrid
sqlDataAdapter1.Fill(dataSet11,"SEC_Index_01"); //Fill result dataset
with SEC_Index_01 table data via the sqlDataAdapter1
//WebRequest req =
WebRequest.Create("http://www.sec.gov/Archives/edgar/daily-index/" +
"company." + "20041222" + ".idx"); // Create the Request object
//WebResponse response = req.GetResponse(); //Create the
Response object
//Stream stream = response.GetResponseStream(); //Create a
Stream
//StreamReader sr = new StreamReader(stream); //Open the file
in a stream reader
StreamReader sr = new StreamReader("C:\\test.txt"); //Read From
A File instead of a webrequest
dataSet11.Tables.Add("MyNewTable"); //Add DataTable to
hold the DataSet
dataSet11.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a
single column to the DataTable
dataSet11.Tables["MyNewTable"].Columns.Add("FormType"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("CIK"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a
single column
string AllData1 = sr.ReadToEnd(); //Read the rest
of the data in the file.
string[] rows = AllData1.Split("\n".ToCharArray()); //Split off
each row at the Line Feed
foreach(string r in rows) //Now add each
row to the DataSet
{
string delimStr1 = "\t";
string[] items = r.Split(delimStr1.ToCharArray()); //Split the row
at the delimiter
dataSet11.Tables["MyNewTable"].Rows.Add(items); //Add the item
}
for (int i = 1; i <= 11; i++) //Remove first 11
rows from the DataTable/DataSet
{
dataSet11.Tables["MyNewTable"].Rows.RemoveAt(0);
}
sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Insert the new
rows from the "result" dataset
//sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
update
dataGrid1.SetDataBinding(dataSet11, "MyNewTable"); //Binds DataGrid to
DataSet,displaying datatable.
MessageBox.Show("Data has been added to the SQL Table SEC_XBRL_Index");
}
}
}Here's the reply to your earlier post...
A quick browse through your code, you have this line:
sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
update
which sends the updates (i.e. insert, update, delete operation) to sqlserver
for SEC_Index_01 table.
You have to addrow to dataSet11.Tables["SEC_Index_01"] if you want the
inserts!
The gist of it is you would want to add this to your code before calling
da.update.
e.g.
foreach(DataRow dr in dataSet11.Tables["MyNewTable"].Rows)
{
DataRow r = dataSet11.Tables["SEC_Index_01"].NewRow();
r["CompanyName"] = dr["CompanyName"];
// ... do the same for the rest of your columns
dataSet11.Tables["SEC_Index_01"].Rows.Add(r);
}
-oj
"a" <a@.discussions.microsoft.com> wrote in message
news:35A6293F-CA8B-415E-9DEE-36ECD43B5F3D@.microsoft.com...
> I've cleaned up and parsed a delimited file into a dataset.
> Now I want to add that dataset to an sql table.
> Simple, right?
> My code is below.
> It runs, but no records from the parsed file are added to the table.
> Can anyone look at this code and make it work?
> Thanks
> Paul
> PS Bulk Insert is a nice idea, and if anyone knows how to use it to insert
> my dataset, I'm all ears.
> ========================================
=========
> using System;
> using System.Drawing;
> using System.Collections;
> using System.ComponentModel;
> using System.Windows.Forms;
> using System.Data;
> using System.Data.SqlClient;
> using System.Net;
> using System.IO;
> using System.Text;
> using System.Text.RegularExpressions;
> using System.Collections.Specialized;
> namespace Bot_01
> {
> /// <summary>
> /// Summary description for Form1.
> /// </summary>
> public class Form1 : System.Windows.Forms.Form
> {
> private System.Windows.Forms.Button button1;
> private System.Windows.Forms.DataGrid dataGrid1;
> private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
> private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
> private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
> private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
> private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
> private System.Data.SqlClient.SqlConnection sqlConnection1;
> private Bot_01.DataSet1 dataSet11;
> /// <summary>
> /// Required designer variable.
> /// </summary>
> private System.ComponentModel.Container components = null;
> public Form1()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
> //
> // TODO: Add any constructor code after InitializeComponent call
> //
> }
> /// <summary>
> /// Clean up any resources being used.
> /// </summary>
> protected override void Dispose( bool disposing )
> {
> if( disposing )
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> base.Dispose( disposing );
> }
> #region Windows Form Designer generated code
> /// <summary>
> /// Required method for Designer support - do not modify
> /// the contents of this method with the code editor.
> /// </summary>
> private void InitializeComponent()
> {
> this.button1 = new System.Windows.Forms.Button();
> this.dataGrid1 = new System.Windows.Forms.DataGrid();
> this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
> this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
> this.dataSet11 = new Bot_01.DataSet1();
> ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
> ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
> this.SuspendLayout();
> //
> // button1
> //
> this.button1.Location = new System.Drawing.Point(0, 0);
> this.button1.Name = "button1";
> this.button1.TabIndex = 0;
> this.button1.Text = "button1";
> this.button1.Click += new System.EventHandler(this.button1_Click);
> //
> // dataGrid1
> //
> this.dataGrid1.DataMember = "";
> this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
> this.dataGrid1.Location = new System.Drawing.Point(88, 8);
> this.dataGrid1.Name = "dataGrid1";
> this.dataGrid1.Size = new System.Drawing.Size(200, 256);
> this.dataGrid1.TabIndex = 1;
> //
> // sqlDataAdapter1
> //
> this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
> this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
> this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
> this.sqlDataAdapter1.TableMappings.AddRange(new
> System.Data.Common.DataTableMapping[] {
> new System.Data.Common.DataTableMapping("Table",
> "SEC_Index_01", new System.Data.Common.DataColumnMapping[] {
> new
> System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
> new
> System.Data.Common.DataColumnMapping("FormType", "FormType"),
> new
> System.Data.Common.DataColumnMapping("CIK", "CIK"),
> new
> System.Data.Common.DataColumnMapping("DateFiled", "DateFiled"),
> new
> System.Data.Common.DataColumnMapping("SECWebAddress",
> "SECWebAddress")})});
> this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
> //
> // sqlSelectCommand1
> //
> this.sqlSelectCommand1.CommandText = "SELECT ID, CompanyName, FormType,
> CIK, DateFiled, SECWebAddress FROM SEC_Index_01" +
> "";
> this.sqlSelectCommand1.Connection = this.sqlConnection1;
> //
> // sqlInsertCommand1
> //
> this.sqlInsertCommand1.CommandText = "INSERT INTO
> SEC_Index_01(CompanyName, FormType, CIK, DateFiled, SECWebAddress) VA" +
> "LUES (@.CompanyName, @.FormType, @.CIK, @.DateFiled, @.SECWebAddress);
> SELECT ID, Com" +
> "panyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE (ID = " +
> "@.@.IDENTITY)";
> this.sqlInsertCommand1.Connection = this.sqlConnection1;
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.FormType",
> System.Data.SqlDbType.VarChar, 8000, "FormType"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
> //
> // sqlUpdateCommand1
> //
> this.sqlUpdateCommand1.CommandText = @."UPDATE SEC_Index_01 SET
> CompanyName = @.CompanyName, FormType = @.FormType, CIK = @.CIK, DateFiled =
> @.DateFiled, SECWebAddress = @.SECWebAddress WHERE (ID = @.Original_ID) AND
> (CIK
> = @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS NULL) AND (CompanyName
> =
> @.Original_CompanyName OR @.Original_CompanyName IS NULL AND CompanyName IS
> NULL) AND (DateFiled = @.Original_DateFiled OR @.Original_DateFiled IS NULL
> AND
> DateFiled IS NULL) AND (FormType = @.Original_FormType OR
> @.Original_FormType
> IS NULL AND FormType IS NULL) AND (SECWebAddress = @.Original_SECWebAddress
> OR
> @.Original_SECWebAddress IS NULL AND SECWebAddress IS NULL); SELECT ID,
> CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE
> (ID = @.ID)";
> this.sqlUpdateCommand1.Connection = this.sqlConnection1;
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.FormType",
> System.Data.SqlDbType.VarChar, 8000, "FormType"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_ID",
> System.Data.SqlDbType.Int,
> 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
> ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CIK",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_FormType",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.ID", System.Data.SqlDbType.Int, 4,
> "ID"));
> //
> // sqlDeleteCommand1
> //
> this.sqlDeleteCommand1.CommandText = @."DELETE FROM SEC_Index_01 WHERE (ID
> = @.Original_ID) AND (CIK = @.Original_CIK OR @.Original_CIK IS NULL AND CIK
> IS
> NULL) AND (CompanyName = @.Original_CompanyName OR @.Original_CompanyName IS
> NULL AND CompanyName IS NULL) AND (DateFiled = @.Original_DateFiled OR
> @.Original_DateFiled IS NULL AND DateFiled IS NULL) AND (FormType =
> @.Original_FormType OR @.Original_FormType IS NULL AND FormType IS NULL) AND
> (SECWebAddress = @.Original_SECWebAddress OR @.Original_SECWebAddress IS
> NULL
> AND SECWebAddress IS NULL)";
> this.sqlDeleteCommand1.Connection = this.sqlConnection1;
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_ID",
> System.Data.SqlDbType.Int,
> 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
> ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CIK",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_FormType",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
> System.Data.DataRowVersion.Original, null));
> //
> // sqlConnection1
> //
> this.sqlConnection1.ConnectionString = "workstation id=AMD;packet
> size=4096;integrated security=SSPI;data source=AMD;pers" +
> "ist security info=False;initial catalog=SEC_XBRL_10";
> //
> // dataSet11
> //
> this.dataSet11.DataSetName = "DataSet1";
> this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
> //
> // Form1
> //
> this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
> this.ClientSize = new System.Drawing.Size(292, 273);
> this.Controls.Add(this.dataGrid1);
> this.Controls.Add(this.button1);
> this.Name = "Form1";
> this.Text = "Form1";
> ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
> ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
> this.ResumeLayout(false);
> }
> #endregion
> /// <summary>
> /// The main entry point for the application.
> /// </summary>
> [STAThread]
> static void Main()
> {
> Application.Run(new Form1());
> }
> private void button1_Click(object sender, System.EventArgs e)
> {
> dataGrid1.DataSource=null; //Clear the
> datagrid
> sqlDataAdapter1.Fill(dataSet11,"SEC_Index_01"); //Fill result dataset
> with SEC_Index_01 table data via the sqlDataAdapter1
> //WebRequest req =
> WebRequest.Create("http://www.sec.gov/Archives/edgar/daily-index/" +
> "company." + "20041222" + ".idx"); // Create the Request object
> //WebResponse response = req.GetResponse(); //Create the
> Response object
> //Stream stream = response.GetResponseStream(); //Create a
> Stream
> //StreamReader sr = new StreamReader(stream); //Open the file
> in a stream reader
> StreamReader sr = new StreamReader("C:\\test.txt"); //Read From
> A File instead of a webrequest
> dataSet11.Tables.Add("MyNewTable"); //Add DataTable to
> hold the DataSet
> dataSet11.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a
> single column to the DataTable
> dataSet11.Tables["MyNewTable"].Columns.Add("FormType"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("CIK"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a
> single column
> string AllData1 = sr.ReadToEnd(); //Read the rest
> of the data in the file.
> string[] rows = AllData1.Split("\n".ToCharArray()); //Split off
> each row at the Line Feed
> foreach(string r in rows) //Now add each
> row to the DataSet
> {
> string delimStr1 = "\t";
> string[] items = r.Split(delimStr1.ToCharArray()); //Split the row
> at the delimiter
> dataSet11.Tables["MyNewTable"].Rows.Add(items); //Add the item
> }
> for (int i = 1; i <= 11; i++) //Remove first 11
> rows from the DataTable/DataSet
> {
> dataSet11.Tables["MyNewTable"].Rows.RemoveAt(0);
> }
> sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Insert the new
> rows from the "result" dataset
> //sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
> update
> dataGrid1.SetDataBinding(dataSet11, "MyNewTable"); //Binds DataGrid to
> DataSet,displaying datatable.
> MessageBox.Show("Data has been added to the SQL Table SEC_XBRL_Index");
> }
> }
> }|||oj:
Thank You...your answer solved my problem. I'd buy you a drink if I could.
I suppose in retrospect that the answer is somewhat obvious, but not being
all that familiar with what code "I" needed to add to make the data adapters
work, it wasn't obvious to me at all.
Thanks,
Paul
========================================
=======
"oj" wrote:

> Here's the reply to your earlier post...
> A quick browse through your code, you have this line:
> sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
> update
> which sends the updates (i.e. insert, update, delete operation) to sqlserv
er
> for SEC_Index_01 table.
> You have to addrow to dataSet11.Tables["SEC_Index_01"] if you want the
> inserts!
> The gist of it is you would want to add this to your code before calling
> da.update.
> e.g.
> foreach(DataRow dr in dataSet11.Tables["MyNewTable"].Rows)
> {
> DataRow r = dataSet11.Tables["SEC_Index_01"].NewRow();
> r["CompanyName"] = dr["CompanyName"];
> // ... do the same for the rest of your columns
> dataSet11.Tables["SEC_Index_01"].Rows.Add(r);
> }
> --
> -oj|||You're welcome.
A virtual drink is welcome also. ;-)
-oj
"a" <a@.discussions.microsoft.com> wrote in message
news:82ACC91A-6558-4FBC-A06A-486477197C42@.microsoft.com...
> oj:
> Thank You...your answer solved my problem. I'd buy you a drink if I could.
> I suppose in retrospect that the answer is somewhat obvious, but not being
> all that familiar with what code "I" needed to add to make the data
> adapters
> work, it wasn't obvious to me at all.
> Thanks,
> Paul
> ========================================
=======
> "oj" wrote:
>
>

Inserting a Count into a Table

I am inserting data from one table into another. To simplify:

Table 1 has columns:
1.A, 1.B, 1.C, 1.D

Table 2 has columns
2.A, 2.B, 2.C, 2.D, 2.Tag

where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
Not a count of how many records for each combination.

Therefore, if Table 1 is

x - x - x - x
x - x - x - x
x - x - x - x
x - x - x - y
x - x - x - z

then, table 2 is

x - x - x - x - 1
x - x - x - y - 2
x - x - x - z - 3

anyone help me w/ the sql on that ?

i was thinking of putting a variable in the select statement, but was unable to increment it.

thanksWhat do 2.A and 2.B hold? Is 1.A and 1.B relevant?|||This could be done in a single select statements, but I refuse to think about it further until you explain why you would want to do such a loopy thing.|||Try this but I not sure my coding.
INSERT INTO table1 SELECT DISTINCT *FROM table2|||a counter, not a count

what sequence governs this counter, A and B?

could you perhaps explain why you want this weird data?|||Try this

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
Select Distinct *,Tag=(Select count(*) from (Select Distinct D from @.t) T1 where T1.D<=T2.D) from @.t T2

Madhivanan|||madhivanan, very nice try, but not quite right

add the following to your test data and see what happens :) :) :)

insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')|||r937

try this with different combinations

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Select Distinct *,Tag=(Select count(*) from (Select Distinct * from @.t) T1
where T1.A+T1.B+T1.C+T1.D<=T2.A+T2.B+T2.C+T2.D) from @.t T2

Madhivanan|||sorry, that's not right either ;)

add this to your data and see what happens --

insert into @.t values('y','y','x','x')|||r937,

Can you post the expected outcome for these data?

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Madhivanan|||sure

x x x x 1
x x x x 2
x x x x 3
x x x y 1
x x x z 1
x x y y 1
x x z y 1|||Purpose of craziness: i want to order the data when i select from that table.

for the below
Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

the new table should have:

x-x-x-x-1
x-x-x-y-2
x-x-z-y-3|||First, I don't understand why your output isn't:

x-x-x-x-1
x-x-x-y-2
x-x-x-z-3
x-x-y-y-4
x-x-z-y-5

Second, if you are relying on the order of the data in the table for your logic, you are letting yourself in for a heap-o-hurtin'.|||aargh, i think my understanding of this has been wrong all along

it should be like in post #13!!

okay, what about if you add these rows, then what do you get --

insert into @.t values('b','b','x','y')
insert into @.t values('c','c','x','z')|||oh ya..
1. the order should be that...(sorry, it is a friday)

2. i want it ordered as such when i do an extract and display into a report.l

thanks!
C

Inserting a column in an existing table

I have an existing table (see below).

----
[FormCode] [varchar] (4) NULL ,
[FiscalYear] [char] (4) NULL
----

I want to add the column below after the [FormCode] when my SPROC runs.
----
[FiscalMonth] [char] (2) NULL
----

Any ideas would be a big help?
TIF--use this to add the column

alter table MyTable
add FiscalMonth char (2)
go

--and this to drop the column

alter table MyTable
drop column FiscalMonth
go

Cheers|||Thanks for your response, however, I'm actually after adding a column in between existing columns.

So in this case, my new column FISCALMONTH will be added between FORMCODE and FISCALYEAR.

Tnx|||Why is important to have the ordinal position of your column correct?|||The quickest and easiest (at least in most cases) way to "insert" columns into a table is to put the columns wherever they fall and construct a view to order them the way you want them.

In relational algebra, columns have no order. In relational databases, the order of columns should be considered an anomoly, not an attribute.

A view on the other hand is a template for a result set, and columns do have an order in a result set.

-PatP|||I don't understand eather... Why do you need them in a specific order?|||Did you ever get an answer to this? I know that you can create a view to order your columns but it would be nice to do this in the table. No it doesn't matter from a DB perspective but it is cleaner if you are dealing with many columns.|||Why don't you go into design view of a table in Enterprise Manager, make your changes, and save the script.

I would also summarize that ALTER TABLE anything in SQL server produces ineffeciencies at the page level...

Read Nigel's great article on the subject

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

Inserting a block of numbers sequentially into a table

I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7, bu
t
don't have the experience to understand this enough. I also looked in
previous questions on this group, but find I still need help. I need to
insert a series of MSR (Medical Service Record) numbers into a table of
appointments over a selected date range. The last used MSR is recorded in
this table in column LastMSR:
CREATE TABLE [UserVars] (
[LastMSR] [int] NOT NULL ,
[RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
Beginning with LastMSR+1, I want to insert numbers sequentially into the
column MSR of the Appointments table (see below), selecting rows where MSR =
0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
CREATE TABLE [Appointment] (
[APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[APPT_DATE] [datetime] NULL ,
[RESOURCE_ID] [int] NOT NULL ,
[DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[START_TIME] [datetime] NULL ,
[DURATION] [datetime] NULL ,
[STATUS] [smallint] NOT NULL ,
[CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSR] [int] NOT NULL ,
[ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
DEFAULT (getdate()),
[ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Once I'm done, I want to write my highest assigned MSR back to table
UserVars; however, I don't want any other user to allocate a block of MSRs
until I'm done.
Thank you...My first question is: Do you want to single thread access to the table? In
2000, do something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
In 2005, look at rownumber()
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7,
>but
> don't have the experience to understand this enough. I also looked in
> previous questions on this group, but find I still need help. I need to
> insert a series of MSR (Medical Service Record) numbers into a table of
> appointments over a selected date range. The last used MSR is recorded in
> this table in column LastMSR:
> CREATE TABLE [UserVars] (
> [LastMSR] [int] NOT NULL ,
> [RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> ) ON [PRIMARY]
> GO
> Beginning with LastMSR+1, I want to insert numbers sequentially into the
> column MSR of the Appointments table (see below), selecting rows where MSR
> =
> 0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
> CREATE TABLE [Appointment] (
> [APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [APPT_DATE] [datetime] NULL ,
> [RESOURCE_ID] [int] NOT NULL ,
> [DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [START_TIME] [datetime] NULL ,
> [DURATION] [datetime] NULL ,
> [STATUS] [smallint] NOT NULL ,
> [CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSR] [int] NOT NULL ,
> [ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
> DEFAULT (getdate()),
> [ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> Once I'm done, I want to write my highest assigned MSR back to table
> UserVars; however, I don't want any other user to allocate a block of MSRs
> until I'm done.
> Thank you...|||Sorry, I don't understand. When I start, the appointments table might be
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 0
2 11/07/2005 0
etc.
If the last used MSR number (stored in UserVar table) is 20, then when I'm
done I want the appointments table to look like this:
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 21
2 11/07/2005 22
etc.
Starting with the stored LastMSR+1, each row increases MSR by 1.
"Louis Davidson" wrote:

> My first question is: Do you want to single thread access to the table?
In
> 2000, do something like:
> select 'Blue' as color
> into #testtable
> union all
> select 'Red'
> union all
> select 'Green'
> select color, (select count(*) from #testTable as t2 where t2.color <=
> #testTable.color) as rowNumber
> from #testTable
> order by 2
> In 2005, look at rownumber()
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>
>

INSERTING 20Mill Records into a Table with 100Mill Records..

Hi, Iam new to SQL Srvr 2005 with a Oracle Background..

I have three tables
Table 1 (100 Mill Rows)
Table 2 (20 Mill Rows)
Table 3 (10 Mill Rows)

INSERT INTO Table1
select Table2.* from Table 2
where exists (select 1 from Table3
where Table2.xyz = Table3.xyz
and Table2.abc = Table3.abc);

Whats the most efficient way to do this..
Iam already DISABL'ng the Indexes before the Insert on Table 1
Also -- Whats the SQLSRVR's equivalent to Rollback Segment?

I would suggest to use a join and be sure to have indexes in table2 and table3 by the columns used in the join.

- index on table2 by (xyz, abc)

- index on table3 by (xyz, abc)

The index could be also by (abc, xyz), but it will depend on the order of an existing constraint like primary key or foreign key, or in case there not a constraint, then the selectivity of those columns..

INSERT INTO Table1

select

Table2.*

from

Table 2 inner join Table3

on Table2.xyz = Table3.xyz and Table2.abc = Table3.abc

AMB

|||

Inserting 20 mil rows at one time will create a log of Transaction Log activity.

IF, and that is a big IF, this is a singular operation, and if there is no other activity in the database, you may wish to change the recovery model to 'SIMPLE' (after first making a backup.)

Then do the import in batches of 100k rows - this will greatly reduce the logging pressure and could make a radical difference in speed.

When finished, return the recovery model to the previous setting. AND then make a full backup.

|||

If you go with changing the Recovery Model to simple and back, you'll want to be sure to run a full back-up immediately after reverting back.

Switching to the Simple model breaks the log chain and a full back-up is required to establish a new chain.

|||

Thanks, Dale,

I should have explicitly mentioned that (assumptions, assumptions, etc.)

Inserting 1 row and getting a message that two rows were inserted.

Why does this code tell me that I inserted 2 rows when I really only inserted one? I am using SQL server 2005 Express. I can open up the table and there is only one record in it.

Dim InsertSQL As String = "INSERT INTO dbCG_Disposition ( BouleID, UserName, CG_PFLocation ) VALUES ( @.BouleID, @.UserName, @.CG_PFLocation )"

Dim StatusAs Label = lblStatusDim ConnectionStringAs String = WebConfigurationManager.ConnectionStrings("HTALNBulk").ConnectionStringDim conAs New SqlConnection(ConnectionString)Dim cmdAs New SqlCommand(InsertSQL, con) cmd.Parameters.AddWithValue("BouleID", BouleID) cmd.Parameters.AddWithValue("UserName", UserID) cmd.Parameters.AddWithValue("CG_PFLocation", CG_PFLocation)Dim addedAs Integer = 0Try con.Open() added = cmd.ExecuteNonQuery() Status.Text &= added.ToString() &" records inserted into CG Process Flow Inventory, Located in Boule_Storage."Catch exAs Exception Status.Text &="Error adding to inventory. " Status.Text &= ex.Message.ToString()Finally con.Close()End Try

Anyone have any ideas? Thanks

Change

Status.Text &=

To

Status.Text =

When you put &=, every time when you fire event, the text will be appended

(After I posted it, I realized probably it was able to solve your issue. Sorry for that).

|||

Yeah, somehow, added is getting set to 2 at the

added = cmd.ExecuteNonQuery()

step. I have a trigger set on this table to increase other rows with the same BouleID by one but that shouldn't

affect the ADO object should it?

There is this in the MSDN:

Although theExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

ForUPDATE, INSERT, and DELETE statements, the return value is the numberof rows affected by the command. When a trigger exists on a table beinginserted or updated, the return value includes the number of rowsaffected by both the insert or update operation and the number of rowsaffected by the trigger or triggers. For all other types of statements,the return value is -1. If a rollback occurs, the return value is also-1.

The problem is that these are new inserts into the table so the trigger is not affecting any other rows because they don't exist yet.

|||

Quite possibly. Do you have a SET NOCOUNT ON in your triggers?

|||

No, I dont'. I only have

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

set.

|||

In your trigger, right after the "AS", add "SET NOCOUNT ON". Then any queries the trigger does won't be seen. For example:

CREATE TRIGGER ...

AS

SET NOCOUNT ON

...

Inserted/deleted table.

Hi,

I am currently working on a MS SQL server 2000.

I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted -- are not accessible. anyone knows why? And is there any other way to do this?

Thankspost your t-sql code that you used to access the inserted/deleted tablessql

inserted value on text field gets truncated after 255 chars

Hello,
I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
statement over a table. In the table I have two 'text' fields with the
same properties, with just one difference: one field allows nulls, the
other one does not.
Well, one field actually accepts only the first 255 chars (the nullable
field), while the other field has no problems.
The "Length" property is set to 16 for both fields, as I said all the
properties but one (null/not null) are exactly the same, and also the
context is the same (same database, same table).
Many thanks for your help!
GiovanniHow does your SP look?
It sounds like you truncate it somewhere there. Maybe the parameter is
a varchar or something like that?|||How are you validating that only 255 characters are there? Are you using
SELECT DATALENGTH(col_name) FROM table? Or are you counting the number of
characters in the result set?
"gm1974" <gmascia@.gmail.com> wrote in message
news:1138737544.776002.219580@.f14g2000cwb.googlegroups.com...
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the nullable
> field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
> Many thanks for your help!
> Giovanni
>|||gm1974 wrote:
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the
> nullable field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
>
Are you testing it in QA? If so, you should modify the "maximum characters
per column" setting in the QA options dialog.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Oh, I must be really tired. I definitely forgot to change parameter
type in the SP, it was still set at VarChar(255), so the value was
truncated!
Better to get some sleep, many thanks for your help.|||Thanks for your help, it may be useful in the future.
Giovanni

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satish
Hi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default...b;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command

create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')

when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067

how i can i view the image?

pls clarify my doubt

satishUse WRITETEXT/READTEXT instead of INSERT/SELECT to store / retrieve
There is quite a detailed explanation in Books Online

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141732028.000941.183610@.e56g2000cwe.googlegr oups.com...
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
>
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
>
> pls clarify my doubt
>
> satish|||satish (satishkumar.gourabathina@.gmail.com) writes:
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?

You have not inserted the the image into the table. You have inserted the
disk location of the image into the table. Run

SELECT convert(varchar(80), d) FROM aa

to see.

There is no way to insert data into a column directly from a file. The
normal way of loading image is write a program that reads the file,
and the passes the binary stream through a parameterised INSERT statement
in a client API. You can also convert the contents to a hexstring and
build an INSERT statement from that.

Conversly, to display the image you also need an application. If you
have stored an image in a table, a SELECT on that table in Query Analyzer
or Mgmt Studio will only display a long hex string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql

Inserted Table when Inserting

Hi,

Now thanks to you good folks on here, I have recently found out that when inserting data into table, there is a system table which can be queried with triggers - specifically called "Inserted".

What I am wondering is what are the limitations of what I can do with the data in this table?
I know I can query it from within the trigger, but can I update data specifically in this table before it is inserted?
(ie IF field1 FROM inserted = 'blah' UPDATE inserted SET field2 = 'something')

If so is there anything that I need to look out for? Concerns? Etc?

Thanks in advance for your help

Cheersyou can use the data for comparisons or you can join the query argument in the trigger to the inserted and or the deleted tables

I have never updated them directly so i cant speak to that but i can suggest that anything that you might want to change in these virtual tables (Inserted\Deleted) could just as easily be changed in the triggered or evaluated table directly from the trigger code.

remember these tables contain data to give you a before and after look at the transaction that the trigger is a part of
(a trigger is implicitly part of the X-act that calls it)
so they dont technically exist when you are not in a X-act|||It is a very bad idea to try to modify either INSERTED or DELETED directly, they are implemented in "curious" ways. While you might be able to update them, it is still a very bad idea to do it.

-PatP|||Ahh haaa so if I understand correctly - what you are basically saying is that the information contained in this table, is ALREADY inserted into the table.
So if the file I was inserting had a PK field = 1234, and I wanted to update something in this file once it was inserted I could say something to the effect of:

update table1
set field1 = blah
from table1
where table1.field2 = inserted.field2

Rather than:

update inserted
set field1 = blah
from inserted

Hmm hopefully I have made a bit of sense here....

Thanks.|||Originally posted by Pat Phelan
It is a very bad idea to try to modify either INSERTED or DELETED directly, they are implemented in "curious" ways. While you might be able to update them, it is still a very bad idea to do it.

-PatP

The logical tables INSERTED and DELETED cannot be updated.|||E3xtc

yes that is the case
basically when you perform an insert on a table that has a trigger on it (for insert)
1 the row is inserted to the table
2 the row is also added into the "inserted" table
(which is only available to the xact that calls it)
3 the trigger actions are executed
4 commit or rollback

for deleted the same actions occur except the row to be deleted is added to the deleted table.

an update (in some cases) is a insert and a delete so there is no actual "updated" table
on an update the row as it existed before the update is added to the "deleted" table and the row with the updated column is added to the "inserted" table.

while the table exists(during trigger execution) you can query it just as you would any table.|||Originally posted by E3xtc
Ahh haaa so if I understand correctly - what you are basically saying is that the information contained in this table, is ALREADY inserted into the table. Yep, that you did understand that correctly!

The rows are modified first, placed in a pair of "non-corporeal" tables named INSERTED and DELETED. These tables can be freely modified in an INSTEAD OF trigger if the database compatibility level is set to 80. In the first releases of sp1 and sp3, and in several PSS hot fixes you could update the INSERTED and DELETED tables in any kind of trigger, with any database compatibility level. It is still a bad idea!

In general, it is considered "good form" to use a JOIN back to the primary (host) table to change the values of columns. This becomes much more important in the 64 bit version of SQL 2000, and will be even more so in Yukon.

-PatP|||brilliant!! Thanks all for your help - it is crystal clear now.

Much appreciated!

INSERTED table performance

I have a table with one UPDATE trigger. When I execute a one row update
command
to the table, Graphical Query Plan reports very slow select from INSERTED
table (900ms).
However, if I check the same command using Profiler, everything goes quickly
(duration 0 ms). Why is that? Which one should I trust, profiler or query
plan?I trust Profiler more than the Graphical Query Plan. I have seen some quite
strange costs and percentages in the Graphical Query Plan, specially when
objects are involved that don't exist at the beginning of the query, like
the inserted and deleted tables, temporary tables and table variables
--
Jacco Schalkwijk
SQL Server MVP
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Pexi,
Something that surprise me when I first found out. Inserted and deleted do
not exist, but are virtual tables that are populated each time you query
them by scanning the transaction log to extract the before and after images.
This is why the suggestion is to fill temp tables #inserted and #deleted if
you need to make repeated use of these tables.
Regarding the difference in the timings, the best way to measure is to
create a test. Do a loop calling your UPDATE repeatedly and logging the
milliseconds in a table.
SET @.BeginTime = GetDate()
EXEC YourTestStatement
INSERT INTO TrackingTable Values(@.BeginTime, GetDate())
Afterward you can analyze the results (and publish an article).
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Thanks for the replies! You kind of confirm my thinking:
never trust the query plan - it just tells fairy tales sometimes :)
pexi
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>

INSERTED table and triggers

Hi. I was dealing with triggers when a doubt came in mind.

While I can understand that the DELETED and UPDATED tables can contain more rows that have been affected by the DELETE or the UPDATE statment, the INSERTED table that I read in a "FOR INSERT" trigger has just 1 row or can have more rows?

Thanks.

many rows. Number of rows depended on how many rows get deleted / updated / inserted|||Image the query

INSERT INTO SomeTable
SELECT SomeCOlumn From ManyRowTable

That will bring up more than one row. bew also aware that the trigger is fired upon DML statement not per row, this means that a query like

INSERT INTO SomeTable
SELECT SomeColumn From SomeTable2 Where 1 = 0

also brings the trigger to fire.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

INSERTED table

I have a qn regarding the INSERTED table.

Whenever a row is inserted into the table i understand that the INSERTED table also gets that particular row. But how long does that particular row stay thr? Till another new row is inserted into the table? which means that rows get overwritten whenever a row is inserted?

Hope everyone understands what i am trying to say. Would be kind of you to reply.thanks!

GayathriPlease provide an example of the sql.|||SELECT JobNumber
from inserted
where ServiceType = 'On-site' and ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from inserted)

when i execute this statement alone i will only get one job number but when i put this into a trigger and channel the output into a cursor to copy it into a variable it selects some other job number as well...This is the whole code

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
from inserted
where ServiceType = 'On-site' and ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

I was hoping to get just one output since i thought the INSERTED table only contains the last inserted row.|||I understand what you are asking...

Basically you are saying,...

When a table has a trigger on it the trigger has access to a table called inserted (assuming it is an insert trigger). How long does the inserted table with the insert record exist...

In all honesty, I'm not sure, but I would say it would exist until the insert and the associated trigger (if there is one) has been completed...

I'll look up some resources and see what I can find.|||Um,... question,... when you use your cursor are you doing an update or insert into the table with the trigger on it??|||I am inserting|||okie,... so lets think about that for a sec...

you are in the middle of an insert, your trigger fires which opens a cursor which does an insert (loop to start and insert a new record into the inserted table)

your cursor is still open when you do your second insert and it references the same inserted table... which now has the new record in it...

does that make sense??|||Check out your bol:

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.|||and I assume they get cleaned out once the insert is complete... including trigger execution...|||rnealejr ,I understand that INSERTED table stores copies of the rows inserted into the actual table...

So does this mean that everytime an insert or update statement is executed a new inserted table is formed?|||I think the table remains but the rows are removed after the action is completed.

The reason I think this is because according to the BOL you can reference the deleted table when doing an insert and the inserted table when doing a delete but there are no rows contained in the tables...

"When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases."|||You can have more than 1 record in the inserted table and the table is only accessible to the trigger - so the table exists as long as the trigger runs for a particular sql statement.|||So does this mean that everytime an insert or update statement is executed a new inserted table is formed?

These tables are created/stored in memory. From what I remember, I believe the scope of these virtual tables are for the life of the trigger. It would not make sense that ss would keep a table in memory any longer than needed.|||It's highly possible that they continue to exist after their usefulness has gone, after all we are talking a microsoft product and they have done stranger things in the past.

Also the amount of memory you are talking about is minimal so the effect of keeping the table alive in memory is unlikely to cause any real problems.

In fact, it is likely that the over head involved in creating the tables each time if more detrimental then kepeing them in memory especially when you consider that you are likely to do multiple updates/inserts/deletes on any given table at a time rather then constant swap around tables ...|||The inserted and deleted tables exist only within the scope of the trigger execution. Updates use both the inserted and deleted tables because they effectively insert new modified copies of the records and then delete the old ones.

gayamantra, the inserted table does not exist as a distinct and persistent object. Keep in mind that it has the same record format as whatever datatable was the subject of the operation.

New virtual tables of inserted and deleted records must be created (in memory only) for each operation, otherwise multiple users accessing the datatable would end up with their inserted/deleted records intermingling.

I would guess that there is little additional overhead in creating these virtual tables on the fly, because they may be incidental to the database server's operations anyway.|||"New virtual tables of inserted and deleted records must be created (in memory only) for each operation, otherwise multiple users accessing the datatable would end up with their inserted/deleted records intermingling."

Not necessarily, the tables could be created with a user context eg. they are specific to the user at the time... I don't really know though... lets be honest, there are quite a few methods that MS could be using... but for the nature of this discussion the inserted and deleted table exist for the duration of the insert or delete. :Dsql

inserted table

Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database AdministratorThe inserted table can indeed have > 1 row in it and you code should take
this into account. Likely, you don't need a cursor either.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database Administrator|||Don't use a cursor in a trigger, typically people do something like this:
update table set column = value where prinmarykey = (select primary key from
inserted)
If you will have multiple updates or inserts you would want to change it to
this
update table set column = value where prinmarykey IN (select primary key
from inserted)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Is it ever possible for the inserted table to have more than one row in a
> for update trigger? One of our devs recently put a cursor in his for
update
> trigger to loop over rows in the inserted table. However, from what I
> understand, inserted should never have more than one row in it. I just
> wanted to verify this before I removed it as I am working on optimizing
it.
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
>|||I've been able to do that in every case except where the ID value from the
cursor is being passed into a udf that returns a table.. For example:
insert into sometable (column1, column2)
select distinct @.CursorValue, pgr.ID
from someUDF(@.CursorValue) as pgr
I tried changing this to:
insert into sometable(column1, column2)
select distinct i.ID, pgr.ID
from someUDF(i.ID) as pgr,
inserted i
but that didn't work because it expects a single deterministic value to be
passed into the UDL.. It appears that was why the original dev chose to use
a cursor in the trigger to handle this in the first place. Any ideas on how
to do this without the cursor?
Thanks!
Brent Black
Onvia.com
Technical Lead/Database Administrator
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> Don't use a cursor in a trigger, typically people do something like this:
> update table set column = value where prinmarykey = (select primary key
from
> inserted)
> If you will have multiple updates or inserts you would want to change it
to
> this
> update table set column = value where prinmarykey IN (select primary key
> from inserted)
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Brent Black" <bblack@.onvia.com> wrote in message
> news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
a
> update
> it.
>|||What's the UDF look like?
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:ucGCsKN9DHA.1936@.TK2MSFTNGP12.phx.gbl...
> I've been able to do that in every case except where the ID value from the
> cursor is being passed into a udf that returns a table.. For example:
> insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
> I tried changing this to:
> insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to
be
> passed into the UDL.. It appears that was why the original dev chose to
use
> a cursor in the trigger to handle this in the first place. Any ideas on
how
> to do this without the cursor?
> Thanks!
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
this:
> from
> to
in
> a
just
optimizing
>|||Brent,
I wouldn't be surprised if in this case the UDF is something like
create function someUDF(
@.v somedatatype
) returns table ...
WHERE someColumn = @.v
...
If that's the case, then the trigger could probably be written by
joining the inserted
table with whatever the current UDF applies its WHERE clause to, or with
not much more work than that.
In other words, as Ray said, what does the UDF (and the trigger) look like?
SK
Brent Black wrote:

>I've been able to do that in every case except where the ID value from the
>cursor is being passed into a udf that returns a table.. For example:
>insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
>I tried changing this to:
>insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to be
>passed into the UDL.. It appears that was why the original dev chose to us
e
>a cursor in the trigger to handle this in the first place. Any ideas on ho
w
>to do this without the cursor?
>Thanks!
>Brent Black
>Onvia.com
>Technical Lead/Database Administrator
>"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
>news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>from
>
>to
>
>a
>
>
>|||Hi Brent,
Thank you for using the newsgroup.
Here is an example for your reference, you could run in your Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL ,
[test_column] varchar(2)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[author_fun]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[author_fun]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function author_fun(@.state varchar(30))
returns table
as
return(select * from authors where @.state=authors.state
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
truncate table authorsx
insert into authorsx select *,1 from author_fun('CA')
select * from authorsx
go
drop table authorsx
So, I agree with Ray that if your the value returned by the UDF is match
the column you want to insert to or not.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Brent,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. For any questions, please feel free to post new
message here and I am glad to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

inserted table

Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database AdministratorThis is a multi-part message in MIME format.
--=_NextPart_000_01FB_01C3F484.E84A26E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
The inserted table can indeed have > 1 row in it and you code should take
this into account. Likely, you don't need a cursor either.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database Administrator
--=_NextPart_000_01FB_01C3F484.E84A26E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The inserted table can indeed have => 1 row in it and you code should take this into account. Likely, you don't =need a cursor either.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brent Black" wrote in =message news:uhVRS4K9DHA.3404=@.TK2MSFTNGP09.phx.gbl...Is it ever possible for the inserted table to have more than one row in =afor update trigger? One of our devs recently put a cursor in his for updatetrigger to loop over rows in the inserted table. =However, from what Iunderstand, inserted should never have more than one row in =it. I justwanted to verify this before I removed it as I am working on optimizing it.Brent BlackOnvia.comTechnical =Lead/Database Administrator

---

--=_NextPart_000_01FB_01C3F484.E84A26E0--|||Don't use a cursor in a trigger, typically people do something like this:
update table set column = value where prinmarykey = (select primary key from
inserted)
If you will have multiple updates or inserts you would want to change it to
this
update table set column = value where prinmarykey IN (select primary key
from inserted)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Is it ever possible for the inserted table to have more than one row in a
> for update trigger? One of our devs recently put a cursor in his for
update
> trigger to loop over rows in the inserted table. However, from what I
> understand, inserted should never have more than one row in it. I just
> wanted to verify this before I removed it as I am working on optimizing
it.
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
>|||I've been able to do that in every case except where the ID value from the
cursor is being passed into a udf that returns a table.. For example:
insert into sometable (column1, column2)
select distinct @.CursorValue, pgr.ID
from someUDF(@.CursorValue) as pgr
I tried changing this to:
insert into sometable(column1, column2)
select distinct i.ID, pgr.ID
from someUDF(i.ID) as pgr,
inserted i
but that didn't work because it expects a single deterministic value to be
passed into the UDL.. It appears that was why the original dev chose to use
a cursor in the trigger to handle this in the first place. Any ideas on how
to do this without the cursor?
Thanks!
Brent Black
Onvia.com
Technical Lead/Database Administrator
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> Don't use a cursor in a trigger, typically people do something like this:
> update table set column = value where prinmarykey = (select primary key
from
> inserted)
> If you will have multiple updates or inserts you would want to change it
to
> this
> update table set column = value where prinmarykey IN (select primary key
> from inserted)
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Brent Black" <bblack@.onvia.com> wrote in message
> news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Is it ever possible for the inserted table to have more than one row in
a
> > for update trigger? One of our devs recently put a cursor in his for
> update
> > trigger to loop over rows in the inserted table. However, from what I
> > understand, inserted should never have more than one row in it. I just
> > wanted to verify this before I removed it as I am working on optimizing
> it.
> >
> > Brent Black
> > Onvia.com
> > Technical Lead/Database Administrator
> >
> >
>|||What's the UDF look like?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:ucGCsKN9DHA.1936@.TK2MSFTNGP12.phx.gbl...
> I've been able to do that in every case except where the ID value from the
> cursor is being passed into a udf that returns a table.. For example:
> insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
> I tried changing this to:
> insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to
be
> passed into the UDL.. It appears that was why the original dev chose to
use
> a cursor in the trigger to handle this in the first place. Any ideas on
how
> to do this without the cursor?
> Thanks!
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> > Don't use a cursor in a trigger, typically people do something like
this:
> >
> > update table set column = value where prinmarykey = (select primary key
> from
> > inserted)
> >
> > If you will have multiple updates or inserts you would want to change it
> to
> > this
> >
> > update table set column = value where prinmarykey IN (select primary key
> > from inserted)
> >
> > HTH
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Brent Black" <bblack@.onvia.com> wrote in message
> > news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Is it ever possible for the inserted table to have more than one row
in
> a
> > > for update trigger? One of our devs recently put a cursor in his for
> > update
> > > trigger to loop over rows in the inserted table. However, from what I
> > > understand, inserted should never have more than one row in it. I
just
> > > wanted to verify this before I removed it as I am working on
optimizing
> > it.
> > >
> > > Brent Black
> > > Onvia.com
> > > Technical Lead/Database Administrator
> > >
> > >
> >
> >
>|||Brent,
I wouldn't be surprised if in this case the UDF is something like
create function someUDF(
@.v somedatatype
) returns table ...
WHERE someColumn = @.v
...
If that's the case, then the trigger could probably be written by
joining the inserted
table with whatever the current UDF applies its WHERE clause to, or with
not much more work than that.
In other words, as Ray said, what does the UDF (and the trigger) look like?
SK
Brent Black wrote:
>I've been able to do that in every case except where the ID value from the
>cursor is being passed into a udf that returns a table.. For example:
>insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
>I tried changing this to:
>insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to be
>passed into the UDL.. It appears that was why the original dev chose to use
>a cursor in the trigger to handle this in the first place. Any ideas on how
>to do this without the cursor?
>Thanks!
>Brent Black
>Onvia.com
>Technical Lead/Database Administrator
>"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
>news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>>Don't use a cursor in a trigger, typically people do something like this:
>>update table set column = value where prinmarykey = (select primary key
>>
>from
>
>>inserted)
>>If you will have multiple updates or inserts you would want to change it
>>
>to
>
>>this
>>update table set column = value where prinmarykey IN (select primary key
>>from inserted)
>>HTH
>>--
>>Ray Higdon MCSE, MCDBA, CCNA
>>--
>>"Brent Black" <bblack@.onvia.com> wrote in message
>>news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
>>
>>Is it ever possible for the inserted table to have more than one row in
>>
>a
>
>>for update trigger? One of our devs recently put a cursor in his for
>>
>>update
>>
>>trigger to loop over rows in the inserted table. However, from what I
>>understand, inserted should never have more than one row in it. I just
>>wanted to verify this before I removed it as I am working on optimizing
>>
>>it.
>>
>>Brent Black
>>Onvia.com
>>Technical Lead/Database Administrator
>>
>>
>>
>
>|||Hi Brent,
Thank you for using the newsgroup.
Here is an example for your reference, you could run in your Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL ,
[test_column] varchar(2)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[author_fun]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[author_fun]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function author_fun(@.state varchar(30))
returns table
as
return(select * from authors where @.state=authors.state
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
truncate table authorsx
insert into authorsx select *,1 from author_fun('CA')
select * from authorsx
go
drop table authorsx
So, I agree with Ray that if your the value returned by the UDF is match
the column you want to insert to or not.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Brent,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. For any questions, please feel free to post new
message here and I am glad to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.