What's the syntax for combining INTO and UNION clauses?
What I want to do is:
SELECT blah
INTO newtable
FROM oldtable1
WHERE <conditions>
UNION
SELECT blah
INTO newtable
FROM oldtable2
WHERE <conditions>
DTS doesn't seem to like my syntax. Where should the INTO(s) really go?
Thanks
Andy 5 33915
Try with something like this:
select <column list>
into newtable
from
(
select <column list>
from table1
union
select <column list>
from table2
) un
MC
"Andy Kent" <an************ ******@virgin.n et> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. . What's the syntax for combining INTO and UNION clauses?
What I want to do is:
SELECT blah INTO newtable FROM oldtable1 WHERE <conditions> UNION SELECT blah INTO newtable FROM oldtable2 WHERE <conditions>
DTS doesn't seem to like my syntax. Where should the INTO(s) really go?
Thanks
Andy
Thanks, that worked...
Next part of problem: how would I get it to create and populate an
IDENTITY column?
Bearing in mind I'm working in DTS, which doesn't like anything too
clever ...
well, you can add identity column after you insert data. So, after insert
issue:
go
alter table table1
add Something_ID int identity(1,1)
LMK if you need anything else
MC
"Andy Kent" <an************ ******@virgin.n et> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. . Thanks, that worked...
Next part of problem: how would I get it to create and populate an IDENTITY column?
Bearing in mind I'm working in DTS, which doesn't like anything too clever ...
A poor carpenter blames his tools.
Not trying to pick on you, but DTS is very powerful; there are some
things that it's not good at, but chewing up CORRECT SQL syntax is not
one of them.
If you want to add an identity column in one step, you can use a
subquery for the UNION statement:
SELECT SELECT IDENTITY(int,1, 1) as splat, blah
INTO newTable
FROM (SELECT blah
FROM oldtable
UNION --do you need DISTINCT values? UNION ALL will be faster
SELECT blah
FROM oldTable2) x
Stu
You were quite close... Here's an example:
Use Northwind
GO
SELECT OrderID
INTO #t
FROM Orders
UNION
SELECT ProductID
FROM "Order Details"
HTH,
Gert-Jan
Andy Kent wrote: What's the syntax for combining INTO and UNION clauses?
What I want to do is:
SELECT blah INTO newtable FROM oldtable1 WHERE <conditions> UNION SELECT blah INTO newtable FROM oldtable2 WHERE <conditions>
DTS doesn't seem to like my syntax. Where should the INTO(s) really go?
Thanks
Andy This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alan |
last post by:
I'm just about to start a project that needs to combine the results of a SQL
Server query with the results of an Index Server query. The basic idea is
that the user enters/selects a bunch of search criteria on a form. Most of
the criteria selected by the user will be used to select records from the
database - standard WHERE clause stuff - but the user can also enter
free-text that should be searched for in associated uploaded documents. The...
|
by: Phillip Rhodes |
last post by:
Hi all, I have a question I hope someone can help me with:
I'm doing some communication over a socket, and have need to send
and receive values of type double. I'm doing everything in terms
of single byte reads and writes to the socket, so what I need to do
is take the individual bytes I receive, that represent a double, and
actually turn them into a double. This I'm struggling to figure
out.
I can easily combine the bytes into a...
|
by: PATGMorris |
last post by:
I've got a Union Query that pulls data from two different tables for
chemistry and micro testing. The tables containing very similar data but
for reasons not necessary here, cannot be put into one table (differing
types of results mainly)
However, both have date entry fields for varying time stations from an
initial, 2 weeks, 1 month, 2 months through to 24 months. I've written a
Union Query that pulls the dates into a sort of ...
|
by: justin tyme |
last post by:
Hello Experts!
I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query....
|
by: m.k.ball |
last post by:
Hi - I have some tables designed to keep track of order. The main table
contains a session id, address and date. Linked to this I have a table
of items that make up each order. I'm concerned that over the course of
a few weeks performance will slow down as the tables fill up, so I
though I would make duplicates of each table and have any orders over a
month old moved (along with the corresponding items) to the duplicate
tables. I would...
| |
by: Frank |
last post by:
Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!
Basically there are tables that track history and each table tends to
track only a specific value housed within a date range. I'm trying to
combine the tables...
|
by: spatik |
last post by:
I have a scenario like this in my application.
We can have values documented for each time column.
I have discrete timepoints in hand and I need to find the
documented values just before and just after the time columns.
Consider a scenario where I have 8 time points like
10:00,10:05,10:10,10:15,10:20,10:25,10:30 and 10:35.
I run a query for that like to find the documented value just before the 8 time points.
I also run the same big...
|
by: billelev |
last post by:
I am trying to combine two queries into one UNION query. The first sub-query works, but the second creates the following error at the end of the first iif statement: "Syntax error in union query"
Does anybody know why?
Select Symbol, MarketValue AS , Type, RiskType FROM
. AS tmpVarPositions
UNION
SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
|
by: Don Barton |
last post by:
I have 2 tables, Table 1 has Name, NameID, and A, B, C fields.
Table 2 has Name, NameID, and D, E fields.
Several of the Names/NameID are the same in both databases. I want my
merged the tables so each record looks like; Name, NameID, A, B, C, D,
E. I have merged them successfully but I am getting two records for
each name, one record for A, B, C fields, and another record for the
D, E fields.
Example,
Name NameID A ...
|
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: 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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |