How to Create Insert Statement from Table Data using sp_export_data without Id?

Question

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.

Answer 1

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
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,Col1 VARCHAR(200)
);
GO
INSERT INTO dbo.testNegative
        ( Col1 )
VALUES  ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test5' ) ;
GO

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.

Answer 2

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

sp_export_data tblTableName
, @ommit_identity = 1