May 21, 2011 at 1:34 pm
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
May 21, 2011 at 2:02 pm
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?
May 21, 2011 at 2:48 pm
I am doing export only for practice..and learning purpose.thanks for the help..is there any query to import?
May 22, 2011 at 10:02 am
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/
May 22, 2011 at 5:45 pm
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.May 26, 2011 at 9:17 am
Are you trying to export it to a document ?
May 26, 2011 at 9:36 am
Right click in SSMS the database and select Export. Use the wizard. It builds simple SSIS packages.
May 26, 2011 at 11:44 am
thanks a lot for this it worke::-)
May 30, 2011 at 6:09 am
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
May 31, 2011 at 7:50 am
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
June 2, 2011 at 5:12 am
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..:)
June 2, 2011 at 5:21 am
for simple operation of export or import, you can to use Import/Export Tool in SSMS. for complex operation, use SSIS!
June 3, 2011 at 12:31 am
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
June 3, 2011 at 12:32 am
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)".
June 6, 2011 at 3:24 pm
Tiya (6/3/2011)
Sorry forgot to attach error message on sql 2005Msg 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/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply