Hi All,
I am used to other SQL engines, and have a few basic questions--
1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:
CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;
Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.
2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.
Thanks in advance,
Ben 6 4753
BJMurphy (mu********@gma il.com) writes:
I am used to other SQL engines, and have a few basic questions--
1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:
CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;
Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.
You can create a table from a query this way:
SELET x, y, z
INTO tablename
FROM sourcetable
There is nothing resemblent of CREATE OR REPLACE for anything in SQL
Server. You need to have things like:
IF object_id('some table') IS NOT NULL
DROP TABLE sometable
2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.
GO and ; are entirely unrelated. In T-SQL, GO is just another
identifier like MyTable, [Order Details] or whatever. GO is used by
many query tools as a batch separator, so that you can give the tool
something like:
CREATE something
go
-- Use this something
go
-- Drop this something
Each batch will be sent to SQL Server separately. A batch can consist
of many statements, separated or not by semicolons.
In application code you would typically never use GO, but each Execute
command is a batch.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Feb 13, 5:17 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
BJMurphy (murphy....@gma il.com) writes:
I am used to other SQL engines, and have a few basic questions--
1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:
CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;
Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.
You can create a table from a query this way:
SELET x, y, z
INTO tablename
FROM sourcetable
There is nothing resemblent of CREATE OR REPLACE for anything in SQL
Server. You need to have things like:
IF object_id('some table') IS NOT NULL
DROP TABLE sometable
2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.
GO and ; are entirely unrelated. In T-SQL, GO is just another
identifier like MyTable, [Order Details] or whatever. GO is used by
many query tools as a batch separator, so that you can give the tool
something like:
CREATE something
go
-- Use this something
go
-- Drop this something
Each batch will be sent to SQL Server separately. A batch can consist
of many statements, separated or not by semicolons.
In application code you would typically never use GO, but each Execute
command is a batch.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -
- Show quoted text -
Thanks, Erland, for your response.
To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?
Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?
I understood the semi-colon to be analogous to "GO" in that both would
submit the query to the SQL server, acting as a separator, but it
seems that you are saying this is not the case. I apologize for my
confusion, I just want to make sure I have everything straight in my
head.
I'm not the expert that Erland is, but I think I know.
GO and semicolon both serve to separate statements. But GO also
separates batches.
If you make 4 statements into one batch like this --
statement1; statement2; statement3; statement4 GO
....and statement4 fails with an error, the affected objects are
restored to the way they were before statement1.
If you make them 4 separate batches like this --
statement1 GO statement2 GO statement3 GO statement4 GO
.... and statement4 fails with an error, the objects are restored to
the way they were after statement 3.
Jim
On Feb 14, 4:54 pm, jim_geiss...@co untrywide.com wrote:
I'm not the expert that Erland is, but I think I know.
GO and semicolon both serve to separate statements. But GO also
separates batches.
If you make 4 statements into one batch like this --
statement1; statement2; statement3; statement4 GO
...and statement4 fails with an error, the affected objects are
restored to the way they were before statement1.
If you make them 4 separate batches like this --
statement1 GO statement2 GO statement3 GO statement4 GO
... and statement4 fails with an error, the objects are restored to
the way they were after statement 3.
Jim
OK. It seems as though the variables that I had setup were only valid
and were local to the specific batch in which they were created. If I
have ten statements, some of which are codependent, but I know they
will run successfully, it seems like I would want to use semi-colons
because it will allow me to keep the values of the local variables
throughout the entire batch, since there is only one batch. Would the
codependent queries still work?
For example:
create table foo;
select * from bar into foo;
select * from foo where (true);
Is then equivalent to:
create table foo
go
select * from bar into foo
go
select * from foo where (true);
Again, assuming the queries run succesfully.
Is there a way to create global variables?
Thanks for your response, Jim. I appreciate the help. Also, don't
worry, a book is on the way (the highly recommended one that comes in
two parts, I forget the title and author, sorry), so I won't be
posting too many questions, hopefullly :-)
BJMurphy (mu********@gma il.com) writes:
To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?
Not really. SQL Server never reaches any GO statement. GO is not a statement
at all. GO carries no particular meaning for SQL Server at all. GO is a
separator that is intercepted by interactive query tools. You would
never have any GO in a program.
Keep in mind that SQL Server is a client-server application. Query
Analyzer or SQL Server Management Studio are not SQL Server. They
are client tools. When you select a couple of statements in a
query window, they will send these to SQL Server. But if there are
GO on some lines, they will send the batches one by one. And one
batch will not be sent, until the previous has executed.
Once in SQL Server, SQL Server will execute the statements, sequentially.
(Unless there is some control-of-flow languages.)
The semi-colons are statement terminators, but in difference to
other semicolon languages, they are optional in SQL Server. (Except in
a few situations where they are needed to disambiguiate the grammar.)
Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?
There are statements that must be alone in a batck, like CREATE PROCEDURE
and CREATE VIEW. So to refer to you newly created view, you need to
do this in a separate batch. There are also some commands that does not
take effect until the next batch. Some SET commands are like that.
However, CREATE TABLE does not have any of these quirks. You can say:
CREATE #mytable (a int NOT NULL
INSERT #mytable(a) VALUES (213)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
BJMurphy (mu********@gma il.com) writes:
For example:
create table foo;
select * from bar into foo;
select * from foo where (true);
Is then equivalent to:
create table foo
go
select * from bar into foo
go
select * from foo where (true);
Again, assuming the queries run succesfully.
Well, since two of them has syntax errors, they will not. So
the first batch will fail. But if you run the second set of bathces,
the second statement will create the table "foo".
Is there a way to create global variables?
No. The place to write data that needs to be persisted over batches
is in tables, typically temp tables.
(OK, there is a command SET CONTEXT_INFO which could be used for
this, but it's not anywhere close to practical for this purpose.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: AK |
last post by:
I don't want any part of the previous discussion on Visual Basic
versus Visual Basic.Net.
My query is about using Visual Basic for Applications; and whether it
is better to use Visual Basic 6 or Visual Basic.Net as a springboard
for studying VBA.
I use Office 2000 and would like to use VBA as a tool to customize it.
I have zero programming experience.
I would like to read through and work on the examples of a beginners
|
by: Orange Free |
last post by:
I want to create a program that will ask a user a series of questions
and then generate a Microsoft Word document whose content is dictated
by the answers. I am not a professional programmer, and I understand
only a little about OO programming. Should I
a) stick to -- *gasp* -- Visual Basic to accomplish my goal;
b) use Python, with which I am somewhat familiar, and which I would
prefer to use; or
|
by: Michael Foord |
last post by:
#!/usr/bin/python -u
# 15-09-04
# v1.0.0
# auth_example.py
# A simple script manually demonstrating basic authentication.
# Copyright Michael Foord
# Free to use, modify and relicense.
# No warranty express or implied for the accuracy, fitness to purpose
|
by: Steven O. |
last post by:
First, this may not be the correct newsgroup. I have some relatively
basic questions on SQL. I tried to find a newsgroup that was
specifically just about SQL, and was surprised to find that all the
SQL-related newsgroups seem to be product related. But if I missed
something, and someone can steer me to a correct newsgroup, please do
so.
My specific questions:
1. I want to put comments in an SQL script. For example, I want
|
by: Ramesh |
last post by:
hi,
Let me ask some basic questions.
Can anybody explain me about the following questions:
1. When we have to create sn key? Whenever we compiled
Component we have to create or it is a one time process?
2. What information contained in sn key. I gone through
that it is having public key. How it is using this key to
intract with client.
3. When we have to run gacutil.exe file. Whenever we
| |
by: Jim H |
last post by:
If there is a site someone can point me to that answers such basic
questions, rather than taking up support's time posting answers, please let
me know.
I've developed C# .NET, unmanaged C++, and MFC applications. I have not
written any C++.NET apps yet and have some very basic questions.
Are managed C++ programs fully independent executables or are they still
processed at run time like C# and vb.net apps are? Is the finished product...
|
by: Fay Yocum |
last post by:
BEWARE beginner questions!!
I have some experience in Access but never as much as I want or need. I have
decided to get in on VB.Net. I would only
rate myself in Access as a Beginner/Intermediate VBA programmer. I have
decided to go at learning VB.Net in a
more organized manner. I have some questions where answers are not becoming
clear with VB.Net. So here goes.
1 All of the books I have accessed do basic one-two form examples...
|
by: software2006 |
last post by:
ASP And Visual Basic Interview questions and answers
I have listed over 100 ASP and Visual Basic interview questions and
answers
in my website
http://www.geocities.com/myinterviewquestions/ASPAndVisualBasic.htm
So please have a look and make use of it.
|
by: Goran Djuranovic |
last post by:
Hi all,
I am experiencing a strange thing happening with a "designer.vb" page. Controls I manually declare in this page are automatically deleted after I drop another control on a ".aspx" page.
- Why is this happening?
- Can I disable automatic declaration and have everything be declared manually?
- Any other options to fix this?
Thanks in advance.
Goran Djuranovic
|
by: LayneMitch via WebmasterKB.com |
last post by:
Hello.
This is a basic quiz. Only about 3 questions. I'm having issue with the
answer key which stays on a certain value regardless of acknowledging that
you have the correct answer for the first and moving on to the next answer.
Here's the code:
<html><head><title>Problem7</title>
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |