3

I am using the script generator in SQL 2017 to create an INSERT script.

When generated, it includes the Identity which I don't want.

I have set Script Foreign Keys and Script Primary Keys to false but it is still including them.

My table is: ID (Identity), machnineno, amount

what I want to create is an INSERT script that looks like

INSERT tablename (machineno,amount) values (1789,15)
INSERT tablename (machineno,amount) values (3894,22)
INSERT tablename (machineno,amount) values (2097,9) 

Currently it is doing

INSERT tablename (id,machineno,amount) values (22,1789,15)
INSERT tablename (id,machineno,amount) values (45,3894,22)
INSERT tablename (id,machineno,amount) values (77, 2097,9)

How can I generate a script without the ID?

1
  • Do you really want to lose the identity column, or you are just not supplying an specific value and letting the table generate a new one?
    – EzLo
    Aug 9, 2018 at 6:35

1 Answer 1

2

Disabling to script the primary key and foreign key only restrict the generation of scripts creating these constraints. Identity is a table property. I would suggest (not efficient since duplicating data) to copy the column you required to a new table and script that table.

You can use,

Select machineno,amount into tmp_table 
from tablename

Note the new table created will not create constraints and triggers or you can script the DDL of the original table and create a dummy table without identity column in it and copy the data to the new table so it will retain the constraints

Then script the tmp_table using generate scripts. This will not have the identity column. You can change the table name as required after you have scripted it.

OR Scripting dynamically

SELECT 'INSERT INTO tablename (machineno,amount) values ('+convert(varchar(28),machineno)+','+convert(varchar(28),amount)+')'
FROM tablename 
1
  • that's exactly what I ended up doing. I copied the table, removed the identity and then scripted the table.
    – Rob
    Aug 16, 2018 at 4:00

Your Answer

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

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