Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Manually increment smallint column during an INSERT command. Expand / Collapse
Author
Message
Posted Tuesday, November 5, 2013 2:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
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.
Post #1511626
Posted Tuesday, November 5, 2013 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 13,313, Visits: 12,180
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1511634
Posted Tuesday, November 5, 2013 2:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
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.
Post #1511639
Posted Tuesday, November 5, 2013 6:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1511689
Posted Wednesday, November 6, 2013 7:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
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...
Post #1511883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse