|
|
Posted by Jack Vamvas on 03/01/06 16:37
You can select the first five columns like this:
select table_name, column_name, ordinal_position, data_type
from information_schema.columns WHERE table_name = 'myTable' and
ordinal_position < 6
Of course , from there you will need to construct the sql statement ,
assuming the column names are different.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<carlospedr@gmail.com> wrote in message
news:1141215075.052962.41310@v46g2000cwv.googlegroups.com...
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]
|