T-SQL (SS2K8) :: Measuring Volume Of Data Created Temporarily To Replace Usage Of Physical Tables In Query

Sep 12, 2014

How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.

View 1 Replies


ADVERTISEMENT

Filter Dynamically Created Data Tables Using Sql Query

May 24, 2008

Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose,
I have four data tables with the same structure but records may be
different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I
want to that two records by filtering all tables and sum of rates and
want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance. 

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Compare Data In 2 Tables

Sep 17, 2015

Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.

View 4 Replies View Related

T-SQL (SS2K8) :: Select Query To Pull Required Data From 3 Tables

Mar 19, 2014

I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.

Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .

Example:

Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,

[Code] ....

Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .

My Final Output of select query should be like below.

EmpID Role
1 Role1
2 Role2
3 Role1

View 5 Replies View Related

SSIS To Replace Data Of Tables

Apr 29, 2008

I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.

View 3 Replies View Related

Get New Database Created Then Running Script To Created Tables / Relationships

Jun 29, 2015

trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. 

E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

View 3 Replies View Related

T-SQL (SS2K8) :: Outputting Sproc Result Set To A New Physical Table

Aug 3, 2015

I need to output a sproc into a new physical table, so the column definitions match the output.

Select Into DbName.NewTableName
Followed by an
Insert Into DbName.NewTableName
From (SprocNameHere),

View 9 Replies View Related

How To Replace Data Of Tables From Source To Destination Database Using SSIS

Apr 29, 2008

I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.

View 5 Replies View Related

Rollback Will Drop Created Tables And Drop Created Tables In Transaction..?

Dec 28, 1999

Hi folks.

Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2

then execute bellow statements
select * from t1
this query gives you table with out data

select * from t2
you will recieve an error that there is no object

but if you rollback
T1 willn't be there in the database

droped table t2 will come back please explain how it can happand.....................

Email Address:
myself@ramkistuff.8m.com

View 1 Replies View Related

MSSQL 2005 Inserting Data On Tables Created By Regular Users With Ddl_admin Role, Using Access Or Other Front End Apps

Feb 14, 2008

Writing to tables created by regular users on MSSQL2005

I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much

View 3 Replies View Related

T-SQL (SS2K8) :: Get Difference Between Data In Two Tables

May 27, 2014

I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:

CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,

[Code] ....

Output should be like this

PNR | TKT_NUMBER | DEPACITYNAME | ARRCITYCODE | NAME
fdf44 12669 Sah DXB lamees

View 8 Replies View Related

Write A Query To Replace Data From A Single Field

Feb 15, 2008

Hi I have a table named  UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out 

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Predict Future Job Schedules From MSDB Tables

May 8, 2014

I want to query my msdb job and jobschedule related tables to generate a list of runtimes for each of these jobs for the next day or any future date. This query should output JobID, Run_Date(YYYYMMDD), and Run_Time(HHMMSS).

If I have 3 jobs with...

Job#1 scheduled to run once every 4 hours between 6 AM and 10 PM
Job# 2 scheduled to run every 15 minutes between 11 AM and 1 PM
Job# 3 scheduled to run every minute between 4 PM and 4:15 PM

my output should look as below ....

View 1 Replies View Related

T-SQL (SS2K8) :: Union Of Two Tables Returned Back To Main Query

Aug 18, 2014

Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.

I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.

Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2

Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB

View 3 Replies View Related

T-SQL (SS2K8) :: INSERT Data From Multiple Tables

Dec 1, 2014

I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.

I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.

insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;

It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.

View 3 Replies View Related

Stored Procedure | Selects Some Data |query The Results It Created And Output

Dec 2, 2005

I can create 1 view of a database then use that views results to query

but i want do this with a stored procedure
pass some data to it, so it selects some data

query the results it created and output this data.

ie


pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output

)


select from a database with the firstpassed value

Select *
From TableName
Where ID = @FirstValue

Then
using the results from the above select

Select *
From theResultsOfAbove
Where ID = @SecondValue

any ideas would be fabo !

View 6 Replies View Related

T-SQL (SS2K8) :: Replace All New Line Characters

May 19, 2014

I am working with a large amount of text data in a single column. I am in the process of pumping that column of data into a delimited text file. The data in the column has new line characters that I need to remove. I need to data to be in a single line in the text file.

So far the things that I have tried are not working. What I try for removing the new line characters?

View 3 Replies View Related

T-SQL (SS2K8) :: Using Like In A Replace Cast Sentence?

Jul 16, 2014

CREATE TABLE [dbo].[instructions](
[site_no] [int] NOT NULL,
[instructions] [text] NULL
)
Select top 3 * from instructions

Output

Site_no Instructions
20 Request PIN#510 then proceed
21 Request PIN#987 if wrong request name
22 Request PIN#688 allowed to use only numbers

All text instructions start with “Request PIN#XXX” also after that the text are different for every site_no

I need insert in all site_no rows and after the “Request PIN#XXX” the text “and codeword” keeping the current rest of text

How can I set e REPLACE CAST sentence using something LIKE PIN%%%%

To get these type of results

site_no instructions
----------- ----------------------------------------------------
20 Request PIN#510 and codeword then proceed
21 Request PIN#987 and codeword if wrong request name
22 Request PIN#688 and codeword allowed to use only numbers

View 4 Replies View Related

T-SQL (SS2K8) :: Calculate On Newly Created Columns

Aug 2, 2014

