November 5, 2013 at 2:04 pm
Here is how the table is created...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SPEDDTL](
[spdd_f1] [char](2) NOT NULL,
[spdd_f2] [char](1) NOT NULL,
[spdd_f3] [char](1) NOT NULL,
[spdd_f4] [int] NOT NULL,
[spdd_f5] [smallint] NOT NULL,
[spdd_f6] [smallint] NULL,
[spdd_f7] [decimal](9, 2) NULL,
[spdd_Identity] [numeric](9, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [ISPEDDTL0] ON [dbo].[SPEDDTL]
(
[spdd_f1] ASC,
[spdd_f2] ASC,
[spdd_f3] ASC,
[spdd_f4] ASC,
[spdd_f5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
GO
SET ANSI_PADDING OFF
GO
Here is how I am scripting the insert from the other table with the manual number column issue...
INSERT INTO SPEDDTL (spdd_f1, spdd_f2, spdd_f3, spdd_f4, spdd_f5, spdd_f6, spdd_f7)
( SELECT spd_f1, spd_f2, spd_f3, spd_f4, {manual number value}, spd_f6, spd_f7 FROM SPED WHERE NOT EXISTS
(SELECT * FROM SPEDDTL WHERE spdd_f1 = spd_f1 and spdd_f2 = spd_f2 and spdd_f3 = spd_f3 and spdd_f4 = spd_f4))
I cannot change spdd_f5 into an auto-increment field, must stay as created.
November 5, 2013 at 2:13 pm
Generically you can use ROW_NUMBER for this.
INSERT INTO SPEDDTL (spdd_f1, spdd_f2, spdd_f3, spdd_f4, spdd_f5, spdd_f6, spdd_f7)
( SELECT spd_f1, spd_f2, spd_f3, spd_f4, ROW_NUMBER() over (order by (select null)), spd_f6, spd_f7
FROM SPED WHERE NOT EXISTS
(SELECT * FROM SPEDDTL WHERE spdd_f1 = spd_f1 and spdd_f2 = spd_f2 and spdd_f3 = spd_f3 and spdd_f4 = spd_f4))
There are a couple of issues here.
1) We don't know if you can have duplicates in spdd_f5 or not. If so, you can determine a modifier for ROW_NUMBER prior to this query.
2) There is no way to know what row will get what value because there is no indication of what the order of rows inserted will be.
_______________________________________________________________
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/
November 5, 2013 at 2:20 pm
ROW_NUMBER sounds promising.
the values they want in spdd_f5 will be 1 thru 100 (will never go over 100) no duplicates.
it is just a pad number but it would need to be reset when any of the _f1, _f2, _f3, _f4 columns values change.
this might not be possible in T-SQL, if it isn't then i will just write a program to do it.
November 5, 2013 at 6:47 pm
roy.tollison (11/5/2013)
ROW_NUMBER sounds promising.the values they want in spdd_f5 will be 1 thru 100 (will never go over 100) no duplicates.
it is just a pad number but it would need to be reset when any of the _f1, _f2, _f3, _f4 columns values change.
this might not be possible in T-SQL, if it isn't then i will just write a program to do it.
I wouldn't be so hasty as to suggest this is not possible in T-SQL. I think this is something like what you're looking for:
INSERT INTO SPEDDTL (spdd_f1, spdd_f2, spdd_f3, spdd_f4, spdd_f5, spdd_f6, spdd_f7)
SELECT spd_f1, spd_f2, spd_f3, spd_f4
,ROW_NUMBER() OVER (PARTITION BY spd_f1, spd_f2, spd_f3, spd_f4 ORDER BY (SELECT NULL))
,spd_f6, spd_f7
FROM SPED
WHERE NOT EXISTS
(
SELECT *
FROM SPEDDTL
WHERE spdd_f1 = spd_f1 and spdd_f2 = spd_f2 and spdd_f3 = spd_f3 and spdd_f4 = spd_f4
);
However I do have some concerns, for example what if additional matching rows are added to SPED after you've run this query to populate SPEDDTL? Those will never get added to SPEDDTL, and it may be a bit bothersome to add them later. To do that, you'd need to have something better than:
ORDER BY (SELECT NULL)
In the window function. If you could describe what causes the generation of the new SPED records to be added, it might help to identify what you could change the ORDER BY to, and allow you to INSERT the additional records.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 7:33 am
This works for me. These are just one shot deals. another dept has programs for maintaining the data. all they need for me to do was mass copy from one table to the next. kind of like an upgrade, but they just needed some data for testing new programs/features. They will actually write their own upgrade programs/processes. Just needing data for testing.
Thanks so much...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply