Posted by carlospedr on 03/01/06 14:11

I have to insert data from about 30 tables into a single table (Users),
to do so i used a cursor and a bit of dynamic sql, this should work
fine if the tables have to do the select from had the same number of
columns, how ever they don't.
I only need the first 5 columns from each the table, in the case where
they have more than the 5 i need for my 'Users' table i get this error:
'An explicit value for the identity column in table 'Users' can only be
specified when a column list is used and IDENTITY_INSERT is ON.'.
Is there a way to select only the first five columns from a table, if
not is there another solution for my problem?
My Sql query is the following:

DROP TABLE Users
Create Table Users
(
ID INT identity PRIMARY KEY,
TIPO VARCHAR(255),
NOME VARCHAR(255),
USERNAME VARCHAR(255),
EMAIL VARCHAR(255),
GROUPID VARCHAR(255)

)
DECLARE @Table VARCHAR(255)
DECLARE @Sql VARCHAR(8000)
DECLARE sysCursor CURSOR
FOR
SELECT name FROM sysobjects where xtype='U'
OPEN sysCursor
FETCH NEXT FROM SysCursor into @Table
while @@FETCH_STATUS<>-1
BEGIN
/*
* INSERE VALORES NA TABELA DE TESTE
*/
SET @SQL = 'INSERT INTO Users
SELECT * FROM ['+ @Table +']'
EXECUTE (@SQL)
SET @SQL = 'UPDATE Users SET GROUPID=' + @Table +
'WHERE GROUPID IS NULL'
EXECUTE (@SQL)
print @table
FETCH NEXT FROM SysCursor INTO @Table
END
CLOSE sysCursor
/*
* APAGA VALORES INVÁLIDOS DA TABELA DE TESTE
*/
DELETE FROM Users WHERE TIPO IS NULL
DELETE FROM Users WHERE NOME='Nome'
DEALLOCATE sysCursor


I hope you can give me hand, thank you in advance.

[Back to original message]