By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Insert first n columns from select

P: n/a
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.

Mar 1 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
<ca********@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.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.
Mar 1 '06 #2

P: n/a
That's what i needed!! thanks.

Mar 1 '06 #3

P: n/a
You have no key on Users and no way to get a key since all the columns
are NULL-able. IDENTITY cannot be a key **by definition**. Next, why
are all the columns set to a magical VARCHAR (255) data type? How much
research did you do to come up with that number? Why are there no
constraints on any column?

Why do you use such vague data element names, like "tipo" (of what?),
"email" (address? provider? what?)

Again, going back to the basics, a table has a fixed number of columns
**by definition**. Your request makes no sense in an RDBMS. You can
insert a defualt value into a column, but not just "the first 5 columns
from each the table".

If you have a proper RDBMS design, you should never need them in
99.9999% of the time. I am also trying to figure how the same data got
into 30+ tables. A table should model one and only one kind of entity.
Next, stop using cursors and dynamic SQL. It says that you have a file
system written in SQL instead of an RDBMS. Your use of IDENTITY is a
replacment for a sequentail record number. You will wind up locating
your columns by ordinal positions, instead in by names. That is a
sequential file!! Why use SQL, if you are goiing to program like this?

Mar 2 '06 #4

P: n/a
his NESS wrote:
"IDENTITY cannot be a key **by definition**. "
Huh????

Mar 2 '06 #5

P: n/a
Read Dr. Codd or anyone else in RDBMS. Apparently none of the newbies
today do.

A key is **by definition** a subset of attributes of an entity. The
proprietary IDENTITY is an exposed physical locator derived from the
internal state of the hardware used to store that data. Or, to put it
another way, go to the real world that you are modeling and show me
where God put an IDENTITY number on the entities.

You have no way to validate or verify an IDENTITY because it is exposed
physical locator. It is a replacement for a SEQUENTIAL FILE record
number. It is totally non-relational.

Mar 3 '06 #6

P: n/a
I will explain,
I started with and excel file, when i imported it to sql server i ended
up with 30 odd tables, everyone of them had different column names
result of different headers in the excel file,
each of them, for some strange reason that i'm still to understand,
with a diferent number of columns but the data was present was allways
present on the first 5 columns
My intention is/was to transform this tables into a RDB, the first sept
i took was to build a sigle table with all the information from these
over 30 tables, i know my script is too say the least un-ortodox but i
do have a very strange representation of the data to start with.

Mar 3 '06 #7

P: n/a
ca********@gmail.com wrote:
I will explain,
I started with and excel file, when i imported it to sql server i ended
up with 30 odd tables, everyone of them had different column names
result of different headers in the excel file,
each of them, for some strange reason that i'm still to understand,
with a diferent number of columns but the data was present was allways
present on the first 5 columns
My intention is/was to transform this tables into a RDB, the first sept
i took was to build a sigle table with all the information from these
over 30 tables, i know my script is too say the least un-ortodox but i
do have a very strange representation of the data to start with.


Take care when importing data from Excel. The Excel OLEDB driver and
DTS can do some strange things when attempting to derive metadata from
spreasheets. A spreadsheet is not a table and it doesn't have any of
the nice properties we expect from tables such as keys, strict typing,
fixed precision, fixed number of columns, etc.

The best method is to create the target table(s) first. Then use DTS or
whatever to populate them from Excel. The bottom line is that Excel is
a lousy data interchange format.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Mar 3 '06 #8

P: n/a
Mr. Portas follows my experience.

When you sometimes have 15 columns, perhaps at some point you scrolled
over, typed something, adn deleted it. the fact that you "touched" a
cell means Excel will remember it, and "help" you by "saving it."
I personally believe that if you are resting on a cell, and autosave
kicks off, that can cause the cell to be marked as "used."
Excel has workbooks and sheets. In DTS you can specify which sheet you
want. It does get confusing, but like Mr. Portas said, DTS is not a bad
way to go.

Also, instead of "pushing" data from Excel to SQL every time, move the
data once, then link a brand new excel sheet/workbook to the SQL table
using "Import External Data". Now the data will reside in SQL, and you
can manipulate it in Excel.

Mar 3 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.