regarding select into query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

regarding select into query

Author

Topic

idthiru
Starting Member

6 Posts

Posted - 2006-09-15 : 08:59:02

Hi all

please tell me whether i shall write a query like this.

SELECT * INTO [tblTemp] ([rowId] int IDENTITY(1,1),
[Account Number] varchar(50),
[Mobile Number] varchar(50) NULL,
[Name] varchar(100)) NULL'
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=file.xls;Extended properties=Excel 8.0')...[Sheet1$]

shal I define the structure of the table in "select into" query? is it possible? please tell me.

I thank all in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 09:03:56

CREATE TABLE [tblTemp]
(
[rowId] int IDENTITY(1,1),
[Account Number] varchar(50),
[Mobile Number] varchar(50),
[Name] varchar(100)
)

INSERT [tblTemp]
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=file.xls;Extended properties=Excel 8.0')...[Sheet1$]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-15 : 09:13:17

You can use

Select * into Tbl from SomeTable

to create and populate table from some other data..but you CAN'T explicitly define data type of the columns...they are deduced from the data which you are populating. Also it is not recommended due to locking overhead it will place on the system tables.

Harsh Athalye
India.
"Nothing is Impossible"

idthiru
Starting Member

6 Posts

Posted - 2006-09-16 : 03:09:38

quote:
Originally posted by harsh_athalye

You can use

Select * into Tbl from SomeTable

to create and populate table from some other data..but you CAN'T explicitly define data type of the columns...they are deduced from the data which you are populating. Also it is not recommended due to locking overhead it will place on the system tables.

Harsh Athalye
India.
"Nothing is Impossible"

thanks for your help and also happy because
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-09-16 : 04:38:52

"but you CAN'T explicitly define data type of the columns"

hehehe ... I reckon you can if you CAST them in the SELECT !!

I've done things like:

SELECT [MyColumn] = CONVERT(varchar(100), NULL),
*
INTO MyNewTable
FROM MyOldTable

in the past.

However, much MUCH better to pre-create the table for all the good reasons mentioned above - plus stick a Primary Key definition in the CREATE TABLE statement whilst you are at it - e.g.


CREATE TABLE [tblTemp]
(
    [rowId] int IDENTITY(1,1) NOT NULL,
    [Account Number] varchar(50),
    [Mobile Number] varchar(50),
    [Name] varchar(100),
    PRIMARY KEY
    (
        [rowId]
    )
)

Edit: The PK doesn't have to be on the rowId column, but whichever colmn it is will need to be NOT NULL!

Kristen

idthiru
Starting Member

6 Posts

Posted - 2006-09-16 : 05:02:13

Hello,

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.

My most recent problem is:
The understanding of field values by sql server and excel.

1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),

the table fields show exponential values(may be float
datatype).

Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).

Kristen
Test

22859 Posts

Posted - 2006-09-16 : 05:53:39

This is the same question that you posted: http://sqlteam.com/forums/topic.asp?TOPIC_ID=72095

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources