select into withidentity

[php newbie]

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 ....

[Hugo Kornelis]

Re: select into withidentity

On 26 May 2004 13:41:48 -0700, php newbie wrote:
[color=blue]
>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 ....[/color]

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)

[Ross Presser]

Re: select into withidentity

On 26 May 2004 13:41:48 -0700, php newbie wrote:
[color=blue]
> 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 ....[/color]

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 ...

[Erland Sommarskog]

Re: select into withidentity

php newbie (newtophp2000@y ahoo.com) writes:[color=blue]
> SELECT seq_number = identity(10),
> prod_number,
> prod_name
> INTO my_products
> FROM all_products
> WHERE ....[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[php newbie]

Re: select into withidentity

Erland Sommarskog <sommar@algonet .se> wrote in message[color=blue]
> 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.[/color]

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.

[Erland Sommarskog]

Re: select into withidentity

php newbie (newtophp2000@y ahoo.com) writes:[color=blue]
> 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?[/color]

No.
[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[php newbie]

Re: select into withidentity

Erland Sommarskog <sommar@algonet .se> wrote in message[color=blue]
> 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.[/color]

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!

[Erland Sommarskog]

Re: select into withidentity

php newbie (newtophp2000@y ahoo.com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp