StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It sounds like you're using dynamic SQL in your stored procedure, so you're ready to dynamically create your list of columns in the SELECT clause.</p> <p>You can select from sys.columns to get the list of columns, and learn if the table has an identity column. Here's a query that shows the information you need to create the list of columns.</p> <pre><code>SELECT c.name, is_identity FORM sys.columns c WHERE object_id = object_id('MyTable') </code></pre> <p>In short, if <code>is_identity</code> is 1 for at least one column, you'll need to include the <code>SET IDENTITY_INSERT</code>. And, you would exclude any columns from the SELECT clause where <code>is_identity</code> = 1.</p> <p>And, this approach will adapt to new columns you add to the tables.</p> <p>Here's an example</p> <pre><code>DECLARE @TableName varchar(128) = 'MyTableName' DECLARE @ColumnName varchar(128) DECLARE @IsIdentity bit DECLARE @TableHasIdentity bit = 0 DECLARE @sql varchar(2000) = 'SELECT ' -- create cursor to step through list of columns DECLARE MyCurs CURSOR FOR SELECT c.name, is_identity FROM sys.columns c WHERE object_id = object_id(@TableName) ORDER BY column_id -- open cursor and get first row OPEN MyCurs FETCH NEXT FROM MyCurs INTO @ColumnName, @IsIdentity -- process each column in the table WHILE @@FETCH_STATUS = 0 BEGIN IF @IsIdentity = 0 -- add column to the SELECT clause SET @sql = @sql + @ColumnName + ', ' ELSE -- indicate that table has identity column SET @TableHasIdentity = 1 -- get next column FETCH NEXT FROM MyCurs INTO @ColumnName, @IsIdentity END -- cursor cleanup CLOSE MyCurs DEALLOCATE MyCurs -- add FROM clause SET @sql = LEFT(@sql, LEN(@sql)-1) + CHAR(13) + CHAR(10) + 'FROM ' + @TableName -- add SET IDENTITY if necessary IF @TableHasIdentity = 1 SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON' + CHAR(13) + CHAR (10) + @sql + CHAR(13) + CHAR (10) + 'SET IDENTITY_INSERT ' + @TableName + ' OFF' PRINT @sql </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload