November 19, 2009 at 9:50 am
I am trying to insert some rows to a table using a select statement, which seems really simple but I am missing something. It's a messy database to begin with (bizarre schema but we have to work with what we've got).
The table consists of the following fields: COL1(VARCHAR 10), COL2(VARCHAR 10), COL3(DATETIME), COL4(VARCHAR 2048), COL5(VARCHAR 1), COL6(VARCHAR 10), COL7(VARCHAR 10), COL8(VARCHAR 3)
COL1 and COL7 are composite primary keys. COL1 is a client name, and COL7 is an incrementing row number for each client (which although defined as a varchar, contains a numeric value).
I need to select a set of data from other tables, and insert it to this table. However, for each entry, I need to increment the value of COL7 to the next value for the specific company. What is the best way to do this?
INSERT INTO dbo.TABLE1
( COL1 ,
COL2 ,
COL3 ,
COL4 ,
COL5 ,
COL6 ,
COL7 ,
COL8
)
( SELECT T2.COLA ,
T2.COLB ,
GETDATE() ,
'TEXT STRING ' + CONVERT(VARCHAR, GETDATE(), 103) ,
'Y' ,
'USERCODE' ,
( SELECT MAX(COL7) + 1
FROM TABLE1 AS T1
WHERE T1.COL1 = T2.COLA
) ,
'1'
FROM TABLE2 AS T2
WHERE ( T2.COLC > 5.00 )
AND T2.COLA IN ( 'COMPANY' )
AND EXISTS ( SELECT *
FROM TABLE3 AS T3
WHERE T3.COLA = T2.COLA
AND T3.COLB = T2.COLB
AND T3.COLC = T2.COLC
AND DATEDIFF(dd, T3.COLD,
GETDATE()) > 30 )
GROUP BY T2.COLA ,
T2.COLB
)
November 19, 2009 at 10:11 am
The problem is the way SQL handles these. That will be an all at once operation, meaning that every line you insert will use the same MAX value + 1, which will give you errors if you insert multiple items with the same COL1. It doesn't insert the lines one at a time, then grab a new max.
Try something like the following. Note that because I didn't have sample data, this is untested.
;WITH SEL AS(
SELECT T2.COLA COL1, T2.COLB COL2, GETDATE() COL3, 'TEXT STRING ' + CONVERT(varchar, GETDATE(), 103) COL4, 'Y' COL5,
'USERCODE' COL6, MaxCol7 COL7, '1' COL8
FROM TABLE2 AS T2
LEFT JOIN ( SELECT COL1, MAX(COL7) MaxCol7
FROM Table1
GROUP BY Col1) T1 ON T2.ColA = T1.Col1
WHERE (T2.COLC > 5.00)
AND T2.COLA IN ('COMPANY')
AND EXISTS ( SELECT *
FROM TABLE3 AS T3
WHERE T3.COLA = T2.COLA
AND T3.COLB = T2.COLB
AND T3.COLC = T2.COLC
AND DATEDIFF(dd, T3.COLD, GETDATE()) > 30 )
GROUP BY T2.COLA, T2.COLB)
INSERT INTO dbo.TABLE1 (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8)
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7+ RN, COL8
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) RN
FROM SEL) SEL
It is possible this could be optimized/simplified further, but I didn't want to change it too much without knowing your actual data. All I really attempted to do here is use the ROW_NUMBER() function to insert sequential values into COL7.
[EDIT] Typo in the RowNumber code. [/Edit]
November 19, 2009 at 10:14 am
This has "concurrency issue" written all over it, so you will need to ensure noone else is actively inserting during this process.
That said - if you can squeeze this in during a time when noone else is inserting, try something like this:
;With MAXCTE as
(select Max(col7) MaxCol,cola from table1 group by cola),
IncrementCTE as
(select T2.cola,
t2.colb,
ROW_NUMBER() over (partition by cola order by colb) RN
From TABLE2 AS T2
WHERE ( T2.COLC > 5.00 )
AND EXISTS ( SELECT *
FROM TABLE3 AS T3
WHERE T3.COLA = T2.COLA
AND T3.COLB = T2.COLB
AND T3.COLC = T2.COLC
AND DATEDIFF(dd, T3.COLD,
GETDATE()) > 30 )
)
INSERT INTO dbo.TABLE1
( COL1 ,
COL2 ,
COL3 ,
COL4 ,
COL5 ,
COL6 ,
COL7 ,
COL8
)
SELECT COLA ,
COLB ,
GETDATE() ,
'TEXT STRING ' + CONVERT(VARCHAR, GETDATE(), 103) ,
'Y' ,
Maxcol+RN,
'1'
FROM MAXCTE
inner join IncrementCTE on maxcte.cola=incrementCTE.cola
GROUP BY COLA ,
COLB
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2009 at 10:20 am
Oops, mine didn't handle NULLS or the fact that COL7 is actually a varchar. One sec...
Modified:
;WITH SEL AS(
SELECT T2.COLA COL1, T2.COLB COL2, GETDATE() COL3, 'TEXT STRING ' + CONVERT(varchar, GETDATE(), 103) COL4, 'Y' COL5,
'USERCODE' COL6, ISNULL(MaxCol7,0) COL7, '1' COL8
FROM TABLE2 AS T2
LEFT JOIN ( SELECT COL1, MAX(CAST(COL7 as int)) MaxCol7
FROM Table1
GROUP BY Col1) T1 ON T2.ColA = T1.Col1
WHERE (T2.COLC > 5.00)
AND T2.COLA IN ('COMPANY')
AND EXISTS ( SELECT *
FROM TABLE3 AS T3
WHERE T3.COLA = T2.COLA
AND T3.COLB = T2.COLB
AND T3.COLC = T2.COLC
AND DATEDIFF(dd, T3.COLD, GETDATE()) > 30 )
GROUP BY T2.COLA, T2.COLB)
INSERT INTO dbo.TABLE1 (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8)
SELECT COL1, COL2, COL3, COL4, COL5, COL6, CAST(COL7+RN as varchar(10)), COL8
FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) RN
FROM SEL) SEL
November 19, 2009 at 2:07 pm
Thanks for your help, I got it working with your suggestions. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply