August 6, 2013 at 7:18 am
Code:
SELECT
IDENTITY(INT,1,1) AS D_Key,
C_Key,
D_Num,
D_Name,
D_Address
INTO D_Table2
FROM D_Table1
I need to populate a table based on results from a query. If I do:
INSERT INTO table2
SELECT
IDENTITY(INT,1,1) AS D_Key,
C_Key,
D_Num,
D_Name,
D_Address
FROM D_Table1
It gives me the error: The IDENTITY function can only be used when the SELECT statement has an INTO clause?
August 6, 2013 at 7:39 am
karunakar2351 (8/6/2013)
Code:SELECT
IDENTITY(INT,1,1) AS D_Key,
C_Key,
D_Num,
D_Name,
D_Address
INTO D_Table2
FROM D_Table1
I need to populate a table based on results from a query. If I do:
INSERT INTO table2
SELECT
IDENTITY(INT,1,1) AS D_Key,
C_Key,
D_Num,
D_Name,
D_Address
FROM D_Table1
It gives me the error: The IDENTITY function can only be used when the SELECT statement has an INTO clause?
The error message pretty much says it all. You can't do that in a select statement. Maybe you can use ROW_NUMBER?
INSERT INTO table2
SELECT
ROW_NUMBER() over (order by (select null)),
C_Key,
D_Num,
D_Name,
D_Address
FROM D_Table1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 7:45 am
Or you can define the IDENTITY property on the D_Key field and then insert the data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2013 at 7:49 am
Perhaps the table column you're inserting into needs an identity property. If you must define number externally, then use row_number() or insert into temp table containing identity column, and then insert table from temp table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy