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:

enter image description here

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.

FlipperPA · Accepted Answer · 2013-05-14 18:55:36Z

16

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.

answered May 14, 2013 at 18:55

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Leandro Bardelli Over a year ago

OMG, it was so easy that I never thought about it! I was thinking in make some SP with fetch cursor and etc. LOL. Thanks a lot.

FlipperPA Over a year ago

It comes in handy in many situations when you need to transform data and don't have regex handy... it has saved me several times over the years!

Leandro Bardelli Over a year ago

and what about nulls? I've some records with null (smalldatetime field)

FlipperPA Over a year ago

@LeandroBardelli I missed this years ago, but have a look at the COALESCE function to map your NULLs to a string "NULL".

Whelkaholism · Accepted Answer · 2018-08-14 09:23:37Z

8

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:

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

answered Aug 14, 2018 at 9:23

Whelkaholism

1,5053 gold badges18 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Server - Generate script without primary key

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related