how to quickly learn the syntax of SQL?

Hi, All:

I know oracle SQL, now I need to do a lot of SQL query on Microsoft SQLSERVER, can any one point out any place that I can find out the syntax of SQLserver SQL statement? Since this is just a short term assignment, so I don't want to buy a book, just hoping I can learn something quickly from online. I don't need learn anything deep, just need to know some simple syntax so I can do join, count, concatenate, min(), max(), sum () etc.
thanks in advance.Books Online, the definitive source for information about SQL Server and T-SQL, is available online here, to for free download here.|||

You will be suprised that for the standard things like joining and concatenating, SQL Server support most things the ANSI standard which lets you change your SQL strings easily. For special things like system functions you will need the appropiate TSQL equivalent but for ANSI joining like *= you can do a "soft-migration" to SQL Server. (And in addition you won′t need the dual table anymore :-) ) :-D

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||
Hi, Thanks for all of your replies.

How to quickly evaluate SSIS, programmer perspective

SSIS looks interesting. I haven't loaded any Yukon or read the 2005 BOL, so I'm coming in with a clean slate.

In the past I have found the graphical programming interface for DTS, limiting, annoying and unnatural. I much prefer coding in an OO language the conventional way.

I'm interested in evaluating SSIS as quickly as I can. The ideal is programming with a standard .NET language. I am happy if a design surface gives me a start but I expect to go into the code it writes and continue with a standard code editor.

What is the best reference to get a idea whether this is sensible to do and what the programming experience is like?

Thanks.That still isn't the experience. While SSIS has made great strides over DTS in terms of functionality, i.e. the ability to easily do looping constructs, etc it is still not like writing a Windows Forms application where you write code to perform various operations. The script transform is the exception to this but I have found myself using it very rarely.|||Thanks for that Chris.

I'm aware of a way of programmatically creating a DTS task. (I never used it, for real work, but I've read interesting write ups. If I remember correctly it involved exporting the package as a VBScript, that created the task by instantiating and using the underlying DTS object model. These VBScript files could then be used as the basis for creating a VB or VB.NET program, though this was probably not done often.)

I assumed that SSIS would, at least, retain that functionality, but with a .NET language in place of the VBScript. (Though I was hoping for something a lot better.)

(I really like the idea of a live diagram showing the code, but I still want to be able to access that code directly.)|||I can't seem to find a way to export a package like you used to be able to do in DTS. Doesn't mean that it isn't possible. Just isn't obvious to someone who has spent around 150 hours in the tool.|||That feature is not supported on SSIS.
K|||Thanks Kirk.

I don't understand that design decision. It cuts the traditional style of programming out of the loop. I was really looking forward to improved access to the ETL engines. (I guess that means carry on with other approaches to ETL in the CLR 2.0 era!)|||You still have a choice how how you build packages. Designer Vs Code.

You can still create/load and execute packages through code. What you cannot do is create a package and "script" it, in effect Save As VB as we had in DTS. It's a shame, since it was a great way to get a head start on creating a package in code, but at the end of the day not a major problem really.

I don't understand the comparison of SSIS with traditional style programming, since at the end of the day this is a ETL tool. You can access the objects through code if you want, but there will be limitations using the object model since it is designed to do a specific job, it is not a programming language.

On the programming theme, the ability to develop your own components for SSIS is so much easier, and more open, compared to DTS. You can write connection managers, tasks, pipeline components (a bit like old transforms), log providers and enumerators.|||Thanks Darren.

That revives my interest.

Recently I have given up using DTS in favour of coding the whole process. So, in my mind, I see a continuum between code and ETL tool. I think the design decision is driven by the idea of who the target user is. It's assumed the user is "Somebody who is not going to write conventional code". (In that case those who are comfortable writing code, kinda get driven out.) (The decision might also be based on one guy to do DTS, one guy to do code, one guy to test... which in my view is a recipe to turn a 2 man-hour job into a 9 man-day job!!)

As you say the loss of the ability to generate code from a wizard made package is unfortunate. The improved ability to write your own transforms (etc.) looks like it could have fixed an Achilles Heel of DTS. (It was an horrendous job to write script to perform real world serious transforms.)|||

MikeGale wrote:

Thanks Darren.

That revives my interest.

Recently I have given up using DTS in favour of coding the whole process. So, in my mind, I see a continuum between code and ETL tool. I think the design decision is driven by the idea of who the target user is. It's assumed the user is "Somebody who is not going to write conventional code". (In that case those who are comfortable writing code, kinda get driven out.) (The decision might also be based on one guy to do DTS, one guy to do code, one guy to test... which in my view is a recipe to turn a 2 man-hour job into a 9 man-day job!!)

