August 16, 2016 at 11:35 am
I have this script below in stored proc. I am manually entering the manual entry before running the stored procedure, those are dates from 2 days before and it needs to be in that format mmddyyyy
How to make this happen?
begin tran
MERGE PAS T
USING (SELECT * FROM PP where Tran='U')as S
ON(T.CAR=S.Car and T.NUMBER=S.Number)
WHEN NOT MATCHED THEN
INSERT
(
NUMBER
Last_file_DT
)
VALUES(
'08142016' --- manual entry
)
WHEN MATCHED THEN UPDATE SET
T.UMBER= S.Number,
T.Last_fileDT='08142016' ---manual entry
commit
August 16, 2016 at 12:40 pm
PJ_SQL (8/16/2016)
I have this script below in stored proc. I am manually entering the manual entry before running the stored procedure, those are dates from 2 days before and it needs to be in that format mmddyyyyHow to make this happen?
begin tran
MERGE PAS T
USING (SELECT * FROM PP where Tran='U')as S
ON(T.CAR=S.Car and T.NUMBER=S.Number)
WHEN NOT MATCHED THEN
INSERT
(
NUMBER
Last_file_DT
)
VALUES(
'08142016' --- manual entry
)
WHEN MATCHED THEN UPDATE SET
T.UMBER= S.Number,
T.Last_fileDT='08142016' ---manual entry
commit
How to make what happen? And why oh why are you not storing dates in a date or datetime datatype?
_______________________________________________________________
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 16, 2016 at 12:42 pm
This table was already created and I am not sure if we can change the datatype now.
August 16, 2016 at 12:44 pm
PJ_SQL (8/16/2016)
This table was already created and I am not sure if we can change the datatype now.
I understand. But still the question remains....how to make what happen? It is not at all clear what your original question is.
_______________________________________________________________
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 16, 2016 at 12:53 pm
It is inserting value in PAS table if the conditions matches or else updating values.
But the value for last_update_DT in stored proc is entered manually 08142016
even though when it matches or not.
August 16, 2016 at 1:14 pm
PJ_SQL (8/16/2016)
It is inserting value in PAS table if the conditions matches or else updating values.But the value for last_update_DT in stored proc is entered manually 08142016
even though when it matches or not.
Huh??? Is the date supposed to be part of the matching condition? I am sure this is clear to you but on this side of the screen it doesn't make sense (at least not to me).
_______________________________________________________________
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 16, 2016 at 1:49 pm
Yes, the date field needs to be updated as well if other record matches
August 16, 2016 at 1:59 pm
PJ_SQL (8/16/2016)
Yes, the date field needs to be updated as well if other record matches
Here is the challenge. I can't see your screen, I have no idea what your table structure or what you are actually trying to do. All I have to work with at this point is a pretty vague description of the problem. I am happy to help but I just don't understand what you are trying to do here. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
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 16, 2016 at 2:21 pm
Will this help?
CREATE TABLE [dbo].[PP](
[PP_NUMBER] [varchar](10) NOT NULL,
[PP_CAR] [varchar](2) NOT NULL,
[PP_Tran] [varchar](1) NULL,
[PP_Column 15] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PAS](
[PAS_NUMBER] [varchar](10) NOT NULL,
[PAS_CAR] [varchar](2) NOT NULL,
[PAS_Last_file_DT] [varchar](10) NULL
) ON [PRIMARY]
begin tran
MERGE PAS as T
USING (SELECT * FROM PP where Tran='U')as S
ON(T.PAS_CAR=S.PP_Car and T.PAS_NUMBER=S.PP_Number)
WHEN NOT MATCHED THEN
INSERT
(
PAS_NUMBER,
PAS_CAR,
PAS_Last_file_DT
)
VALUES(
PP_NUMBER,
PP_CAR,
'08142016' --- manual entry
)
WHEN MATCHED THEN UPDATE SET
T.PAS_NUMBER= S.PP_Number,
T.PAS_CAR =S.PP_CAR,
T.PAS_Last_fileDT='08142016' ---manual entry
commit
August 16, 2016 at 3:13 pm
PJ_SQL (8/16/2016)
Will this help?CREATE TABLE [dbo].[PP](
[PP_NUMBER] [varchar](10) NOT NULL,
[PP_CAR] [varchar](2) NOT NULL,
[PP_Tran] [varchar](1) NULL,
[PP_Column 15] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PAS](
[PAS_NUMBER] [varchar](10) NOT NULL,
[PAS_CAR] [varchar](2) NOT NULL,
[PAS_Last_file_DT] [varchar](10) NULL
) ON [PRIMARY]
begin tran
MERGE PAS as T
USING (SELECT * FROM PP where Tran='U')as S
ON(T.PAS_CAR=S.PP_Car and T.PAS_NUMBER=S.PP_Number)
WHEN NOT MATCHED THEN
INSERT
(
PAS_NUMBER,
PAS_CAR,
PAS_Last_file_DT
)
VALUES(
PP_NUMBER,
PP_CAR,
'08142016' --- manual entry
)
WHEN MATCHED THEN UPDATE SET
T.PAS_NUMBER= S.PP_Number,
T.PAS_CAR =S.PP_CAR,
T.PAS_Last_fileDT='08142016' ---manual entry
commit
This is a great start. But the primary question is still...what do you want to happen. What is it you are trying to accomplish here? You have a table and some code. How about a couple rows of sample data and an explanation of what you expect to happen. Honestly I am still trying to figure out what you are asking.
_______________________________________________________________
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 16, 2016 at 3:53 pm
Here's the one with create, insert and merge
So, during merge I would need "Last_update_file_DT " either in insert or update to be date 2 days before (for today's e.g: 08142016 is two days before).
That is what I am trying to get instead of having to enter the date manually.
CREATE TABLE [dbo].[Processed](
[DL_ID_NUMBER] [varchar](10) NOT NULL,
[LAST_NAME] [varchar](40) NULL,
[FIRST_NAME] [varchar](40) NULL,
[CARD_TYPE] [varchar](2) NOT NULL,
[Last_update_file_DT] [varchar](10) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[WEEKLYUPDATE](
[Tran] [varchar](1) NULL,
[Card_Type] [varchar](2) NULL,
[DL_Number] [varchar](10) NULL,
[Last_Name] [varchar](40) NULL,
[First_Name] [varchar](40) NULL,
[Issue_Date] [varchar](8) NULL,
[Column 15] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO [Test].[dbo].[WEEKLYUPDATE]
([Tran]
,[Card_Type]
,[DL_Number]
,[Last_Name]
,[First_Name]
,[Issue_Date]
,[Column 15])
VALUES
('U','DL','0000920591','Test','Test','08082016',''),
('U','DL','0000020591','Test_1','Test_1','08072016',''),
('U','DL','0000920581','Test_2','Test_2','08082016','')
GO
INSERT INTO [Test].[dbo].[Processed]
([DL_ID_NUMBER]
,[LAST_NAME]
,[FIRST_NAME]
,[CARD_TYPE]
,[Last_update_file_DT])
VALUES
('0000000001','TEst01','test00','DL','08092014'),
('0000000002','TEst02','test02','DL','08102014'),
('0000000003','TEst02','test02','DL','08112014'),
('0000920581','A','B','DL','09182015')
GO
begin tran
MERGE [Processed] AS T
USING (SELECT * FROM WEEKLYUPDATE where [Tran]='U')as S
ON(T.CARD_TYPE=S.Card_Type and T.DL_ID_NUMBER=S.DL_Number)
WHEN NOT MATCHED THEN
INSERT
(
DL_ID_NUMBER,
LAST_NAME,
FIRST_NAME,
CARD_TYPE,
Last_update_file_DT
)
VALUES(
DL_Number,
Last_Name,
First_Name,
Card_Type,
'08142016'
)
WHEN MATCHED THEN UPDATE SET
T.DL_ID_NUMBER= S.DL_Number,
T.LAST_NAME= S.Last_Name,
T.FIRST_NAME= S.First_Name,
T.CARD_TYPE= S.Card_Type,
T.Last_update_file_DT='08142016'
;
Commit
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply