I am trying to copy data from one table to another, and in the process
add a sequence number to copied rows. I have looked at the IDENTITY
function when creating tables, and essentially this is the behavior I
am trying to duplicate.
However, my question is if I can do this with the SELECT INTO
statement as well. I have tried a few variations and it does not seem
to work:
SELECT seq_number = identity(10),
prod_number,
prod_name
INTO my_products
FROM all_products
WHERE .... 7 2663
On 26 May 2004 13:41:48 -0700, php newbie wrote: I am trying to copy data from one table to another, and in the process add a sequence number to copied rows. I have looked at the IDENTITY function when creating tables, and essentially this is the behavior I am trying to duplicate.
However, my question is if I can do this with the SELECT INTO statement as well. I have tried a few variations and it does not seem to work:
SELECT seq_number = identity(10), prod_number, prod_name INTO my_products FROM all_products WHERE ....
Hi php,
You should add the identity property to the definition of the column in
the CREATE (or ALTER) TABLE. Then use an INSERT .. SELECT statement and
leave the identity column out of the column list.
CREATE TABLE my_products (
seq_number int NOT NULL IDENTITY,
prod_number int NOT NULL UNIQUE, -- ????
prod_name varchar(25) NOT NULL, -- ????
PRIMARY KEY (seq_number)
)
INSERT my_products (prod_number, prod_name)
SELECT prod_number, prod_name
FROM all_products
WHERE ....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
On 26 May 2004 13:41:48 -0700, php newbie wrote: I am trying to copy data from one table to another, and in the process add a sequence number to copied rows. I have looked at the IDENTITY function when creating tables, and essentially this is the behavior I am trying to duplicate.
However, my question is if I can do this with the SELECT INTO statement as well. I have tried a few variations and it does not seem to work:
SELECT seq_number = identity(10), prod_number, prod_name INTO my_products FROM all_products WHERE ....
That syntax would work with Sybase. However, for MS SQL Server, the syntax
to use is slightly different:
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable
WHERE ...
php newbie (ne**********@y ahoo.com) writes: SELECT seq_number = identity(10), prod_number, prod_name INTO my_products FROM all_products WHERE ....
As Ross posted, the syntax is slightly different.
I like to point out that if you intend to use an ORDER BY to that you
hope to determine the values of seq_number, don't use SELECT INTO,
but CREATE TABLE + INSERT. In neither of the cases are you guaranteed
to get the order you ask for, but your odds are better with INSERT,
particularly if you add a OPTION (MAXDOP 1) at the end of the SELECT
statement to turn of parallelism.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <so****@algonet .se> wrote in message I like to point out that if you intend to use an ORDER BY to that you hope to determine the values of seq_number, don't use SELECT INTO, but CREATE TABLE + INSERT. In neither of the cases are you guaranteed to get the order you ask for, but your odds are better with INSERT, particularly if you add a OPTION (MAXDOP 1) at the end of the SELECT statement to turn of parallelism.
Thanks to Erland, Hugo, Ross, and all who replied with answers; much
appreciated.
Erland, my statement does contain an ORDER BY clause. So you got my
interest with your comment. Could you please elaborate a bit more?
Do you mean that the new identity column could have duplicates?
and/or when the MAXDOP > 1? Or only perhaps when it contains an ORDER
BY clause?
What I wanted to achieve was a unique, increasing number for each row
of the new table as it would be when ordered. They don't even need to
be sequential, they just need to be in the same sort order as the
ORDER-BY clause.
php newbie (ne**********@y ahoo.com) writes: Erland, my statement does contain an ORDER BY clause. So you got my interest with your comment. Could you please elaborate a bit more? Do you mean that the new identity column could have duplicates?
No.
What I wanted to achieve was a unique, increasing number for each row of the new table as it would be when ordered. They don't even need to be sequential, they just need to be in the same sort order as the ORDER-BY clause.
And that last things is the issue. You are not guaranteed that. All rows
will get a unique value, but the order may not agree with your ORDER BY
clause.
This is more likely to happen with SELECT INTO than INSERT, and with
INSERT you can improve your odds by turning off parallelism. But whichever
you to, you are relying on chance to some extent. I say to some extent,
because in many cases you do get the expected result, particularly if the
number of rows is moderate.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <so****@algonet .se> wrote in message And that last things is the issue. You are not guaranteed that. All rows will get a unique value, but the order may not agree with your ORDER BY clause.
This is more likely to happen with SELECT INTO than INSERT, and with INSERT you can improve your odds by turning off parallelism. But whichever you to, you are relying on chance to some extent. I say to some extent, because in many cases you do get the expected result, particularly if the number of rows is moderate.
I see. After your post, I changed my query. I am now using the
following version:
SELECT IDENTITY(INT,1, 1) as seq_number, prod_number, prod_name
INTO my_products
FROM ( SELECT prod_number, prod_name
FROM all_products
WHERE ...
ORDER BY ...) sub_table
I trust that the identity values would now agree to the ORDER BY
clause, regardless of the number of data rows. Please let me know if
not.
Thanks!
php newbie (ne**********@y ahoo.com) writes: I see. After your post, I changed my query. I am now using the following version:
SELECT IDENTITY(INT,1, 1) as seq_number, prod_number, prod_name INTO my_products FROM ( SELECT prod_number, prod_name FROM all_products WHERE ... ORDER BY ...) sub_table
I trust that the identity values would now agree to the ORDER BY clause, regardless of the number of data rows. Please let me know if not.
There is even less guarantee in this case. If you ran this query
without the IDENTITY function and the INTO, you are not guaranteed
to get back the rows in order. And nor should you be, because your
outer SELECT does not have an ORDER BY, which means "give me the
rows in any order you want".
If you want achieve as high certainty as possible you should do:
CREATE TABLE #temp(...)
INSERT #temp (...)
SELECT col1, col2, ...
FROM ...
OPTION (MAXDOP 1)
If you want to be dead sure you should do:
SELECT rowno = (SELECT count(*)
FROM tbl b
WHERE b.keycol >= a.keycol), col1, ...
FROM tbl a
...
This gets messy if you have a multi-column key. And performance may
not be bright.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: JT |
last post by:
I have an input form for which I've created a "matrix" for user input.
Basically, the user chooses a radio button and then through javascript, a
select box is displayed to define a value for that radio option, like so:
Choice: (Radio1) type: (select box1)
Choice: (Radio1) type: (select box2)
Choice: (Radio1) type: (select box3)
Choice: (Radio1) type: (select box4)
Choice: (Radio1) type: (select box5)
|
by: John Fabiani |
last post by:
Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use
>>> string1='18 Tadlock Place'
>>> cursor.execute("SELECT * FROM mytest where address = %s",string1)
All works as expected. But
>>> numb=10
>>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb)
Traceback (innermost last):
File "<stdin>", line 1, in ?
|
by: Memduh Durmaz |
last post by:
Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.
Is there any basic structure on Sql Server like WITH ... SELECT
structure?
|
by: point |
last post by:
Hello there...
I'm a PHP programmer and starting to learn JS...
I have a following problem....
I have 3 select boxes! one is hotel one is destination and one is
country...
if someone clicks selects the country then the destination select box shows
the destinations in that country and further if he chooses destination all
|
by: Carl Wu |
last post by:
Hi all,
I am newcomer in HTML, Javascript,
I want to create two select controls S1, S2.
There are 3 options: ALL, A, B in S1;
When select A in S1, It let you select A1, A2 in S2,
| |
by: CJM |
last post by:
I'm building a search function for one of my applications. The user has the
option to enter a number criteria of criteria, but none are compulsary. I
need to be able to build up a query string that includes only the right
criteria. The simplest way I have found is something like this:
sSQL = "Select field1, field2, etc form table where 1=1"
If Request.Form("Criteria1") <> "" then
sSQL = sSQL & " and criteria1 = " &...
|
by: serge |
last post by:
Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?
I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I create a single View
and specify also in this View the WHERE clause
that is common in these stored procedures, I
will have the new stored procecures changed to
be like:
|
by: serena.delossantos |
last post by:
Trying to insert into a history table. Some columns will come from
parameters sent to the store procedure. Other columns will be filled
with a separate select statement. I've tried storing the select return
in a cursor, tried setting the values for each field with a separate
select. Think I've just got the syntax wrong. Here's one of my
attempts:
use ESBAOffsets
go
|
by: Apaxe |
last post by:
In the database i have a table with this information:
key_id =1
key_desc =43+34+22+12
I want sum the values in key_desc. Something like:
SELECT key_desc FROM table
But the result of the select was "111" and not "43+34+22+12".
|
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: 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: 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.
| |