Import Export Wizard - 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

General SQL Server Forums

New to SQL Server Programming

Import Export Wizard

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-12-15 : 18:36:01

So I'm sure we all know how awesome the import/export wizard is. But its a little slow and I'm sick of doing the same things over and over again.

So. I was hoping you could let me know what the sql looks like that I could execute to do what the import export wizard is doing?

I'll walk you through what I do like 5 times a day.

1. Open Wizard
2. Data Scource: "Sql Server native Client 10.0"
3. Server name: "Main Server"
4. Authentication: Use Windows Authentication
5. Database: "MainDB"
Click Next
6. Destination "Sql Server native Client 10.0"
7. Server Name: "My Personal Server"
8. Authentication: Use Windows Authentication
9. Database: "MainDB"
Click Next
10. Copy Data from one or more tables or views
Click Next
11. Toggle Check Boxes by the tables I want
Press Finish
Press Finish

PREST0O!!!!!! I get what I need.

What does the SQL commands executing this look like ?~!??~?~

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-15 : 18:58:58

I use bcp.exe. I export to a csv file and then import via it.

Here's an example:

bcp.exe Db1.dbo.Tbl1 out E:\SomeFile.csv -t, -T -SServer1\Instance1 -c -r\r\n
bcp.exe Db1.dbo.Tbl1 in E:\SomeFile.csv -t, -T -SServer2\A -c -r\r\n

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-15 : 18:59:35

Or use a linked server:

INSERT INTO LinkedServerName.Db1.dbo.Tbl1 (...)
SELECT ...
FROM Db1.dbo.Tbl1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ConradK
Posting Yak Master

140 Posts

Posted - 2010-12-15 : 19:12:01

quote:
Originally posted by tkizer

Or use a linked server:

INSERT INTO LinkedServerName.Db1.dbo.Tbl1 (...)
SELECT ...
FROM Db1.dbo.Tbl1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Tell me more about this. Can you write exactly what the query would look like given the sample info I gave you?

Because I've taken these kinds of basics, and always get werid permission denials. But the thing is, if I can do it with the wizard, obviously I have the permissions needed to do this....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-15 : 19:22:38

These aren't queries. These are commands that you'd run at the command prompt. Only select/insert will be required to run bcp.exe for export/import.

I tried to make it clear from my commands what parts you'll have to change. Run bcp.exe /? from a cmd window to see the options.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ConradK
Posting Yak Master

140 Posts

Posted - 2010-12-15 : 19:53:58

hmmmm.... I want to link servers, but can't seem to make it work then I guess....

I'm not interested in importing and exporting csvs.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-16 : 00:27:14

Then you just need to create a linked server.

INSERT myTable
SELECT <fieldList>
FROM otherServer.DatabaseName.dbo.TableName;

If you have issues creating a linked server, let's hear about that. We'll help ya along.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-16 : 00:28:27

By the way, almost everything you can do from the GUI can be captured by profiler so you can see the T-SQL commands for yourself.

This, unfortunately, is not one of those cases, but do keep it in mind for other exercises.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-12-16 : 11:31:31

Well I don't even know where to begin. In my company I have a server on my local machine, and then we have the 'company' server. I'm not sure how to connect or link them, what command to use or ANYTHING. I don't even know where to start.

I imagine http://msdn.microsoft.com/en-us/library/ms190479.aspx is telling me what to do, and I've used this to connect xlses, looks like this:

EXEC sp_addlinkedserver 'awesome',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:\SKUM\opossum.xls',
NULL,
'Excel 8.0'
GO

but I dunno how to use this to connect the server in the other room on my network.... For some reason its just not making sense to my skull.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources