October 15, 2009 at 3:51 pm
another alternative would be to use left(VEHDESC,41)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 15, 2009 at 3:53 pm
Or you can take a substring.
Look up LEFT and SUBSTRING in SQL's books online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2009 at 3:54 pm
jordon.shaw (10/15/2009)
One last question and I'll leave everybody alone. In this script, I'm inserting VEHDESC from IMSV7.COMPVEH into ASSETDESC in FA00100; however, VEHDESC is a datatype of varchar(256) and ASSETDESC is datatype varchar(41). Is there a way to tell SQL to only insert the first 41 characters?
I'm with the Joker on this one, since they're both varchar fields (i.e. text) then there shouldn't be any need to convert, and you can just left() it.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 15, 2009 at 3:57 pm
I know that I said I would leave y'all alone, but I lied. Here is a issue, if you look, I'm setting a varible of assetindex to what is currently there and adding one, see this line: SELECT @assetindex = MAX(ASSETINDEX)+1 FROM FA00100. With that being said, it only works for one of these records, since I'm not looping, its not adding one for each record. Suggestion? How do I make this add 1 to ASSETINDEX for each record using the insert statement the way y'all have suggested?
Thanks,
Jordon
October 15, 2009 at 4:10 pm
Probably you can use identity column on the ASSETINDEX. The MAX() + 1 approach is tricky and can kill performance.
Regards
Piotr
...and your only reply is slàinte mhath
October 15, 2009 at 4:17 pm
jordon.shaw (10/15/2009)
How do I make this add 1 to ASSETINDEX for each record using the insert statement the way y'all have suggested?
Make the column identity and let SQL handle it. Managing your own counter usually results in one of two things - bad performance or duplicate values/duplicate key errors. Which one you get depends what you do with isolation level and transactions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 7:43 am
Oh, how I wish I could do that. The way I'm entering these records isn't the normal process, there is an application that normally enters these records. I'm just trying to get the records from one system to the other. So, the peice of crap application, which I'm not going to call names, because I don't want to bring Microsoft into this :-D, but it does what I was trying to do where when you save a record, it finds the max number and adds one to it. The application can get away with it, because its only entering one at a time, whereas I'm trying to push about 550 at one time. So, I'm afraid that I do need a way to loop and add that number as this query runs. Is there anyway possible?
Jordon
October 16, 2009 at 8:02 am
How about @assetIndex = @assetIndex + 1?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 16, 2009 at 8:04 am
Jordon, where is the last number stored, if not in the table you are inserting to? I see an immediate conflict if you are inserting a set of rows at the same time the application-that-must-not-be named is inserting a row. You don't have to start at 1 every time you have an identity column, and there are other set-based techniques available, which we will discuss. But if you start a series at 551 for example and are inserting 1000 rows, you can't have the other application picking up number 552 and trying to insert it.
Help us out and with details, when you see what looks like a roadblock. We have answers, but we aren't familiar with your setup, and we're not mind readers.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 16, 2009 at 8:12 am
The application will never be entering these records at the same time this query is going to run. Basically, I'm going to create this as a stored procedure that runs after hours. Basically, I have two systems, one used for maintenance on vehicles and the other used for financial tracking on vehicles. All my vehicles are currently in the maintenance system and I'm trying to get them over to the finance system. The first time, it will be about 550 records, but then after the first push, it will only be 1 or so every now and then. I have the two servers linked together and I'm just basically trying to get it for when my maintenance guys get a new vehicle and enter it into thier system, that it will then automatically push into the financial system for they can do their accounting on it.
This is my table that I'm inserting to:
USE [FTEST]
GO
/****** Object: Table [dbo].[FA00100] Script Date: 10/16/2009 09:10:22 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[FA00100](
[ASSETINDEX] [int] NOT NULL,
[ASSETID] [char](15) NOT NULL,
[ASSETIDSUF] [smallint] NOT NULL,
[SHRTNAME] [char](15) NOT NULL,
[ASSETDESC] [char](41) NOT NULL,
[EXTASSETDESC] [char](41) NOT NULL,
[Master_Asset_ID] [char](19) NOT NULL,
[STRUCTUREID] [char](31) NOT NULL,
[ASSETCLASSID] [char](15) NOT NULL,
[LOCATNID] [char](15) NOT NULL,
[ACQDATE] [datetime] NOT NULL,
[Acquisition_Cost] [numeric](19, 5) NOT NULL,
[ASSETTYPE] [smallint] NOT NULL,
[ASSETSTATUS] [smallint] NOT NULL,
[PROPTYPE] [smallint] NOT NULL,
[ASSETQTY] [int] NOT NULL,
[ASSETBEGQTY] [int] NOT NULL,
[ASSETCURRMAINT] [numeric](19, 5) NOT NULL,
[ASSETYTDMAINT] [numeric](19, 5) NOT NULL,
[ASSETLTDMAINT] [numeric](19, 5) NOT NULL,
[LASTMAINTDATE] [datetime] NOT NULL,
[ASSESSEDVALUE] [numeric](19, 5) NOT NULL,
[MFGRNAME] [char](25) NOT NULL,
[SERLNMBR] [char](21) NOT NULL,
[MODELNUMBER] [char](21) NOT NULL,
[WARRENTYDATE] [datetime] NOT NULL,
[CUSTODIAN] [char](25) NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[DATEADDED] [datetime] NOT NULL,
[DELETEDATE] [datetime] NOT NULL,
[Physical_Location_ID] [char](15) NOT NULL,
[Asset_Label] [char](19) NOT NULL,
[Verified_Date] [datetime] NOT NULL,
[PIN] [char](15) NOT NULL,
[LASTPURCHLINESEQ] [int] NOT NULL,
[LASTMNTDDATE] [datetime] NOT NULL,
[LASTMNTDTIME] [datetime] NOT NULL,
[LASTMNTDUSERID] [char](15) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKFA00100] PRIMARY KEY CLUSTERED
(
[ASSETINDEX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[ACQDATE]) = 0 and datepart(minute,[ACQDATE]) = 0 and datepart(second,[ACQDATE]) = 0 and datepart(millisecond,[ACQDATE]) = 0))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[DATEADDED]) = 0 and datepart(minute,[DATEADDED]) = 0 and datepart(second,[DATEADDED]) = 0 and datepart(millisecond,[DATEADDED]) = 0))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[DELETEDATE]) = 0 and datepart(minute,[DELETEDATE]) = 0 and datepart(second,[DELETEDATE]) = 0 and datepart(millisecond,[DELETEDATE]) = 0))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[LASTMAINTDATE]) = 0 and datepart(minute,[LASTMAINTDATE]) = 0 and datepart(second,[LASTMAINTDATE]) = 0 and datepart(millisecond,[LASTMAINTDATE]) = 0))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[LASTMNTDDATE]) = 0 and datepart(minute,[LASTMNTDDATE]) = 0 and datepart(second,[LASTMNTDDATE]) = 0 and datepart(millisecond,[LASTMNTDDATE]) = 0))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(day,[LASTMNTDTIME]) = 1 and datepart(month,[LASTMNTDTIME]) = 1 and datepart(year,[LASTMNTDTIME]) = 1900))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[Verified_Date]) = 0 and datepart(minute,[Verified_Date]) = 0 and datepart(second,[Verified_Date]) = 0 and datepart(millisecond,[Verified_Date]) = 0))
GO
ALTER TABLE [dbo].[FA00100] WITH CHECK ADD CHECK ((datepart(hour,[WARRENTYDATE]) = 0 and datepart(minute,[WARRENTYDATE]) = 0 and datepart(second,[WARRENTYDATE]) = 0 and datepart(millisecond,[WARRENTYDATE]) = 0))
As you see, there is a DEX_ROW_ID that is the indentity key; however, the primary key is ASSETINDEX and it must be unique, but I can't change it to an indentity key.
Hopefully this helps when I'm trying to do and thank you all for your help!
Jordon
October 16, 2009 at 8:32 am
INSERT INTO FTEST.dbo.FA00100(<column_list>)
SELECT
D2.assetindex + D1.RowNum
,<Other_Columns>
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY 1) AS RowNum
,<Other_Columns>
FROM INFOR.TRAIN.IMSV7.COMPVEH
LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
) D1
CROSS JOIN
(
SELECT MAX(assetindex) AS assetindex
FROM FTEST.dbo.FA00100 WITH (UPDLOCK)
) D2
October 16, 2009 at 8:47 am
I was going to do a version with a CTE, but it wouldn't have been substantively different from your solution, Ken.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 16, 2009 at 8:50 am
Well, I'm trying to run just the query to see the results, prior to doing the insertation; however, I'm getting an error that says" Msg 5308, Level 16, State 1, Line 79
Windowed functions do not support integer indices as ORDER BY clause expressions. "
This is my code:
declare @unitid varchar(16),
@assetindex int,
@assetidsuf smallint,
@shrtname char(15),
@assetdesc varchar(41),
@masterassetid char(19),
@structureid char(31),
@assetclassid char(15),
@locatnid char(15),
@acqdate datetime,
@cost float,
@assettype smallint,
@assetstatus smallint,
@proptype smallint,
@assetqty int,
@assetbegqty int,
@assetcurrmaint numeric(19,5),
@assetytdmaint numeric(19,5),
@assetltdmaint numeric(19,5),
@lastmaintdate datetime,
@assessedvalue numeric,
@mfgrname char(25),
@serlnmbr char(21),
@modelnumber char(21),
@warrentydate datetime,
@custodian char(25),
@noteindex numeric(19,5),
@dateadded datetime,
@deletedate datetime,
@location_id char(15),
@assetlabel char(19),
@verifieddate datetime,
@pin char(15),
@lastpurchline int,
@lastmntdate datetime,
@lastmnttime datetime,
@lastnmtuser char(15)
SELECT @assetclassid = GPID
FROM INFOR.TRAIN.IMSV7.compgp
WHERE HANID = '37'
SET @assetidsuf = '1';
SET @shrtname = ' ';
SET @masterassetid = ' ';
SET @structureid = ' ';
SET @locatnid = ' ';
SET @assettype = '1';
SET @assetstatus = '1';
SET @proptype = '1' ;
SET @assetqty = '1';
SET @assetbegqty = '1';
SET @assetcurrmaint = '0.00000';
SET @assetytdmaint = '0.00000';
SET @assetltdmaint = '0.00000';
SET @lastmaintdate = '1900-01-01 00:00:00.000';
SET @assessedvalue = '0.00000';
SET @mfgrname = ' ';
SET @serlnmbr = ' ';
SET @modelnumber = ' ';
SET @warrentydate = '1900-01-01 00:00:00.000';
SET @custodian = ' ';
SET @noteindex = '0.00000';
SET @dateadded = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @deletedate = '1900-01-01 00:00:00.000';
SET @location_id = ' ';
SET @assetlabel = ' ';
SET @verifieddate = '1900-01-01 00:00:00.000';
SET @pin = ' ';
SET @lastpurchline = '1';
SET @lastmntdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
SET @lastmnttime ='1900-01-01 00:00:00.000';
SET @lastnmtuser ='sa'
SELECT
D2.assetindex + D1.RowNum
,UNITID,
@assetidsuf,
@shrtname,
convert(varchar(41), VEHDESC),
convert(varchar(41), VEHDESC),
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY 1) AS RowNum
,UNITID,
@assetidsuf,
@shrtname,
convert(varchar(41), VEHDESC),
convert(varchar(41), VEHDESC),
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM INFOR.TRAIN.IMSV7.COMPVEH
LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
) D1
CROSS JOIN
(
SELECT MAX(assetindex) AS assetindex
FROM FTEST.dbo.FA00100 WITH (UPDLOCK)
) D2
Any suggestions?
October 16, 2009 at 9:00 am
jordon.shaw (10/16/2009)
Well, I'm trying to run just the query to see the results, prior to doing the insertation; however, I'm getting an error that says" Msg 5308, Level 16, State 1, Line 79Windowed functions do not support integer indices as ORDER BY clause expressions. "
The error means exactly what it says. The Row_Number cannot have a constant as the order by. There is, however a dirty little trick that currently works. Absolutely no guarantees that this will work in future versions as it's probably not supposed to be allowed. Is a bit of a loophole...
If you don't care about the order of those row_numbers, this is an option
(simplified code, you should be able to adapt this to your query)
SELECT NAME,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNo
FROM sys.tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 9:07 am
This project is almost becoming more trouble than its worth, but the boss wants it done. So, I added the SELECT 1 to my order by statement and now I'm getting several no column was specified for column... of 'D1' Here is the error:
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 3 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 4 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 5 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 6 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 7 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 8 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 9 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 10 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 13 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 14 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 15 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 16 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 17 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 18 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 19 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 20 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 21 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 22 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 23 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 24 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 25 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 26 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 27 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 28 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 29 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 30 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 31 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 32 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 33 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 34 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 35 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 36 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 37 of 'D1'.
Msg 8155, Level 16, State 2, Line 77
No column was specified for column 38 of 'D1'.
Msg 207, Level 16, State 1, Line 82
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply