snippetsqlMinor
Generate Data Only Script without Identity SQL 2017
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
Currently it is doing
How can I generate a script without the ID?
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,
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
You can use,
Select machineno,amount into tmp_table
from tablenameNote 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 tablenameCode Snippets
Select machineno,amount into tmp_table
from tablenameSELECT 'INSERT INTO tablename (machineno,amount) values ('+convert(varchar(28),machineno)+','+convert(varchar(28),amount)+')'
FROM tablenameContext
StackExchange Database Administrators Q#214446, answer score: 2
Revisions (0)
No revisions yet.