Questions tagged [sql-server]
Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
4
votes
0answers
29 views
Safe dynamic SQL for generic search
Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
0
votes
1answer
16 views
T-SQL query Choose name of clients, concluded more than one loan agreement in 2010
QUESTION: Choose the name of clients,
concluded more than one loan agreement in 2010,
as the numbers and dates of issue of these contracts.
Sort by name of the client and date of issue of the loan.
...
0
votes
0answers
30 views
Parties and Messages in SQL e-Commerce db design
I'm posting a follow-up question based on my first question. My goal is to, as an exercise for myself, create a complex (in my opinion) e-commerce application. The explanation of the application can ...
2
votes
0answers
16 views
T-SQL Query remove duplicates except for original row from the table
Question:
There is an Exam_Merge table into which to import
records from a similarly structured table in another database.
In this case, the Exam_Merge table contains the values of the primary key ID
...
4
votes
1answer
34 views
JSON from SQL Server: Reduce lists of single dicts to list of values
SQL Server 2016 introduced some JSON features, but they're far from robust. As far as I can tell, there's no way yet to output a simple array; everything must be in ...
4
votes
1answer
75 views
SQL e-commerce database design
I want as an exercise to create an e-commerce application from bottom up. My main goal here is to gain a lot of knowledge. I have experience in web development, but never have been there from the ...
1
vote
1answer
47 views
Query to generate payroll based on attendance for a month
I have written a query to generate payroll based on attendance for a month, for about 4000 employees. It takes quite a long time to execute. Basically, what I am doing is to joining two different ...
1
vote
1answer
33 views
Insert multiple rows without using CURSOR SQL Server 2014
I have a fairly complex query which insert multiple rows into a table from multiple tables. Its works fine but I really don't want to use cursor as some blog says cursor took long time for execution (...
1
vote
0answers
22 views
Log table with a summary of claims
I'm writing a log table which contains the summary of the claims. The log table logs the data whenever there is an upload.
I have written the following SQL query. Though it serves the purpose, I'm ...
0
votes
0answers
24 views
Ensuring referential integrity in child, group relationship
I'm not going to lie - Sql Server is not in the top 10 of my skill set, but it's something that I have to do from time to time.
What's more, I'm using Entity Framework, so there is a workaround ...
2
votes
1answer
76 views
Web Server handler class to pass IIS http requests to SQL Server stored procedures
This is a c# class I use to implement web database applications with Microsoft IIS and Microsoft SQL server. It passes the http context to a selected SQL stored procedure, and writes a response based ...
1
vote
1answer
113 views
1
vote
1answer
40 views
Read comma separated values and insert in SQL Server 2016 table using String_Split
I know that this question has been asked many times over many years and I have read every one of them but I wanted to get a modern solution since software has changed and ...
3
votes
2answers
98 views
SQL Query - records within the last year
Bit of a complicated one for me.
I have a database full of hundreds of thousands of records, many of which are duplicated.
I need to get all records within the last year but making sure every ...
4
votes
0answers
74 views
Retrieving the IDs of entries to be deleted from a table
Business logic
It would take us too far to explain the whole business case behind this code, so I'll try to be as succinct as possible.
There are Persons,
and ...
0
votes
2answers
60 views
Bash Script for file auditing, push information to server and be able to view in a web page
Edit: The goal of this Question was only seeking code review and guidance in learning or tips for improvement. I apologize if that was not clear or aligned properly with the goals of this forum.
...
0
votes
0answers
19 views
Dynamic “compilation” of SQL stored procedures
I have a stored procedure which is called to create ( "compile" ) temporary stored procedures, code below, based on whether a flag in a database record is set - the various sub-procedures such as ...
0
votes
0answers
26 views
Tracking when a column was last modified using a trigger (in SQL Server)
How can I improve the following code to track when an Azure SQL Database column value was last modified?
...
2
votes
0answers
46 views
SQL Database for Visitor Registration System
This is intended for a web-based application that allows someone to register visitors, assign their hosts, and indicate the dates in which they will be visiting.
Imagine this scenario:
I want to ...
1
vote
1answer
60 views
Upload csv file to sqlserver with python
I have to weekly upload a csv file to sqlserver, and I do the job using python 3.
The problem is that it takes too long for the file to be uploaded (around 30 minutes), and the table has 49000 rows ...
2
votes
0answers
32 views
Get local datetime from UTC time
One of the app is inserting date in UTC format (column CreateDate). When reporting from this table, I used a difference in hours between the inbuilt ...
0
votes
1answer
89 views
Modify a column in 2 tables based on trigger
This is SQL Server 2008r2. First, here are some tables and data for test:
...
1
vote
0answers
21 views
Calculate BACKLOG_M_1
I need to calculate the BLACKLOG_M value corresponding to the Snapdate of the first of the month. It becomes a constant value repeated for each line of the month
As it is explained in the picture:
<...
1
vote
0answers
120 views
Syncing a table to a remote database
I want to sync the table every few minutes to a remote database. I want it to be as efficient as possible. I wrote this stored procedure that will only return rows that are added or removed after the ...
0
votes
1answer
45 views
Validating region/state/province codes for different countries
I have a large table in which I store region/state/province codes (two letters) from different countries. I use these region codes further downstream for multiple process. One of the steps I do is a ...
0
votes
1answer
37 views
Quickly check whether file name is in column in an SQL Table and move to new folder
I have a table that in one of the columns exists a list of file names. The names comes from other tables and the files in a folder.
Not all the files are used, may be attached to parcels that no ...
3
votes
1answer
121 views
SQL SP creating certificates for shipping
We ship large spools of copper wire and pallets of copper rod that have to meet specific chemical and mechanical requirements, this is what must be put on the certifications. I created an SP which ...
1
vote
0answers
21 views
Activity grouping and selection according to order and completion requirements
The problem consists of two categories of events (Work and Rest) which together form a complete set.
The number of Work events is open-ended.
The number of Rest events is fixed at three (3).
The ...
0
votes
1answer
49 views
Updating 3 columns conditionally on a database table
I need to run this script to update the value of 3 columns based on certain conditions. Right now, this is what I have:
...
1
vote
1answer
44 views
Loop Through bitarray to retrieve subsets of that bitarray
Objective:
I want to loop through a bitarray and store subsets of that bitarray in a table.
Context:
I have a bitarray with 48 elements where each element represent one hour. I want to look back 24 ...
3
votes
1answer
164 views
Inserting data into SQL-Server table dynamically
I know I can do all this via SSMA quite quickly, but I wanted to see if I could do it efficiently in PowerShell myself as a little project.
Basically, the entire script takes tables from an Access ...
0
votes
1answer
82 views
SQL Stored Procedure to export specified data
I'm currently using a stored procedure(sproc) to export to Excel. The sproc is being passed ...
2
votes
0answers
67 views
Insert items from one table that do no exist in the other one [closed]
The insert query below must insert the items not present in the Articolo table by importing them from Importazione, and must update the items in the Articolo with those present in the Importazione. To ...
2
votes
0answers
66 views
One class for MySQL and MSSQL Queries
EDIT BELOW
I am trying to create resusable classes for MSSQL and MySQL queries (later also Update and Delete queries). I tried it doing the OOP-way. Here is my result:
Einstellungen.vb:
...
1
vote
0answers
153 views
Stored procedure that adds row-level auditing to a table
I'm looking for any suggestions on how to improve my T-SQL for this procedure. It works okay, but I'd like to get advice to improve it from more experienced SQL developers. This procedure adds 4 ...
1
vote
1answer
80 views
Calculate NPS in a stored procedure
We collect customer surveys and one of the questions is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but ...
0
votes
1answer
84 views
SQL Query to get parent parts of materials using Recursive CTE
The intent of the query is to filter out certain materials to get to a list that will then be run through a recursive CTE to drill up through the BOM to the 0 level assembly.
P_Parts_CTE is filtering ...
4
votes
1answer
98 views
Retrieving a product's sold quantity
I've written a view to retrieve each product's sold quantity and four week sales average by Branch/Route/...
2
votes
2answers
108 views
Stored procedure to implement a search filter
I've implemented a stored procedure for a search process SQL Server 2008. I'm not sure if everything I created is correct. Also I'm wondering if this code has any leaks or vulnerability on SQL ...
7
votes
2answers
97 views
Find how much reputation a user had on a given date
I saw this question on MSE and went ahead and wrote a solution to it:
https://meta.stackexchange.com/questions/313561/determining-users-reputation-as-of-particular-date
This calculates the ...
3
votes
3answers
349 views
Slow SQL Server performance with multiple PreparedStatement loop
I have a need to update thousands of database records at a time. I am using a loop to generate the necessary PreparedStatement updates, but am hoping for a more ...
2
votes
2answers
77 views
Replacing birth year column values with calculated age value
I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values. There are multiple steps like this and ...
2
votes
2answers
44 views
Query to find instances of two types of events occurring more than one second apart
I have 3 tables on my database: BusinessObject and CustomEvent, and EventType. ...
4
votes
2answers
33 views
Select recordIDs for Bill of Materials
I have a hierarchy of parts in my database. For example, one part (call it 0010102-0112-315) is a resistor. It's used in a billion different top-level assemblies. ...
3
votes
0answers
82 views
Reading Database, scraping Barchart and uploading prices to DB
This is my first real excursion into Python, coming from C#. I wanted to see if my code is well-formatted and efficient.
The following will read the needed codes from a Database Table, then submit ...
6
votes
0answers
110 views
Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance
I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and ...
1
vote
1answer
39 views
Ledger query using window functions
This is a ledger kind of table, initial amount is when the movement is null and all transactions on it is based on the Waste_Seq
the query take the last entry (id) based on the movement and change ...
5
votes
2answers
122 views
Combining SQL Tables into One Table in New DB with Similar Fields
I have a SQL Server Database filled with Reports all having the same schema.
What I've done is created a .NET application to automate the process of adding all reports to a new table.
How can the ...
1
vote
2answers
193 views
4
votes
0answers
1k views
Fastest way to insert rows in mysql vs sqlserver (large dataset)
I'm looking for any possible improvements that could be made to run the mysql piece of code faster.
I created a simple test winform application that creates two docker databases :
Sqlserver on linux ...
