SQL Server - Generate script without primary key

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.


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:

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