dynamic-sql's questions - English 1answer

278 dynamic-sql questions.

How would one properly write a FUNCTION in postgres to return a TABLE based on a query where the query WHERE clause can have both an unknown number of filter conditions, and the conditions themselves ...

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...

I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more. We've done NO work concerning cursors/...

I have a general query I need to run for many tables SELECT sum(honoraria_amount) FROM table_1 The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39,...

I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database. ...

I am seeking to run a query to modify views across multiple databases (See the initial question here.) and was able to find how to get it. I've now run into the problem that if I run this from master ...

Right now I have to use query to get the command in text file. Then remove double quote from them. And finally, run that file in psql shell. How can drop all of my functions in PostgreSQL in a single ...

I have a configuration table: Table: refdatecol id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...

Environment SQL Server 2012 (11.0.2100.60) Situation user is allowed to execute a number of stored procedure; user is readonly denywrite one of stored procedure has an EXECUTE statement; The ...

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...

I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table. https://postgis.net/docs/ST_Intersection.html https://postgis.net/docs/ST_Intersects.html ...

Scenario: I have a table with 100M+ rows of data so I have created an index with something as: CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt ON dbo.base_voter (name_voter,...

I'm trying to write a Dynamic query to pass file name and table name to the procedure dynamically. what is wrong with this query? ALTER Procedure [dbo].[OpenExcel] @FileName nvarchar(2000), @...

We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How ...

I've a situation where I have two databases: ABCData ABCUsers One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded: INSERT ...

I just generated this script. It will select all columns from a table dynamically. Is there an alternative or more clean, efficient way of conducting this? Additionally, I want to place line breaks ...

Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values. CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int ...

I am trying to get information about the data in my database. I have several columns in several tables that or type 'TEXT'. I want to perform a query that will create a result that looks like: |...

Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists. So I created a script that calls a procedure that will save the contents of this table ...

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub stored procedure based on passed ...

I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works ...

I'm new to Postgres, so I'm sorry if this is overly simple. But I've searched for ages, and can't find a solution for my dynamic-sql problem. I have one table that contains the list of columns in a ...

Does SQL Buffer Pool size = Max server memory.? I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours. I have 128 GB Physical Memory. Earlier i ...

My database has the following tables: TAG ---------------------- | tag_id | tag_name | ---------------------- TAG_VALUE: Stores values associated to each tag ----------------...

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...

I have the following code: select [schema].[name] [schema], [table].[name] [table], [column].[name] [column], [type].[name] [type], [column].max_length [...

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...

Overview A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an ...

I've got a table that holds columns named "Day1" through to "Day31". Is there a way I can dynamically choose the correct column to put in a WHERE clause? I can get the current day, that's no ...

I have some problems in properly quoting a table name using the format function in the example below. CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text) RETURNS bool AS $...

I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below. I run the application and capture the query using profiler....

I have a table named transaction into which I want to insert 3 lakh (300'000) records. I need it to be created as dynamic query instead of writing thousands of insert statements. For example : ...

I have a function which returns newly inserted row count: BEGIN EXECUTE ( SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1', _tbl, ...

So I need to basically get the count of every table in a database daily and insert those counts into a separate database for analysis and reporting purposes. The table the counts need to be inserted ...

How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)? Example Table: create table ...

Can anyone Please help me to transpose below result +------+------------+-------+ | SVR | dates | count | +------+------------+-------+ | SRV1 | 03/25/2018 | 1 | | SRV1 | 04/06/2018 | 59 ...

I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier. It requires a linked Server. this is a partial view of the ...

I have a table in my db that stored both successful and failed log-in attempts. I am creating a stored procedure that allow us to delete records which are older than X days old. So far so good, but I ...

I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications. Every time I need to reinitialise those database I need to script ...

I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (...

I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "...

I have a table structured as follows: CREATE TABLE t ( HHID varchar2(1), INDIV_ID int, PRIM varchar2(1) ); insert into t values ('A', 10, 'Y'); insert into t values ('A',...

I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting. So, I have the ...

How would I quote the type name to protect against SQL Injection. For example, take this SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int'); ^ ...

Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran. sql server 2008R2 & 2012 ...

I've been stuck with this problem, so I'm looking for help now. I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to ...

The problem I have got an issue setting up Management Data Warehouse on SQL Server 2017 CU 5. The job "collection_set_1_noncached_collect_and_upload" keeps failing. It is related to the "Disk Usage" ...

I would like to find out from the code below, what was the name of the database where the last command was run. In this case I am looking for my_other_database. his there any way of finding this? Is ...

With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique). The Number column should be grouped and start a new section of ...

I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient ...

Related tags

Hot questions

Language

Popular Tags