HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Generate Data Only Script without Identity SQL 2017

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
scriptwithoutidentitysqlgenerate2017dataonly

Problem

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?

Solution

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

Code Snippets

Select machineno,amount into tmp_table 
from tablename
SELECT 'INSERT INTO tablename (machineno,amount) values ('+convert(varchar(28),machineno)+','+convert(varchar(28),amount)+')'
FROM tablename

Context

StackExchange Database Administrators Q#214446, answer score: 2

Revisions (0)

No revisions yet.