How to Create Insert Statement from Table Data using sp_export_data without the Id? I have the following statements and want to omit the Identity. How would I accomplish this?

This is how I'm scripting the one row:

exec sp_export_data tblTableName
 , @from ="FROM tblTableName WHERE tblTableNameId = 123" 

This is how I am script the whole table:

exec sp_export_data tblTableName
 , @from ="FROM tblTableName"

Any & all feedback is appreciated.

2 Answers 2


If you are OK with using a slightly different approach (still T-SQL) go to this link and scroll down to the answer by Neeraj Prasad Sharma where he creates the stored procedure named INS.

Within that procedure you can add a where clause of something like table where id <> 1. Here is some example code.

First create your table and test records.

CREATE TABLE testNegative
    ,Col1 VARCHAR(200)
INSERT INTO dbo.testNegative
        ( Col1 )
VALUES  ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test5' ) ;

This will insert ID's 1-5 into the table. Now say that I want to script out every row other than ID 1. I would then run this:

EXEC INS ' dbo.testNegative where id <> 1';

This procedure has worked well for me in a number of scenarios. I think it will do what you are looking for here.


To Create Insert Statement from Table Data using sp_export_data without the Id the syntax is:

sp_export_data tblTableName
, @ommit_identity = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.