March 26, 2012 at 5:52 am
Hello everybody,
first post here but thanks for all the work behind the site, it has been very useful in the past.
I have a problem with an INSERT....SELECT statement, because the SELECT statement contains a MAX function from the table I'm trying to inserting values. I don't know how to get the right value I need and obviously I get always a primary key violation error.
I know a can use a cursor to have the same result, but IMHO a cursor is slower than a join, please correct me if I'm wrong.
Below I created a script with some test tables that reproduces the error, in the real case it's a little bit more complicated because of other joined tables and those tables are all variable tables.
-- Deleting test tables
IF OBJECT_ID('TempDB..#Storico','U') IS NOT NULL DROP TABLE #Storico
IF OBJECT_ID('TempDB..#Clienti','U') IS NOT NULL DROP TABLE #Storico
IF OBJECT_ID('TempDB..#Ordini','U') IS NOT NULL DROP TABLE #Storico
-- Creating test tables with PKs
CREATE TABLE #Storico(codCli char (10) not null, nProg int not null, cognome char (20) not null, nome char (20), ord char (10) not null PRIMARY KEY CLUSTERED (codCli, nProg))
CREATE TABLE #Clienti(codCli char (10) not null, cognome char (20) not null, nome char (20) not null PRIMARY KEY CLUSTERED (codCli))
CREATE TABLE #Ordini(ord char (10) not null, prezzo decimal(9, 2) not null, codCli char (10) not null PRIMARY KEY CLUSTERED (ord))
-- Populating test tables...
INSERT INTO #Clienti VALUES ('0000000001', 'Primo Nome', 'Primo Cognome')
INSERT INTO #Clienti VALUES ('0000000002', 'Secondo Nome', 'Secondo Cognome')
INSERT INTO #Clienti VALUES ('0000000003', 'Terzo Nome', 'Terzo Cognome')
INSERT INTO #Clienti VALUES ('0000000004', 'Quarto Nome', 'Quarto Cognome')
INSERT INTO #Clienti VALUES ('0000000005', 'Quinto Nome', 'Quinto Cognome')
INSERT INTO #Clienti VALUES ('0000000006', 'Sesto Nome', 'Sesto Cognome')
INSERT INTO #Clienti VALUES ('0000000007', 'Settimo Nome', 'Settimo Cognome')
INSERT INTO #Clienti VALUES ('0000000008', 'Ottavo Nome', 'Ottavo Cognome')
--....
INSERT INTO #Ordini VALUES ('ORD001', 100, '')
INSERT INTO #Ordini VALUES ('ORD002', 100, '0000000001')
INSERT INTO #Ordini VALUES ('ORD003', 100, '0000000004')
INSERT INTO #Ordini VALUES ('ORD004', 100, '')
INSERT INTO #Ordini VALUES ('ORD005', 100, '0000000007')
INSERT INTO #Ordini VALUES ('ORD006', 100, '')
INSERT INTO #Ordini VALUES ('ORD007', 100, '0000000004')
INSERT INTO #Ordini VALUES ('ORD008', 200, '0000000003')
INSERT INTO #Ordini VALUES ('ORD009', 300, '0000000001')
INSERT INTO #Ordini VALUES ('ORD010', 400, '0000000001')
INSERT INTO #Ordini VALUES ('ORD011', 500, '')
INSERT INTO #Ordini VALUES ('ORD012', 300, '0000000008')
INSERT INTO #Ordini VALUES ('ORD013', 700, '0000000005')
INSERT INTO #Ordini VALUES ('ORD014', 300, '0000000002')
INSERT INTO #Ordini VALUES ('ORD015', 900, '0000000002')
INSERT INTO #Ordini VALUES ('ORD016', 200, '')
INSERT INTO #Ordini VALUES ('ORD017', 100, '')
--...
INSERT INTO #Storico VALUES ('0000000001' ,1 ,'Primo Nome', 'Primo Cognome', 'ORD002')
INSERT INTO #Storico VALUES ('0000000001' ,2 ,'Primo Nome', 'Primo Cognome', 'ORD009')
INSERT INTO #Storico VALUES ('0000000003' ,1 ,'Terzo Nome', 'Terzo Cognome', 'ORD008')
-- Trying to insert data gathering from existins tables
INSERT INTO #Storico
SELECT #Clienti.codCli,
(SELECT ISNULL(MAX(nProg),1) FROM #Storico WHERE #Storico.codCli = #Clienti.codCli),-- Raise a primary key violation error !!!!!!
#Clienti.cognome,
#Clienti.nome,
#Ordini.ord
FROM #Clienti, #Ordini
WHERE #Clienti.codCli = #Ordini.codCli
Thanks,
__Danilo
March 26, 2012 at 6:50 am
What column from which table are you trying to evaluate MAX() of?
Currently you have a "circular" evaluation - the MAX() of something which is never assigned a value.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2012 at 8:12 am
ChrisM@Work (3/26/2012)
What column from which table are you trying to evaluate MAX() of?
Column 'nProg' from '#Storico' table.
ChrisM@Work (3/26/2012)
Currently you have a "circular" evaluation - the MAX() of something which is never assigned a value.
I know there is this possibility, so I used the ISNULL statement.
This is the result I'll expect
SELECT * FROM #Storico
codCli nProg cognome nome ord
---------- ----------- -------------------- -------------------- ----------
0000000001 1 Primo Nome Primo Cognome ORD002
0000000001 2 Primo Nome Primo Cognome ORD009
0000000001 3 Primo Nome Primo Cognome ORD002
0000000001 4 Primo Nome Primo Cognome ORD009
0000000001 5 Primo Nome Primo Cognome ORD010
0000000002 1 Secondo Nome Secondo Cognome ORD015
0000000002 2 Secondo Nome Secondo Cognome ORD014
0000000003 1 Terzo Nome Terzo Cognome ORD008
0000000003 2 Terzo Nome Terzo Cognome ORD008
0000000004 1 Quarto Nome Quarto Cognome ORD003
0000000004 2 Quarto Nome Quarto Cognome ORD007
0000000005 1 Quinto Nome Quinto Cognome ORD013
0000000007 1 Settimo Nome Settimo Cognome ORD005
0000000008 1 Ottavo Nome Ottavo Cognome ORD012
ehm...noticed a little mistake on deleting tables
This is right version, sorry....:ermm:
-- Deleting test tables
IF OBJECT_ID('TempDB..#Storico','U') IS NOT NULL DROP TABLE #Storico
IF OBJECT_ID('TempDB..#Clienti','U') IS NOT NULL DROP TABLE #Clienti
IF OBJECT_ID('TempDB..#Ordini','U') IS NOT NULL DROP TABLE #Ordini
__Danilo
March 26, 2012 at 8:22 am
Stripping out a lot of extras maybe this will help show you where the error is. I don't know what you are trying to accomplish but the error is pretty obvious when you make the data small.
-- Deleting test tables
IF OBJECT_ID('TempDB..#Storico','U') IS NOT NULL DROP TABLE #Storico
IF OBJECT_ID('TempDB..#Clienti','U') IS NOT NULL DROP TABLE #Clienti
IF OBJECT_ID('TempDB..#Ordini','U') IS NOT NULL DROP TABLE #Ordini
-- Creating test tables with PKs
CREATE TABLE #Storico(codCli char (10) not null, nProg int not null, cognome char (20) not null, nome char (20), ord char (10) not null PRIMARY KEY CLUSTERED (codCli, nProg))
CREATE TABLE #Clienti(codCli char (10) not null, cognome char (20) not null, nome char (20) not null PRIMARY KEY CLUSTERED (codCli))
CREATE TABLE #Ordini(ord char (10) not null, prezzo decimal(9, 2) not null, codCli char (10) not null PRIMARY KEY CLUSTERED (ord))
-- Populating test tables...
INSERT INTO #Clienti VALUES ('0000000001', 'Primo Nome', 'Primo Cognome')
INSERT INTO #Ordini VALUES ('ORD002', 100, '0000000001')
INSERT INTO #Ordini VALUES ('ORD009', 300, '0000000001')
INSERT INTO #Ordini VALUES ('ORD010', 400, '0000000001')
INSERT INTO #Storico VALUES ('0000000001' ,1 ,'Primo Nome', 'Primo Cognome', 'ORD002')
INSERT INTO #Storico VALUES ('0000000001' ,2 ,'Primo Nome', 'Primo Cognome', 'ORD009')
INSERT INTO #Storico VALUES ('0000000003' ,1 ,'Terzo Nome', 'Terzo Cognome', 'ORD008')
-- Trying to insert data gathering from existins tables
--INSERT INTO #Storico
SELECT #Clienti.codCli,
(SELECT ISNULL(MAX(nProg),1) FROM #Storico WHERE #Storico.codCli = #Clienti.codCli),-- Raise a primary key violation error !!!!!!
#Clienti.cognome,
#Clienti.nome,
#Ordini.ord
FROM #Clienti
left join #Ordini on #Clienti.codCli = #Ordini.codCli
Notice I commented out the INSERT line above so you can the values that will be attempted as an insert. You can see clearly that columns 1 and 2 have the same data in all 3 rows. This is defined as your primary key which just isn't going to work for you. Maybe you need to expand the primary key to include the "ord" column?
_______________________________________________________________
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/
March 26, 2012 at 8:53 am
Sean Lange (3/26/2012)
Notice I commented out the INSERT line above so you can the values that will be attempted as an insert. You can see clearly that columns 1 and 2 have the same data in all 3 rows. This is defined as your primary key which just isn't going to work for you. Maybe you need to expand the primary key to include the "ord" column?
I know I'm trying to insert the same values in the column 1 and 2, but I don't know how to retrieve different values from the MAX() for every row inserted from the SELECT....JOIN.
I even don't know if it is possible to do what I expect from that statement.....
Thanks
__Danilo
March 26, 2012 at 8:55 am
You can't get different values for Max, it will return the largest value.
_______________________________________________________________
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/
March 26, 2012 at 8:55 am
Maybe if you can more clearly explain what you are trying to do.
_______________________________________________________________
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/
March 26, 2012 at 9:15 am
Alright, been working on this and it has been troubling me this morning. My solution probably isn't ideal but I believe it to be close to what you want.
Before I show it though, I'd like to say thanks for posting all your scripts. They were a great help.
This takes the max nProg number for each codCli and assigns nProg numbers for new orders starting at the max nProg for each codCli.
begin tran
;with cte as(
SELECT
c.codCli,
ROW_NUMBER() over(PARTITION by c.codcli order by c.codcli) num,
c.cognome,
c.nome,
o.ord
FROM #Clienti c
INNER JOIN #Ordini o
ON c.codCli = o.codCli
)
INSERT INTO #Storico (codcli,nprog,cognome,nome,ord)
select
c.codcli,
c.num + (SELECT ISNULL(MAX(nProg),1) FROM #Storico WHERE #Storico.codCli = C.codCli),
c.cognome,
c.nome,
c.ord
From cte c
LEFT JOIN #storico s
on c.codcli = s.codcli
and c.ord = s.ord
--commit
--rollback
March 26, 2012 at 9:50 am
calvo (3/26/2012)
They were a great help.
YOU are a great help !!!!
YEAH !!!! It's exactly what I need.
The only problem is that I don't understand what you wrote, but is my fault, I think I'll start to study mdx query right now.:-D
Thanks again to everybody
__Danilo
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply