August 24, 2016 at 12:32 pm
This seems fairly simple, yet I've never been able to do this without multiple steps. I'm creating a sp to check to see if a field exists in Table A for any value in Table B. If there is a field in Table B that is not in Table A then I want to take that field and insert it, along with some constants, an incremental value (based on Max in Table A) for a specific record in Table A.
Basically the purpose of this is for any new user created in Table B, I want this job to run the sp to insert this new User's ID into this other Table, with a sequence field in Table A (non-identity) and the value of another field in Table A is based on a specific user (mine).
I can achieve what I want if I manually type in the UserName but then I have to do this for 50 other users, then it's manual for all new users. Here is the code for the manual insert:
insert into TableA
(UserName,Sequence,Title,Signature,IsDefault)
select '<insertUserID>',(select Max(Sequence+1) from TableA),Title,Signature,'Y'
from TableA
where UserName='me'
I've created a sp and have temp table that grabs all the new UserNames that I need to do this for (#TempNewUsers).
1. How do I add all of these UserNames from #TempNewUsers into this select statement?
2. Will the Sequence+1 put the same value for all items or will it +1 per each UserName ie do I have to declare a variable first then add 1 for each UserName?
August 24, 2016 at 1:29 pm
Luv SQL (8/24/2016)
This seems fairly simple, yet I've never been able to do this without multiple steps. I'm creating a sp to check to see if a field exists in Table A for any value in Table B. If there is a field in Table B that is not in Table A then I want to take that field and insert it, along with some constants, an incremental value (based on Max in Table A) for a specific record in Table A.Basically the purpose of this is for any new user created in Table B, I want this job to run the sp to insert this new User's ID into this other Table, with a sequence field in Table A (non-identity) and the value of another field in Table A is based on a specific user (mine).
I can achieve what I want if I manually type in the UserName but then I have to do this for 50 other users, then it's manual for all new users. Here is the code for the manual insert:
insert into TableA
(UserName,Sequence,Title,Signature,IsDefault)
select '<insertUserID>',(select Max(Sequence+1) from TableA),Title,Signature,'Y'
from TableA
where UserName='me'
I've created a sp and have temp table that grabs all the new UserNames that I need to do this for (#TempNewUsers).
1. How do I add all of these UserNames from #TempNewUsers into this select statement?
2. Will the Sequence+1 put the same value for all items or will it +1 per each UserName ie do I have to declare a variable first then add 1 for each UserName?
Why not just make your column an identity and stop killing yourself? You seem to putting a lot of importance of the value of what is just a number. The number itself really doesn't mean anything.
_______________________________________________________________
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/
August 24, 2016 at 1:41 pm
This is not my database, nor my tables. I cannot modify the existing table structure as that will have an impact on the application that updates this table. Plus if I modify it now, futures application updates will overrides my changes. The sequence number is used by the application as well (ie it displays) so I have to update it.
August 24, 2016 at 2:33 pm
Look up the OUTPUT clause, and the INSERT or MERGE statement. When you add a row to Table B using Merge, you can immediately get back all of the output from the row or rows that were just inserted. Store that output, in a temporary table or table variable, then use it to insert into table A.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 7:28 pm
Work in progress. Now that I've got more time, I thought you might like to see a code sample of how OUTPUT works, just to make sure we're on the right track.
CREATE TABLE #TableA (ID int primary key, SomeDataFromTableB varchar(50));
CREATE TABLE #TableB (ID int primary key identity(1,1), TableBData varchar(50));
INSERT INTO #TableB
OUTPUT Inserted.ID, Inserted.TableBData+' Magic'
INTO #TableA (ID,SomeDataFromTableB)
VALUES ('Alpha'),('Beta'),('Gamma'),('Delta')
select '#TableB',* from #TableB
select '#TableA',* from #TableA
GO
drop #TableA
drop #TableB
As you can see, #TableB is assigning ID's sequentially because it is an identity column. #Table A doesn't have an identity column, but the OUTPUT clause sends across the newly-inserted ID from #TableB. Please let us know if we're misunderstanding your question.
Your examples are very confusing. You are inserting into Table A from Table A. You don't tell us anything about the #temp table with the user information. I don't understand why you have a constant '<insertuser>' instead of just referencing the name of the column containing the user information.
PLEASE do this for us. Send CREATE TABLE scripts for just the tables you mention in the first paragraph. No additional columns other than what you are referencing in your example. Send us a script for TableA, TableB and any other tables from which you are pulling information. THEN send us scripts to insert a few sample rows into each of these tables. THEN draw us a picture of what the final state of the files should be after the code runs.
It is difficult to conceptualize the problem based on your descriptions. Just show us what you want instead and multiple people will look at coding it for you. Help us help you.
Thanks
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2016 at 8:33 am
My apologies. The reason I'm referencing Table A twice is that I need to insert the column values for Title and Signature to all the inserts from one specific record. It's a very lengthy string so I guess I could just copy it and insert it as a string if that's easier, however, if I then change that signature in the application and the field gets updated, I manually have to update the string value. I thought it would be easier to update using the value versus inputting a constant.
The reason I'm using a temp table is that I don't know how to do this with a simple insert into statement. If that's possible, then I don't need a temp table.
Here is more of an explanation:
Table A is the table I need to insert the values into. I need to insert the UserName, Sequence, Title, Signature, IsDefault column values to all the inserts. I am using the where TableA.UserName='me' because I need all inserts, other than the UserName, to be these values. They may change, so I didn't use string values.
Here is Table A's columns:
[KeyID] [bigint] IDENTITY(1,1) NOT NULL,
[UserName] [dbo].[bVPUserName] NOT NULL,
[Sequence] [int] NOT NULL,
[Title] [dbo].[bDesc] NOT NULL,
[Signature] [dbo].[bFormattedNotes] NULL,
[IsDefault] [dbo].[bYN] NULL CONSTRAINT [DF_vPMEmailSignature_IsDefault] DEFAULT ('N')
I need to grab the UserName field values from Table B and insert them into TableA. I also need to first verify if that UserName first exists with a IsDefault='Y', if it does, don't insert, if it doesn't, insert.
For the temp table, here is what I have:
Create Table #TempTable1(
UserName varchar(128)
)
insert into #TempTable1
select UserName
from TableB
where UserName not in (select UserName from TableA where IsDefault='Y')
insert into TableA
(Sequence,Title,Signature,IsDefault,UserName)
select (select Max(a.Sequence+1) from TableA),a.Title,a.Signature,'Y',#TempTable1
from TableA a
where a.UserName='me'
I can't use a join here since TableA doesn't yet have those values.
Maybe what I could do is insert blanks first, then an update statement after as there will be a join. That might be the easiest.
So if I do that, how can I assign a sequential sequence number to each record I insert, or how would I do it with an update statement?
August 25, 2016 at 9:44 am
INSERT INTO dbo.TableA
( Sequence, Title, Signature, IsDefault, UserName )
SELECT A_Last.Sequence + B.row_num, A.Title, A.Signature, 'Y' AS IsDefault, B.UserName
FROM (
SELECT B2.*, ROW_NUMBER() OVER(ORDER BY B2.UserName) AS row_num
FROM dbo.TableB B2
WHERE NOT EXISTS(SELECT 1 FROM dbo.TableA A2 WHERE A2.UserName = B2.UserName AND A2.IsDefault = 'Y')
) AS B
INNER JOIN dbo.TableA A ON A.UserName = 'me'
CROSS JOIN (SELECT MAX(A3.Sequence) AS Sequence FROM dbo.TableA A3 WITH (TABLOCK)) AS A_Last
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2016 at 9:46 am
I went into a completely different way and used variables.
ALTER proc [dbo].[SG_AddEmailSignaturesNewUsers]
as
DECLARE @SeqNo int, @DefaultSignature varchar(MAX)
SET @SeqNo=(select max(Sequence) from TableA)
SET @DefaultSignature=(select Signature from TableA where UserName='me' and IsDefault='Y')
insert into TableA
(Sequence,Title,Signature,IsDefault,UserName)
select @SeqNo+1,'Default',@DefaultSignature,'Y',d.UserName
from TableB b
b.UserName not in (select UserName from TableA where IsDefault='Y')
It's working, except that it's inserting the same sequence value for each insert. I'm trying to have it add 1 for each insert. I'll keep plugging away at it.
August 25, 2016 at 9:53 am
I think what you are looking for is ROW_NUMBER(). Something like the following:
SELECT *, ROW_NUMBER() OVER(ORDER BY <pick some order here>) + ( SELECT MAX(sequence) FROM TableA)
FROM TableB
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 25, 2016 at 10:06 am
I need to grab the sequence number from TableA not Table B.
August 25, 2016 at 10:11 am
The order by column is in TableA as well. Think this is my issue.
August 25, 2016 at 10:55 am
Did you look at the code I posted?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2016 at 11:16 am
Luv SQL (8/25/2016)
I need to grab the sequence number from TableA not Table B.
The sequence number IS coming from TableA.
Part of the problem, is that you haven't been able to give a clear picture of what you want, because you're talking in generalities instead of posting something concrete like sample data and expected results. You can find out how to do this by reading the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply