How to put SET IDENTITY_INSERT dbo.myTable ON statement

c# sql-server identity-insert
samsam114 picture samsam114 · Jul 30, 2010 · Viewed 20.1k times · Source

What I need to do is have a SET IDENTITY_INSERT dbo.myTable ON statement, what's the syntax of using the above statement in a c# app?

Answer

Rob picture Rob · Jul 30, 2010

It's just the same as any other bit of SQL:

using (var connection = new SqlConnection("Connection String here"))
{
    connection.Open();
    var query = "SET IDENTITY_INSERT dbo.MyTable ON; INSERT INTO dbo.MyTable (IdentityColumn) VALUES (@identityColumnValue); SET IDENTITY_INSERT dbo.MyTable OFF;";
    using (var command = new SqlCommand(query, connection)
    {
        command.Parameters.AddWithValue("@identityColumnValue", 3);
        command.ExecuteNonQuery();
    }
}

Related questions

SqlBulkCopy Insert with Identity Column

I am using the SqlBulkCopy object to insert a couple million generated rows into a database. The only problem is that the table I am inserting to has an identity column. I have tried setting the SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity …

Read More
Sql Server Ce 3.5 Identity insert

got an issue with identity columns in Sql Server CE when using Server explorer, in VS2008, executing the following script SET IDENTITY_INSERT testTable ON; Insert into testTable (id,name) values (1,'Something') SET IDENTITY_INSERT testTable ON; sends the follow …

Read More
How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More