As you say the loss of the ability to generate code from a wizard made package is unfortunate. The improved ability to write your own transforms (etc.) looks like it could have fixed an Achilles Heel of DTS. (It was an horrendous job to write script to perform real world serious transforms.)

Mike,
Don't discount the ability to write custom transformation functionality using the script component and the script task - they are fantastically powerful bits of kit and execute managed code rather than script code so its many times more efficient than ActiveX in DTS.

Donald Farmer has book coming out dedicated solely to scripting in SSIS and exactly what can be achieved. http://www.amazon.co.uk/exec/obidos/ASIN/1932577211/202-4310542-9360621?%5Fencoding=UTF8

-Jamie

How to quickly create and populate a table to 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is my
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.
Try this.
while (select count(*) from TableD)<5000
begin
insert into tableD
values (replicate('h', 500000000))
continue
end
TableD is 40,768KB
Divide 1,000,000 by 40,768 = 24
Now, DTS table out to txt file
declare @.counter int
select @.counter=0
while @.counter<24
begin
bulk insert TableD from 'C:\tableD.txt'
select @.counter=@.counter+1
continue
end
Mike K
"MittyKom" wrote:

> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>
|||How about something like the following. I was able to generate it in about
90 seconds on a slow workstation.
-- Set database to simple recovery mode
Create table #Numbers (
Number int)
declare @.x int
set @.x = 1
while @.x < 127000
Begin
insert #Numbers values (@.x)
set @.x = @.x + 1
End
select cast(' ' as char(4100)) as ColX
into TableD
from #Numbers
drop table #Numbers
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:78E92EEE-3245-4DB6-8870-8AD5B00A7CE3@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>

How to quickly create and populate a table to 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is my
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.Try this.
while (select count(*) from TableD)<5000
begin
insert into tableD
values (replicate('h', 500000000))
continue
end
TableD is 40,768KB
Divide 1,000,000 by 40,768 = 24
Now, DTS table out to txt file
declare @.counter int
select @.counter=0
while @.counter<24
begin
bulk insert TableD from 'C:\tableD.txt'
select @.counter=@.counter+1
continue
end
Mike K
"MittyKom" wrote:
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>|||How about something like the following. I was able to generate it in about
90 seconds on a slow workstation.
-- Set database to simple recovery mode
Create table #Numbers (
Number int)
declare @.x int
set @.x = 1
while @.x < 127000
Begin
insert #Numbers values (@.x)
set @.x = @.x + 1
End
select cast(' ' as char(4100)) as ColX
into TableD
from #Numbers
drop table #Numbers
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:78E92EEE-3245-4DB6-8870-8AD5B00A7CE3@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>

How to quickly create and populate a table to 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is m
y
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.Try this.
while (select count(*) from TableD)<5000
begin
insert into tableD
values (replicate('h', 500000000))
continue
end
TableD is 40,768KB
Divide 1,000,000 by 40,768 = 24
Now, DTS table out to txt file
declare @.counter int
select @.counter=0
while @.counter<24
begin
bulk insert TableD from 'C:\tableD.txt'
select @.counter=@.counter+1
continue
end
Mike K
"MittyKom" wrote:

> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>|||How about something like the following. I was able to generate it in about
90 seconds on a slow workstation.
-- Set database to simple recovery mode
Create table #Numbers (
Number int)
declare @.x int
set @.x = 1
while @.x < 127000
Begin
insert #Numbers values (@.x)
set @.x = @.x + 1
End
select cast(' ' as char(4100)) as ColX
into TableD
from #Numbers
drop table #Numbers
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:78E92EEE-3245-4DB6-8870-8AD5B00A7CE3@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>

How to quickly create and populate a table 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is m
y
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.Do you have the SQL Server 2000 Resource Kit? If so, try DBGen.
See:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part11/c3961.mspx[/ur
l]
HTH
Jerry
If not you could use a WHILE loop or CROSS JOIN to produce your data or some
third-party tool/script.
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:AA5D17A7-D8C9-4781-8882-782ED5EF6507@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.|||Why dont you load an image into the SQL Server table? You can have really bi
g
images and you only need to insert 2 or 3 rows based on the size of the
image.
-Mark
"MittyKom" wrote:

> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.|||Thanx Mark and Jerry.
I have a jpeg image saved on my disk. How can i insert it into a table?
"Mark" wrote:
> Why dont you load an image into the SQL Server table? You can have really
big
> images and you only need to insert 2 or 3 rows based on the size of the
> image.
> -Mark
> "MittyKom" wrote:
>|||Give a little more information about what type of testing you are wanting to
do, and perhaps you will get better advice about how to populate the table.
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:AA5D17A7-D8C9-4781-8882-782ED5EF6507@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.