TSQL - Identify the table name as a column of the single or multiple tables in a select statement WITHOUT HARDCODING it as part of the query
I need to display the table name in my select statement.
select col1, table_name_1 as table_name from table_name_1
Which I insert into another table.
My problem is I have to do this dynamically not as explained by this question.
Not sure if this available with a single key word like for an eg. the currentdate we could use something like GETDATE().
Thank you for your answer.
I don't think there is inbuilt function available in T-SQL other than
OBJECT_NAME (object_id('TableName'))which required to hard-code Table name.
However, as workaround, following query could fulfill your requirements:
Declare @tbname varchar(50); Declare @SelectQuery varchar(2000); set @tbname = 'YourTable'; set @SelectQuery = 'select *, OBJECT_NAME (object_id(' + '''' + @tbname + '''' +')) from ' + @tbname; --print @SelectQuery; exec (@SelectQuery);
See also questions close to this topic
Most elegant way to eliminate equal but opposite data using SQL
I have a relatively simple set of data that looks like this:
invoice_id created_at amount_in_cents user_id 22348 2019-11-07 550 31773927 22349 2019-11-08 -550 31773927 22498 2019-11-10 -3400 2389483 22499 2019-11-10 3400 2389483 22500 2019-11-11 18000 93842938
As you can see, the first two rows of the sample data are attributed to the same user_id, but are of inverse amounts (add up to 0). Same with rows 3 and 4. I want to remove all invoices where there is an inverse invoice for the same user, within 30 days of each other, leaving just the fifth row.
I could do this with python, but it would expand the process a lot. Is there a simple way to do this with SQL?
How to match entires in SQL based on their ending letter?
So I'm trying to match entries in two databases so in the new table the row is comprised of two words that end in the same ending letter. I'm working with two tables that have one column in each of them, each named word. table 1 contains the following data in order:
Dog, High, It, Weeks, while table two contains the data:
Bat, Is, Laugh, Sing. I need to select from both of these tables and match the words so that each row is as follows:
Dog | Sing, High | Laugh, It | Bat, Weeks | Is
The screenshot is what I have so far for my SQL statement. I'm still early on in learning SQL so any info to help on this would be appreciated.
MariaDB query issue | integer comparison not working as expected | character encoding issue?
I'm trying to get results of a query from schools that were in a particular region over time. I've created the queries that I think should do this -
SELECT * FROM team t1 JOIN district_team t2 ON t1.school_name = t2.team_name join district t3 ON t2.district_num = t3.districut_number JOIN region_district t4 ON (t2.season = t4.season AND t3.districut_number = t4.district_num AND t4.region_num = 3); SELECT result.school_name, result.region_num, result.season FROM (SELECT * from team t1 JOIN district_team t2 ON t1.school_name = t2.team_name join district t3 ON t2.district_num = t3.districut_number NATURAL JOIN region_district t4)result WHERE result.region_num = 3;
--- Schema of tables in query ---
CREATE TABLE `team` ( `school_name` VARCHAR(50) NOT NULL, `mascot_name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`school_name`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; CREATE TABLE `district_team` ( `district_num` INT(11) NOT NULL, `team_name` VARCHAR(50) NOT NULL DEFAULT '', `season` INT(11) NOT NULL, INDEX `district_fk` (`district_num`), INDEX `team_district_fk` (`team_name`), CONSTRAINT `district_fk` FOREIGN KEY (`district_num`) REFERENCES `district` (`districut_number`), CONSTRAINT `team_district_fk` FOREIGN KEY (`team_name`) REFERENCES `team` (`school_name`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; CREATE TABLE `district` ( `districut_number` INT(11) NOT NULL, PRIMARY KEY (`districut_number`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; CREATE TABLE `region_district` ( `district_num` INT(11) NOT NULL, `region_num` INT(11) NOT NULL, `season` INT(11) NULL DEFAULT NULL, INDEX `region_fk` (`region_num`), INDEX `district_region_fk` (`district_num`), CONSTRAINT `district_region_fk` FOREIGN KEY (`district_num`) REFERENCES `district` (`districut_number`), CONSTRAINT `region_fk` FOREIGN KEY (`region_num`) REFERENCES `region` (`region_number`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ;
Does anyone know why this would be the case or what's going on here? Could this be a character encoding issue?
The Query works when I tell the table which index to use or to ignore the index. So these queries give the expected output --
SELECT * FROM team t1 JOIN district_team t2 ON t1.school_name = t2.team_name join district t3 ON t2.district_num = t3.districut_number JOIN region_district t4 IGNORE INDEX(region_fk) ON (t2.season = t4.season AND t3.districut_number = t4.district_num AND t4.region_num = 3); SELECT * FROM team t1 JOIN district_team t2 ON t1.school_name = t2.team_name join district t3 ON t2.district_num = t3.districut_number JOIN region_district t4 use INDEX(region_fk) ON (t2.season = t4.season AND t3.districut_number = t4.district_num AND t4.region_num = 3);
But this query doesn't
SELECT * FROM team t1 JOIN district_team t2 ON t1.school_name = t2.team_name join district t3 ON t2.district_num = t3.districut_number JOIN region_district t4 ON (t2.season = t4.season AND t3.districut_number = t4.district_num AND t4.region_num = 3);
Does anyone know why this would occur?
Is storing time periods as unit and quantity bad form?
I am currently working on some code that lets the user set some actions to be done on regular intervals. For example, a user asks to run a report and send them the results every 2 weeks.
Currently I am storing that request in SQL Server as a row in a table, with two columns dedicated to the interval the user specified. One column holds the date-unit ('Hours', 'Days', 'Weeks', 'Months', 'Quarters', or 'Years'), and then the other holds the quantity. I'm doing this so I can best utilize
DATEDIFF()for determining when the action should be run again, rather than handle that all myself.
However, I've been reading that it's generally bad form to split up the units and the value like this into different columns.
Is there a different way I can be storing these 'semantic' time periods so that I'm not splitting up the units and the value? Or am I just being too adherent to the rule of thumb, and this is one of the case where splitting up the units and the value works best?
Cannot query a view
I have a database
A. When I query a view
select * from vA
I receive this error
Database 'B' cannot be opened. It is in the middle of a restore.
However, when I query content of a view (
sp_helptext), it returns results without issue.
When I query above query in database
C, it runs without errors too.
NOTE I have restored database
Bpreviously and later deleted it. All above databases are in same server. I think it is a Microsoft bug, what do you think?
JMETER com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set
I am sending the following query on JMeter. After sending it Job is created and it sends an email as it does in SQL server management studio but JMeter gives an error and the test doesn't pass even though the query is working.
JMETER com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set
Exec msdb.dbo.sp_send_dbmail @recipients = 'Tidal-Job-Scheduler@company.com' ,@from_address = 'email@example.com' ,@blind_copy_recipients = 'firstname.lastname@example.org' ,@subject = 'Start ' ,@body = 'TEXT' ,@body_format = 'TEXT'
Select the oldest date for a row and create a new column with values based on the oldest date or other
Firstly to explain what the select is doing currently.
Select reference, status, Valid, createDate, createdBy FROM ( select 1 as value, 'reference' reference, 'status' status, 'Valid' Valid, 'createDate' createDate, 'createdBy' createdBy UNION ALL SELECT 2 as value, l.reference, case l.number when 0 then 'not sent' else 'sent' end status, l.Valid, l.CreateDate, l.CreatedBy Convert(varchar 10), l.CreateDate, 111)CreateDate From dbo.log l where l.CreateDate > DATEADD(HOUR, -24, GETDATE()) ) x ORDER BY x.CreateDate DESC
The purpose of this data is to be used by another procedure to generate a file.
Hence why I have printed the column headers twice.
My issue is the following:
- I need to count all the duplicate reference's from the select
- I need a new column called 'SendMethod'
- From those duplicate references I then need to know what row has the oldest CreateDate
- Based on that I then need this new column to have initial send if it's the oldest CreateDate out of those duplicate rows else it needs to have subsequent send
I need this new column to be part of the select.
So that it produces all of the data at once and not through multiple result sets.
Please see below example of how this would look with this new column included just with some dummy data.
Reference Status Valid CreateDate CreatedBy SendMethod Reference Status Valid CreateDate CreatedBy SendMethod 1 Not sent 0 21/01/2020 12:00 John initial send 2 Not sent 0 21/01/2020 12:03 Bob initial send 3 Not sent 0 21/01/2020 12:05 Bob initial send 4 Sent 1 19/01/2020 12:00 Jane initial send 4 Sent 1 20/01/2020 12:00 Jane subsequent send 4 Sent 1 21/01/2020 12:00 Jane subsequent send
Comparing SQL date ranges across multiple nullable date columns
I have a calendar type check I'm trying to do on SQL Server. For each month of the year, I need to check if the employee was hired or not. There can be an original hire date, a rehire date, a termination date, and the last termination date; other than the original hire date, which will always have a value, all of these date fields can be null.
Given the following data:
EmpID OrigHireDate TermDate LastTermDate RehireDate 42 2017-09-25 NULL 2019-03-26 2019-10-30
What I am trying to achieve is the following result for each month for last year (i.e. 2019) and having no luck in coming up with the right statement. Assume I already have a table containing each month's number along with the start/end date of the month that I can use to compare the date ranges.
EmpID Month EmployeeDuring 42 1 True 42 2 True 42 3 True 42 4 False 42 5 False 42 6 False 42 7 False 42 8 False 42 9 False 42 10 True 42 11 True 42 12 True
- How to remove an unknown characters in the T-SQL