I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns

I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.

Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.

SELECT
NAME = [LastName] + ',' + [FirstName]
,HireDate
,Title
,DepartmentName
,BaseRate
,VacationHours
,SickLeaveHours

[Code] ....

View 1 Replies View Related

High Volume Database Query Optimization

Dec 10, 2007

Hello every body

i am doing a research about high volume database treatment (maybe a database with tera bytes volume) , so is there any optimization or specialization for queries deal with such database? !!

View 5 Replies View Related

T-SQL (SS2K8) :: Enhanced Find And Replace Function

Apr 17, 2014

I'm looking for a way to pull off a complex find+replace within some code, as follows:

@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'

We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...

Rules:

1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).

2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).

View 2 Replies View Related

T-SQL (SS2K8) :: Replace A Special Character In Results?

Jun 11, 2014

I have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.

The table is holding the path like this "/folder/folder/file"

I am trying to convert that path to "folderfolderfile"

In my Select statement I have

SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah

The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.

View 2 Replies View Related

T-SQL (SS2K8) :: Replace String After Specific Index

Mar 23, 2015

I have data like below

Potter, James J
Williams, Ted R
Allen, Gary G

I want to remove Middle Name from the output

Potter, James
Williams, Ted
Allen, Gary

My Query:

SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d

However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"

Do we have any way to say sql to replace after certain index?

View 3 Replies View Related

T-SQL (SS2K8) :: Find And Replace Text For All Sprocs On A Server

Apr 24, 2014

There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )

View 1 Replies View Related

T-SQL (SS2K8) :: Replace Cursor - Convert To Recursive CTE Or While Loop

Jul 2, 2014

Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.

Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor

[Code] ....

View 9 Replies View Related

Handling High Volume Data

Apr 18, 2007

Hi Guys,

I am facing problems with concurrent access in SQL Server 2000,The scenario is that the DB contains one huge de-normalized table containing 40 million records.

The application frequently queries this table to populate other derived tables,the sql queries take a long time to return results.

So if one query is in execution the other user's query goes into a
wait mode.Please suggest how I can better this.

Or do I need to upgrade to 2005.

Regards,
Prashant

View 2 Replies View Related

Loading Huge Volume Data

May 31, 2007

Hi Good morning to all,



My day started with loading huge volume of data and my data flow task failed to do so.



My data flow has a flat file connected to a OLEDB target. This is a one to one mapping. My source file contains 50 lac records and it is of 500 MB in size.



I'm processing the data with all the default buffer settings. I have 4 CPUs in my server.



the system process DTSDebug.exe is utilizing more than 2GB page size. My average CPU usage being 70% when one of those CPU s is hitting 100% utilization.



I'm very new to SSIS. So, please provide me some info how do i set my buffers and do we have any PDF for performance and tuning in SSIS ?



Do we have any bulk load transformation in SSIS to load into DB2UDB ?



If so how do i get it installed?





Thanks in advance,

Suresh N

View 2 Replies View Related

Transaction/Data Volume Vs SQL Edition

Nov 15, 2007

I am in the process of choosing between either SQL Workgroup or Standard Edition. I see the differences in features on the comparison table, but do not see any references to the differing capabilities in handling transactions.

Is there any differences between Workgroup and Standard in terms of handling transaction/data capabilities? i.e. Does Standard have the superior capability in handling X times more TPMs than Workgroup?

If not, am I correct to assume that this is totally determined by hardware configuration (# of CPUs, processor speed, HD speed, RAM) ?

If the data volume / transactions handling is solely determined by hardware configuration, and I know the # of transactions and amount of R/W per second, .......where would be a good reference to know what kind of hardware configuration I need (ideally, once I know the hardware configuration, I guess I would be able to determine I need Workgroup or Standard)

Thanks in advance,
benbry

View 3 Replies View Related

SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?

Jan 30, 2014

providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...

I want to create a job which will run on a Node every 15 min and store data in a table for each instance...

DMV is not giving more stuff and xtended events not sure if i can store that data into a table?

View 7 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Characters With Single Character In A Column?

Jun 21, 2012

I am trying to replace all special characters in a column with one special character.

Example:

Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"

I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.

I am expecting the following results:

Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<

View 9 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Characters Based On Table Values

Dec 12, 2014

There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]

[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000

For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400

The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...

DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';

SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]

SET @Total='select '+@formula

EXECUTE sp_executesql @Total;
PRINT @Total;

View 3 Replies View Related

Solution Design For High Volume Of Data

Apr 18, 2006

Hi,

I have been asked to design a solution for a client of mine who basically requires the daily analysis and reconciliation of the differences between 2 extremely large text files.

The files are not in an identical format but are both in some form of delimited format (one is CSV, the other is a little more complex). For the sake of this question, let's assume that I can effectively import each file into an MS SQL table.

Each file will have in excess of 100,000 rows each day (new data for each day).

Whilst I know that MS SQL does easily have the capacity to store the data, is there a recommended way to tackle the potential problems (I imagine that performance is important... they will be running the report every day)

Or is building the solution as simple as importing the data into 2 tables, and then querying the differences and outputting as a report using Crystal?

Any suggestions appreciated.

Thanks

Rael

View 10 Replies View Related

Huge Volume Of Data Loading Issue

Aug 21, 2007

Hi all,

I've faced a problem with the below error when I load 1.5m data into oracle database.


The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers.

Please help. Thanks

View 19 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved