Table structure duplication
-
Hello,
not being a SQL expert, I'm currently writting some stored procedure. I need to create a temporary table with exactly the same structure as an already existing one (via the 'SELECT * INTO
FROM '), but I'd like the identity constraint to be removed from the . I read all I could concerning the 'SET IDENTITY_INSERT
ON', but this does not help since I want my stored procedure to be generic and I cannot afford naming all columns when inserting (I get the 'An explicit value for the identity column in table ' ' can only be specified when a column list is used and IDENTITY_INSERT is ON.'). 1. First of all: are you definitively positive on the fact that you cannot remove an identity constraint in T-SQL?
2. Is there a GENERIC way of creating a
from an , with exactly the same structure, that is initialized empty, and then fulfilled with some rows of the table? Thank you so much in advance for all your enlightment!
Edouard
-
Hi,
you can create a table with the same structure, initialized empty from existing one with
select * into TAB2 from TAB1 where 1=2
but identity column will be also created
Kindest Regards,vbenus
-
Hi, you can also build a new table based on a cursor with following SELECT:
SELECT
column_name=c.name,
datatype=t.name,
length=c.length
FROM sysobjects AS o
INNER JOIN syscolumns AS c
ON o.id = c.id
INNER JOIN systypes AS t
ON c.xtype=t.xtype
WHERE o.xtype='U'
ORDER BY o.name,c.colid
wich return column name with type and length
HTH
Gigi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply