SQL Server Generate Script does a great job of creating a script for the data in the tables using the Data Only option for 'Types of data to script' in the advanced option. However the script generated also includes all the identifiers such as rowid() and integer ids. Understandably this is for referential integrity, but is there a way to exclude such columns?
How do I exclude auto generated columns from SQL Server Generate Script Data Only?
3.9k views Asked by dchitnis At
2
There are 2 answers
0
On
There doesn't seem to be a way to do this in SQL Server, but a quick workaround is to create an initial script and then use that to create a temporary table. Then you can delete the columns you don't want and use the temporary table to generate a SQL statement for the remaining columns.
Let's say you want to copy an Events
table, but you don't want to include id
:
- Go to tasks > Generate Script and generate a SQL Server script from
Events
. In "Advanced" options, make sure that you are copying both data and schema. Also make sure you are not scripting primary keys or foreign keys (you will still see the columns in your SQL script, but this will make it easier to quickly delete them from the temporary table). - Open the generated SQL script in a text editor and use find and replace to change the name of the table, i.e. from
Events
toEventsTemporary
- Use this script in SQL Server to create and seed the
EventsTemporary
table. Delete the columns you don't want to copy from this table, such asid
. - Generate a second script from the
EventsTemporary
table, but this time just copy "data" without schema. Open this new SQL script in a text editor and change the name of the table back toEvents
- Use the second script in SQL Server to import the data into your actual
Events
table.
Not in SSMS itself.
You can use a 3rd party tool such as the free SSMS Tools pack which has a "generate script from grid results" option. So you can generate INSERTs for
SELECT col1, col3, col6 FROM MyTable
(skipping some columns).May be useful...