SQL Server - Generate script without primary key

Question

I'm trying to make a generated script of my data (I mean, all the INSERT INTO commands). Because access permissions, I can't do a SET IDENTITY_INSERT TABLE OFF and ON (I'm using the user application in Staging)

So, there is a way to make this script in SQL Server Manager and avoid the field with the primary key? I set to false all properties (primary, unique, etc), but the script is still sending this field (For e.g., RecID 1, 2, 3, etc).

I'm using SQL Server 2012.

My configuration for the script:

Results I get:

SET IDENTITY_INSERT -TABLE- ON 
INSERT INTO TABLE (ID,Field1) VALUES (1,'value')

Any solution (except for removing it with Notepad++) is appreciated.

Answer 1

A bit of a work around, but sometimes useful quick and dirty way of doing these things:

SELECT 'INSERT INTO TABLE (Field1) VALUES (''' + Field1 + ''')' FROM TABLE

The result set will be a row for each insert statement for every row in the table TABLE. The INSERT statement is generated from concatenating the INSERT statement text with the values in Field1.

Answer 2

There is another way to do this which is a bit more automatic and a lot less faffy when you have a lot of columns of different data types; given a table T:

(
  ID int identity,
  C1 nvarchar(255),
  C2 datetime
  ...
)

...select everything except the identity column into a new table:

select C1, C2, ... into InterimTable from T

Then:

1. Run the Generate Scripts wizard on InterimTable.
2. Use whatever tool you have to search the SQL for InterimTable and replace with T
3. Run