Mimicking INDETITY() with INSERT...SELECT
-
FROM SQL BOL :
"IDENTITY (Function)
Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.
Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE."
I would like to simulate this functionality with an INSERT...SELECT.
EX:
--these objects fo not exist, written purely
--from thin air as an example from an existing query attempt
INSERT INTO DestinationTable
SELECT tbl1.Col1,
tbl1.Col2,
tbl1.Col3,
tbl2.Col1,
IDENTITY(int, 1, 1) As SortOrder
FROM tbl1
CROSS JOIN tbl2
WHERE tbl1.tbl1id = 1
Produces :
Server: Msg 177, Level 15, State 1, Line 7
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
Does anyone have an idea on how to do this? I am scratching my head on this one...
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
It would be good if you could maybe explain what you are trying to do.
From what you have supplied I assume that you want to insert some records into a table [DestinationTable] that already exists. During the insert you want the column SortOrder do be automatically assigned an incrementing value. If this is what you are wanting then you could do the following
SELECT tbl1.Col1,
tbl1.Col2,
tbl1.Col3,
tbl2.Col1,
IDENTITY(int, 1, 1) As SortOrder
INTO #t
FROM tbl1
CROSS JOIN tbl2
WHERE tbl1.tbl1id = 1
INSERT INTO DestinationTable
SELECT Col1,
Col2,
Col3,
Col1,
SortOrder
FROM #t
HTH
Tim
-
I believe it means SELECT..INTO..FROM
like so.
SELECT tbl1.Col1,
tbl1.Col2,
tbl1.Col3,
tbl2.Col1,
IDENTITY(int, 1, 1) As SortOrder
INTO DestinationTable
FROM tbl1
CROSS JOIN tbl2
WHERE tbl1.tbl1id = 1
Hey Scorpion, I couldn't help but laugh too. I wonder if anyone else is possibly still in mid typing
Edited by - antares686 on 01/16/2003 4:58:00 PM
-
Create your table with the SortOrder field as an Identity field, and just leave it out of your insert statement altogether.
And of course, the Temp table suggestion works if you cannot have the field as an identity field.
Personally, I would avoid the select into from method altogether, as this is a concurrency killer. Create your table in one statement, populate it in another. Otherwise, you hold locks on system tables for the full length of the insert.
When I went to suggest something, there were no other answers. I get finished typing, and there are three.....LMAO.
Edited by - scorpion_66 on 01/16/2003 4:53:59 PM
Edited by - scorpion_66 on 01/16/2003 4:56:17 PM
-
Nice interesting solution to my question. How could it be done without a temp
table, or cursor though? I usually resort to those as a LAST resort. I can not
use it as an IDENTITY field either as this sort order is by entity. Take for
example the famous pubs. If I had a mythical join table between the authors
table and the titles tables, lets call it authors_favorite_titles, and the table
was designed as follows :
USE PUBS
CREATE TABLE [dbo].[authors_favorite_titles] (
[au_id] [id] NOT NULL ,
[title_id] [tid] NOT NULL ,
[SortOrder] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[authors_favorite_titles] WITH NOCHECK ADD
CONSTRAINT [DF_authors_favorite_titles_SortOrder] DEFAULT (0) FOR [SortOrder],
CONSTRAINT [PK_authors_favorite_titles] PRIMARY KEY CLUSTERED
(
[au_id],
[title_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[authors_favorite_titles] ADD
CONSTRAINT [FK_authors_favorite_titles_authors] FOREIGN KEY
(
[au_id]
) REFERENCES [dbo].[authors] (
[au_id]
),
CONSTRAINT [FK_authors_favorite_titles_titles] FOREIGN KEY
(
[title_id]
) REFERENCES [dbo].[titles] (
[title_id]
)
GOThen the SortOrder would be for that author only as a way to sort his favorites.
I created this table purely as an example so that I would best explain what I am
trying to do. What I want is to write a query that would cross join the authors
and the titles then and insert the records into the join table with a default
sort order. But therein lies my dilemma.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
quote:
I believe it means SELECT..INTO..FROMI do not wish to create a new table as the SELECT INTO will do. I wish to
insert from a cross joined select with an incremented number (that can repeat)
to give that insert a default sort order. I apologize for any misconceptions
generated from my inadequate expression of my issue, sometimes my thoughts
tumble around in the vacuum of my brain, and get mangled in the translation when
output.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
I've only ever used two methods to perform this, and as far as I know (look out), they are the only ways without using an identity field. The first is a temp table, and the second is a subquery that counts the rows in your query for an incrementing counter field, which of course, creates a temp table behind the scenes to to the counting anyway.
Oops, in thinking about it while typing, I've thought of another possibility (shows how far that "as far as I know" goes
). How about a table variable? Can you create an identity field in a table variable, and would your result set fit in memory? -
I found an interesting post that relates to this subject at http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/69091C0DC2E8E996852568E7006CB9A3
From that article :
INSERT INTO customer (CustID, CustName) SELECT COALESCE(MAX(custid)+1,1), 'Foobar Widgets, Inc.' FROM Customer
I am about to try this out. Will post back if it works well.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
That is actually quite cool. I have a couple uses for it myself....
I'm still interested in knowing if it can be done with a table variable though....I'll look into it tomorrow.
-
quote:
I found an interesting post that relates to this subject at http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/69091C0DC2E8E996852568E7006CB9A3From that article :
INSERT INTO customer (CustID, CustName) SELECT COALESCE(MAX(custid)+1,1), 'Foobar Widgets, Inc.' FROM Customer
I am about to try this out. Will post back if it works well.
Well, it worked great, if ONE
inserting one row at a time. The solution I finally settled upon, going back to my previous example was to give the books a default sort, and to insert that when inserting into the authors favorites books table. I would not mind seeing if you got the table variable to work scorpion for my general knowledge, but I have to move on, so don't waste your time unless you want to find the answer...Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
). How about a table variable? Can you create an identity field in a table variable, and would your result set fit in memory?
inserting one row at a time. The solution I finally settled upon, going back to my previous example was to give the books a default sort, and to insert that when inserting into the authors favorites books table. I would not mind seeing if you got the table variable to work scorpion for my general knowledge, but I have to move on, so don't waste your time unless you want to find the answer...Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply