how to export data in sql server?

Question

Post reply

how to export data in sql server?

Tiya

Ten Centuries

Points: 1146
More actions
May 21, 2011 at 1:34 pm

#238554

Hi ,
I want to export this data in sql server and table should be named as "student"...how can i do this?
and if we can export this will it take datatype by default?
for example id is int
name is varchar
id is primary key
etc? or we have to change that after export/import?
idnameroll no.status
1abc5A
2dhd27D
3yrt15A
4jgh8D
5fnf7A
6fmd,1D
7deke9D

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

There are numerous tools available to import data such as SSMS, BULK INSERT, OPENROWSET, linked server, SSIS, BCP.

Each one has its own strngth and weaknesses.

Question aside: is this a real-world scenario or is it some kind of homework?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tiya Ten Centuries Points: 1146 More actions · Answer 2

I am doing export only for practice..and learning purpose.thanks for the help..is there any query to import?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Tiya (5/21/2011)
I am doing export only for practice..and learning purpose.thanks for the help..is there any query to import?

One of the options mentioned was SSIS.

You could launch BIDS and create a simple SSIS Project.

Use the Wizard to select your source and destination.

Map the Columns and execute the package.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Tiya (5/21/2011)
I am doing export only for practice..and learning purpose.thanks for the help..is there any query to import?

Well... it all depends on what are you planing to do with that data.

May be you are planing to load the data in a different RDBMS - most likely "export" method will be dependent on the purpose of the data "export".

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Guras SSCertifiable Points: 6518 More actions · Answer 5

Guras

SSCertifiable

Points: 6518

May 26, 2011 at 9:17 am

#1330425

Are you trying to export it to a document ?

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 6

Right click in SSMS the database and select Export. Use the wizard. It builds simple SSIS packages.

Tiya Ten Centuries Points: 1146 More actions · Answer 7

Tiya

Ten Centuries

Points: 1146

May 26, 2011 at 11:44 am

#1330545

thanks a lot for this it worke::-)

prashavhad SSC Enthusiast Points: 165 More actions · Answer 8

You can use Export SSIS tool from Sql-2005 or DTS tool from Sql-2005.

It will surely work to out your table into same data type

Thanks & Regards,

Prashant Avhad

vikingDBA SSCertifiable Points: 5356 More actions · Answer 9

Another way is using the SQLCMD command line utility.

Example:

sqlcmd -SBOBSRV4 -E -Q"Select * from Purchasing.Vendor" -dAdventureWorks -oC:\sqlcmdoutput.txt

-S is the server (remember to use the serveramedinstance if needed)

-E is windows authentication

-Q is the query statement

-d is the database

-o is the output file (in comma separated value format)

Creates comma separated file. Note that SQLCMD also puts the column headings in the output file.

You can trick it and do CONVERT() statements in the SELECT statement to make the output specific formats.

Example:

sqlcmd -SBOBSRV4 -E -Q"Select CONVERT(varchar(200),VendorName) AS Name, CONVERT(int,VendorZip) as Zip,

CONVERT(varchar(30),StartDate,101) as StartDate from Purchasing.Vendor" -dAdventureWorks -oC:\sqlcmdoutput.txt

Great thing about this is you can put the command in a batch (.bat) file and schedule it to run in Task Scheduler in Windows.

Hope this

helps.

Bob L

Runal SSC Enthusiast Points: 134 More actions · Answer 10

Hi Tiya,

I have tried doing this long back..

For example..

I take the data in Excel i.e each column having the data as Id, Name, Roll no, Status.

Then open the SQL Server Enterprise manager & use the Import export options

where in you have to specify the source location ..that will be ur path where u have stored the excel

Then destination ....consider a default one..

Then after the entire process u will have the data in the default table..

Go to the design view & change the Datatypes as u want according to the data & then after which change the name of the table..

CAn try this..:)

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 11

for simple operation of export or import, you can to use Import/Export Tool in SSMS. for complex operation, use SSIS!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Tiya Ten Centuries Points: 1146 More actions · Answer 12

I started first bulk insert and its not working:

CREATE TABLE PRODUCT(

MAKER VARCHAR(10) NOT NULL,

MODEL VARCHAR(50) NOT NULL PRIMARY KEY,

TYPE VARCHAR(50))

________________________

BULK

INSERT product

FROM 'c:\product.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

__________________

I have uploaded sheet also and format i kept is like this:

A,1232,PC

A,1233,PC

A,1276,Printer

A,1298,Laptop

A,1401,Printer

A,1408,Printer

A,1752,Laptop

B,1121,PC

B,1750,Laptop

C,1321,Laptop

D,1288,Printer

D,1433,Printer

E,1260,PC

E,1434,Printer

E,2112,PC

E,2113,PC

Tiya Ten Centuries Points: 1146 More actions · Answer 13

Sorry forgot to attach error message on sql 2005

Msg 4832, Level 16, State 1, Line 2

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 2

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Tiya (6/3/2011)
Sorry forgot to attach error message on sql 2005
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2

You have some dead space at the end of the last row.

I added column names to make the mapping easier and I deleted the dead space at the end of the last row and I loaded the product table without any errors or warnings.

SELECT * FROM PRODUCT

MAKER MODEL TYPE

-------------------------------------------------- -------------------------------------------------- --------------------------------------------------

A 1232 PC

A 1233 PC

A 1276 Printer

A 1298 Laptop

A 1401 Printer

A 1408 Printer

A 1752 Laptop

B 1121 PC

B 1750 Laptop

C 1321 Laptop

D 1288 Printer

D 1433 Printer

E 1260 PC

E 1434 Printer

E 2112 PC

E 2113 PC

(16 row(s) affected)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/