Options for ETL:  Transact-SQL

enrique_aeo
enrique_aeo used Ask the Experts™
on
Hi experts,

I am reading about: Options for ETL, one is Transact-SQL
Can you give me an example about Transact-SQL?

This is the complete list:
Microsoft SQL Server Integration Services
The Import and Export Data Wizard
Transact-SQL
The bcp utility
Replication
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Transact-SQL is literally just an extension of the SQL database language: http://en.wikipedia.org/wiki/Transact-SQL

Microsoft has different SQL syntax than say MySQL or NoSQL for example depending on the functions. The basics are the same (SELECT, WHERE, etc) but specialized functions exist.

Think of it as the difference between using PHP v4 or PHP v5, or .NET 3.5 vs .NET 4. Basically same syntax with different functions available.

Author

Commented:
My question is not very clear.

My question is: how can I do ETL operations using Transact SQL.
It is as with: The Import and Export Data Wizard
but do not understand how I can do it with Transact SQL
So doing it with t-sql just means doing it with raw sql commands. If you are doing it with the SSIS, wizard, or bcp the t-sql will be behind the scenes and won't be seen by you (in sql you can view the raw sql from the wizard the end though).

SSIS is best for importing data from data files into sql
bcp is a backup utility command
etc

t-sql is just the core functionality way to do it, it is what the other interfaces actually generate for you (SSIS, bcp, etc) and would be what is used if doing the data import export from your an external application if you were writing one, as the other methods are what is built into SQL server.

So that said, I guess the question is still unclear. Are you trying to do something specifically?
As reference: http://msdn.microsoft.com/en-us/library/ms162802.aspx

" Except when used with the queryout option, the utility requires no knowledge of Transact-SQL."

The other methods are just interfaces to setup imports/exports so you don't have to know or write all the t-sql to do it. The interface generates t-sql for you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial