|
Well, I just noticed that my database was in Singlr User Mode. I set it back to Multi User and the problem no longer seems to appear.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Is there a way to split data into two separate tables with a single SQL statement? I know that sounds like an odd question, but I have a large table in terms of rows, and I would like to "split" the data into two separate tables for separate processing in parallel fashion. Call me crazy but I thought there was a way to do that in SQL Server, i.e. a SQL statement to divide the table into two separate tables.
Is there a way to do that or am I misremembering?
|
|
|
|
|
Take a look at partitioning although I would be wary of physically splitting a table simply for parallel processing, there are probably better ways of doing the job.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Just run two selects ("in one statement"). Select "top half" (count / 2) in one case, select the rest for the other (not in; greater than; whichever).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Why not implement it in a procedure?
|
|
|
|
|
Hello:
I need help with this:
SELECT *
FROM
(SELECT
CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END as Quarter
FROM viewSalesTracker_Model1
WHERE Quarter = @Quarter
The error I am receiving is:
Error Source: .Net SqlClient Data Provider
Error Message: Must declare the scalar variable "@Quarter".
|
|
|
|
|
It means that the compiler does not know what @Quarter is. You have not declared it anywhere.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Michele Smith wrote: Error Source: .Net SqlClient Data Provider
Error Message: Must declare the scalar variable "@Quarter".
Then declare this "@Quarter"variable!
|
|
|
|
|
Once you've declared the @Quarter variable or parameter, you'll run into another error:
Invalid column name 'Quarter'.
You can't reference an alias introduced in the SELECT clause within the WHERE clause of the same statement. You either have to use a sub-query, a common table expression, or duplicate the logic of the alias in the WHERE clause.
SELECT Quarter
FROM
(
SELECT CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
)
WHERE Quarter = @Quarter; Or:
WITH cteQuarters As
(
SELECT CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
)
SELECT Quarter
FROM cteQuarters
WHERE Quarter = @Quarter; Or:
SELECT CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
WHERE CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END = @Quarter;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I cannot figure out why my trigger keeps inserting duplicates. Do anyone see what might might be causing it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[update_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER INSERT , UPDATE, Delete
AS
BEGIN
Declare @new_agentId as uniqueidentifier --varchar(10)
Declare @new_email as varchar(50)
Declare @agentid as varchar(10)
Select @agentid = SO.new_agentId, @new_email = new_email
FROM inserted SE join import_new_serviceorganization SO ON SE.new_agentId = SO.ID
join app_users a on a.user_name = 'SO-' + SO.new_agentid
IF @agentid is not null
Begin
INSERT INTO EE_SO_MAILING_LISTS ([PARTNER], EMAIL_ADDRESS,STATEMENT_FLAG, REFRESH_DATE)
Values(@agentid, @new_email, 'N', GetDate())
END
Else if exists (Select * from deleted)
Begin
Select @new_agentId = new_agentId, @new_email = new_email
FROM deleted
Select @agentid =new_agentId From import_new_serviceorganization Where id = @new_agentId
Delete from EE_SO_MAILING_LISTS where PARTNER = @agentid and EMAIL_ADDRESS = @new_email
End
END
|
|
|
|
|
You can't treat triggers like this. Triggers will often have multiple records in the inserted and deleted tables so you can't use variables.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
How are you suppose to do them?
|
|
|
|
|
Depends on what you were trying to do but using variables will never work when there’s more than one record in the inserted or deleted tables.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
As well as the issue of multiple affected records, your trigger is trying to handle all three DML operations (INSERT , UPDATE , and DELETE ). But the code only seems to be handling the INSERT and DELETE cases.
- For
INSERT , there will be records in the inserted virtual table, but none in the deleted virtual table; - For
DELETE , there will records in deleted , but none in inserted ; - For
UPDATE , there will be records in both;
Use the inserted and deleted Tables - SQL Server | Microsoft Docs[^]
It would almost certainly be simpler to have separate queries for each operation. Try something like this:
CREATE OR ALTER TRIGGER [dbo].[insert_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.EE_SO_MAILING_LISTS
(
[PARTNER],
EMAIL_ADDRESS,
STATEMENT_FLAG,
REFRESH_DATE
)
SELECT
SO.new_agentId,
I.new_email,
'N',
GetDate()
FROM
inserted As I
INNER JOIN dbo.import_new_serviceorganization As SO
ON SO.ID = I.new_agentId
WHERE
Exists
(
SELECT 1
FROM dbo.app_users As A
WHERE A.user_name = 'SO-' + SO.new_agentId
)
;
END
GO
CREATE OR ALTER TRIGGER [dbo].[update_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
If Updated(new_email)
BEGIN
UPDATE
L
SET
EMAIL_ADDRESS = I.new_email
FROM
dbo.import_new_serviceorganization As SO
INNER JOIN inserted As I
ON I.new_agentId = SO.ID
INNER JOIN deleted As D
ON D.new_agentId = SO.ID
INNER JOIN dbo.EE_SO_MAILING_LISTS As L
ON L.PARTNER = SO.new_agentId
And L.EMAIL_ADDRESS = D.new_email
;
END;
END
GO
CREATE OR ALTER TRIGGER [dbo].[delete_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE
FROM
L
FROM
dbo.EE_SO_MAILING_LISTS As L
WHERE
Exists
(
SELECT 1
FROM dbo.import_new_serviceorganization As SO
INNER JOIN deleted As D
ON D.new_agentId = SO.ID
WHERE SO.new_agentId = L.PARTNER
And D.new_email = L.EMAIL_ADDRESS
)
;
END
GO
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
IMHO you are creating a nightmare for your successor by implementing business rules in a trigger. He/she is going to ask where the hell are all these emails coming from? And will spend the next #n hours/days searching for the code that generates the emails.
When he/she does eventually think to look into triggers they will heap curses on your name. Triggers should only be used for audit purposes not business rules.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hi,
I need to insert all the records from an excel sheet to a sql database table and find out below through sql queries.Excel is attached for reference below
device_id device_name appanalyticssessioneventid app_name category local_time
1223593358dfa0030bfb23209fe2c93b8fe0f0ac2af363b9760e84d0ff2e926c Galaxy S9 Plus 1223593358dfa0030bfb23209fe2c93b8fe0f0ac2af363b9760e84d0ff2e926c:2018-04-20T15:15:37.139UTC:2018-04-20T10:11:14.000 -0500 Robinhood Finance 4-20-2018 10:12:53
1cab04b741fa186ddeb5145df7905eb918ad45a09638a3faac0a2c30d60c38bc Galaxy S9 1cab04b741fa186ddeb5145df7905eb918ad45a09638a3faac0a2c30d60c38bc:2018-04-20T23:53:21.784UTC:2018-04-20T18:17:56.000 -0500 KNOX Configure Productivity 4-20-2018 17:53:02
102151dd863d3ac3f70ec3ed9aa105a847ebf02574300446911d567aa9f713f4 Galaxy S9 Plus 102151dd863d3ac3f70ec3ed9aa105a847ebf02574300446911d567aa9f713f4:2018-05-15T06:29:19.679UTC:2018-05-14T21:33:41.000 -0700 YouTube Entertainment 5-14-2018 21:45:19
[edit]
Removed thousands of lines of the same text
[/edit]
a)Daily avg of app session count per device id
b)How many times on an average user login to facebook app weekly
Thanks,
Barath
modified 30-Mar-20 8:37am.
|
|
|
|
|
Are you insane!!!?
You posted thousands of line of gibberish and then expected someone else to do all the work for you?!! Come on man!!
Provide you credit card number and I'll gladly do this for you.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
1. Thank you for cleaning up your question.
2. What exactly do you want us to do? You have several different requirements. You need to break them down into tasks and do each one.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
You have just identified the groupings for your query.
a) select the fields, group by date, appID, deviceID and do a count.
b) identify the facebook appid, repeat A with a where clause filtering the appid.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have a survey table where people were asked up 7 question and I'm trying to figure out the best way to assign a primary key in sql. here how it looks like.
key surveyid questionid answid questions answer
ed45894 1 3 1 when was the last time you bought soda 1 week ago
ed45894 1 7 2 How many live in your house 4
ed45894 1 4 5 do you children in your house Yes
ed45894 1 2 7 do you use coupon while shopping No
fs45895 1 3 4 when was the last time you bought soda yesterday
fs45895 1 7 2 How many live in your house 1
fs45895 1 4 5 do you children in your house Yes
fs45895 1 2 7 do you use coupon while shopping No
|
|
|
|
|
You're highly unlikely to overrun an int for surveys.
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
|
|
|
|
|
learning_new wrote: trying to figure out the best way to assign a primary key in sql There is only one way to do so; you find the column or columns that uniquely identify a single row in the collection.
If you are having trouble with it, you're not normalizing. Your table should be in 3NF; or in short, you want the key, the whole key, and nothing but the key. Codd's law, look it up.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Can you explain what these columns are ?
Looks like you have enough data to make a primary key, but understanding the data would lead to a suggestion.
|
|
|
|
|
Thanks David. I was able to figure out.
|
|
|
|
|
Say I have this TSQL table
create table Table (
TableId int identity(1, 1) not null,
value nvarchar(max) null,
id0 int null,
id1 int null,
id2 int null
)
and say I make a query like (getting the biggest id2 for a given id0 and id1 )
declare @arg0 int = 2
declare @arg1 int = 3
select top 1 id2
from Table
where id0 = @arg0 and id1 = @arg1
order by id2 desc
What would be the better index Index1 , or Index2 ?
create index Index1 on Table (id2) include (id0, id1)
create index index2 on Table (id0, id1, id2)
modified 5-Feb-20 20:52pm.
|
|
|
|