Transact SQL :: Complex Query And Insert Data

Sep 1, 2015

I have three tables and all three are linked. Looking for query so that I can get the desired result. Notes for Table_A and Table_B:

ROW Data are given in the variables to insert the Row data. If these Row data are already exist with the exactly same sequence in the row of table then don't need to INSERT data again.If these variable date doen't exist then need to add this row.

Notes for Table_C:

Seq_id_Table_A is a Seq_id of #table_A. Seq_id_Table_B is a Seq_id of #table_B.

--Table_A----------------------Variables for Table_A--------------------
Declare @table_A_Y char(4)='TRC'
Declare @table_A_Y1 char(2)='1'

[code]...

View 6 Replies


ADVERTISEMENT

If Else Query For A Complex Data Set

Jan 4, 2007

I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure). 
 
Basically, a customer purchases a particular house, with a particular elevation, in a particular community.  This data is stored in a Customer table. From the customer’s selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.
 
A customer’s data looks similar to this in the customer table:
 





CustomerID

CommunityID

Community Name

PlanID

Plan Name

ElevationID

ElevationName


1234567

7

Hickory Hills

25

Allen

3

C
 
The Optionmaster table is structured like this (there are actually about 1000 records):
 





Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price


4567

Optional Window

0

+

0

+

0

+

250


1234

Optional Door

0

+

0

+

0

+

100


1234

Optional Door

7

Hickory Hills

0

+

0

+

0


4567

Optional Brick

0

+

25

Allen

0

+

250


9101

Optional Dormer

0

+

25

Allen

2

B

50


9125

Optional Tub

8

Smithville

0

+

0

+

800


9125

Optional Kitchen

0

+

0

Lori

0

+

2500
 
 
 





First. Based on the customer’s table I need to first select all options = to their community, as well as those available to communities everywhere (+).  In some cases the same option is maintained for both at both levels.  In that case the community specific option must be selected. 
 
To do this I think I need an if else statement to select those records:
 
In this case, if CommID =7, select record, else select 0.  This eliminates the other Smithville community and pulls the correct option # 1234.
 
The result would look like this:


 

 

 

 

 

 

 

 

 


Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price


4567

Optional Window

0

+

0

+

0

+

250


1234

Optional Door

7

Hickory Hills

0

+

0

+

0


4567

Optional Brick

0

+

25

Allen

0

+

250


9101

Optinal Dormer

0

+

25

Allen

2

B

50


9125

Optinal Kitchen

0

+

0

Lori

0

+

2500


 

 

 

 

 

 

 

 

 


Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+).  So, the Lori Plan data goes away.
 
Something like,  If the plan ID = 25, select record, else 0.  the result would be:


 

 

 

 

 

 

 

 

 


Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price


4567

Optional Window

0

+

0

+

0

+

250


1234

Optional Door

7

Hickory Hills

0

+

0

+

0


4567

Optional Brick

0

+

25

Allen

0

+

250


9101

Optinal Dormer

0

+

25

Allen

2

B

50


 

 

 

 

 

 

 

 

 


Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that  plan (+). 
 
Something like this: If the Elevation ID = 2, select record, else. 0.  Note the elevation B data goes away.
 
The final result would be for this customer is:


 

 

 

 

 

 

 

 

 


Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price


4567

Optional Window

0

+

0

+

0

+

250


1234

Optional Door

7

Hickory Hills

0

+

0

+

0


4567

Optional Brick

0

+

25

Allen

0

+

250
 
Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.
 
 

View 5 Replies View Related

Transact SQL :: Query Is Trying To Insert Duplicate Record?

May 18, 2015

My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.

-- Inserting new preferred supplier into R5CATALOGUE

DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG,
CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)

[code]....

View 5 Replies View Related

Single Complex INSERT Or INSERT Plus UPDATE

Jul 23, 2005

Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View 4 Replies View Related

Transact SQL :: Query To Insert Over 1000,0000 Record

May 8, 2015

How to write a query that can insert over 1000,0000 dummy records in the fastest way? My current query is

  DECLARE @d DateTIme = GETDATE()
  DECLARE @c INT = 1
WHILE @c <= 5 BEGIN
INSERT INTO MyTable VALUES (NEWID(),'PREFIX' + RIGHT('0000000000'+ (CAST(@c AS VARCHAR)), 10), DATEADD(MINUTE,@c,@d), FLOOR(RAND()*3), FLOOR(RAND()*2), 'INFO')
    SET @c = @c + 1
END

View 7 Replies View Related

Transact SQL :: Confirmation Of UNION ALL Query For INSERT INTO Temp Table

Jul 21, 2015

I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.

In my first SELECT statement, I have INSERT INTO @tblData.

Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?

DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table

[Code] ....

View 3 Replies View Related

Transact SQL :: INSERT Specific Data To Other Table

Aug 10, 2015

I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them

Table 1 - VendorTransactions

ID     PARTY    AMOUNT   VOUCHER
---------------------------------------
1       A               5000              Purchase
2       B               3000              Purchase
3       C               2000              Purchase

4       A               3000              Payment
5       B               1000              Payment
6       C               2000              Payment
7       A               1000              Payment


Now we have a blank table Table 2 - Liabilities

ID     PARTY    AMOUNT 

I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below

ID     PARTY    AMOUNT
-------------------------
1       A               1000              
2       B               2000              
3       C               0       

View 3 Replies View Related

Transact SQL :: Insert Data Into Two Tables At A Time

Apr 20, 2015

i have these two tables

create table [dbo].[test1](
[test1_id] [int] identity(1,1) primary key,
[test2_id] [int] not null
)

[code]...

I want to insert the data into two tables in one insert statement. How can i do this using T-SQL ?

View 6 Replies View Related

Transact SQL :: Insert Data Into Excel Using OPENDATASOURCE

May 14, 2015

While trying to insert data into existing XLS file, using below command, i am getting following error.

Insert into OPENDATASOURCE( 'Microsoft. ACE.OLEDB.12.0','Data Source=e:ediuploadhello1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
Select top 50 product_no From product_mst
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". Unknown provider error.

View 2 Replies View Related

Transact SQL :: XML Data Column - Insert Into Another Table

May 29, 2015

I have table with about 10000 rows,  there is a column named  Raw_XMLData is defined as varchar but data is xml format.  

I try to insert into another table, if Raw_XMLData column has is valid xml data?

Is it possible to do in T sql?

View 2 Replies View Related

Transact SQL :: Insert Duplicate Data From One Table To Another

May 14, 2015

i am having three tables

test
testId  testName  totalQuestion

Questions

QuestionId  fktestId  QuestionName

Now i am trying to insert a duplicate copy test by passing testId..Here is my sp

if not EXISTS(SELECT testName from tblTest WHERE UPPER(@testName) = UPPER(@testName))
BEGIN
INSERT INTO tblTest SELECT @userId, testName,duration,totalQuestion,termsCondition,0,GETUTCDATE(),GETUTCDATE() from tblTest WHERE id=@testId
SET @insertedTestId=@@identity
INSERT INTO tblTestQuestion SELECT @insertedTestId,question,0,GETUTCDATE(),GETUTCDATE() from tblTestQuestion WHERE testId=@testId
END

how to insert in answer table as one question can be multiple answers.

View 4 Replies View Related

Transact SQL :: Bulk Insert Arabic Data From CSV File To DB

Aug 5, 2015

USE TEST 
GO 
/****** BULK INSERT  ******/
BULK
INSERT [Table01]
FROM 'C:empdata.csv'

[code]....

I am using above code to insert csv file data which consist of arabic data as well.  Upload is successful however Arabic field data is uploaded with invalid characters and getting the following error Msg 4864, Level 16, State 1, Line 3...Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

View 15 Replies View Related

Transact SQL :: Error To Insert Data From Link Server

Jul 30, 2015

Msg 7314, Level 16, State 1, Line 42

The OLE DB provider "SQLNCLI10" for linked server "192.168.0.40" does not contain the table ""CP_DW"."dbo"."StgDimSalesTargetSetup"". The table either does not exist or the current user does not have
permissions on that table.

Insert into [192.168.0.40].CP_DW.dbo.StgDimSalesTargetSetup

Select t.SalesTargetCode,t.SalesTargetId,t.TargetDefination,
--LEFT(DATENAME(MM, t.PeriodFrom), 3) + '-' + CONVERT(VARCHAR(4),DATEPART(YY, t.PeriodFrom)) AS MonthYear
--CONVERT (char(8),PeriodFrom,112) [Datkey],
Left(CONVERT (char(8),PeriodFrom,112),6) [Monthkey],t.PeriodFrom,t.PeriodTo,t.TargetAmount
FROM [SO_SalesTargetSetup] t

View 5 Replies View Related

Transact SQL :: Read Data From CSV Files And Insert Into DataBase

May 6, 2015

I have a requirement to

a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.

This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.

View 9 Replies View Related

Transact SQL :: Data Validation Of All Insert Statement Inside Server

Jun 25, 2015

In C# .NET I have the possible to create some validations of my data, with regulary expressions.  Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?

View 5 Replies View Related

Transact SQL :: Method To Generate Insert Statements For Data In A Table

May 30, 2013

I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)

View 7 Replies View Related

Transact SQL :: BULK INSERT Not Importing Correct Data For Field?

May 14, 2015

I am using a BCP format file to import a CSV file. The file looks like the following:

"01","02"

The format file looks like the following:

6.0                                                                                     
2                                                                                      
1      SQLCHAR    0      0       """         0    ""
2      SQLINT       0      0       "",""     1   MROS
3      SQLINT       0      0       ""
"   2   MROF

When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02.  These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file.  The results was that the 01 became 12592 and the 02 became 12848.  where these numbers are coming from?

View 7 Replies View Related

Transact SQL :: Bulk Insert When Data File Is On Network Share

Dec 3, 2015

I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.) I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there. My SQL is in the form of:

Bulk Insert [table name] From '[server][share][filename]' With (FirstRow = 2, FormatFile='FormatFile.xml')

Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error:

Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file "[server][share][filename]" could not be opened. Operating system error code 5(Access is denied.).

I found this snip at

BULK INSERT (Transact-SQL)Security Account Delegation (Impersonation), which says, in part (emphasis mine):

To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation.

How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work. Later we tried constrained delegation and it still does not work.

I have verified the SPNs:

C:>setspn adsvc_sqlRegistered ServicePrincipalNames for CN=SVC_SQL,OU=Service Accounts,OU=Users,OU=ad domain,DC=ad,DC=local:        MSSQLSvc/SQLQA.ad.local:1433        MSSQLSvc/SQLDev.ad.local:1433        MSSQLSvc/SQLQA.ad.local        MSSQLSvc/SQLDev.ad.local
I have verified that my SQL connection is TCP and I am getting/using a Kerberos security token.
C:>sqlcmd -S tcp:SQLQA.ad.local,1433 -E1> Select dec.net_transport, dec.auth_scheme From sys.dm_exec_connections As dec Where session_id = @@Spid;2>
gonet_transport auth_scheme------------- -----------TCP KERBEROS(1 rows affected)1>

If I move the source file to a local drive (on the SQL server), all works fine, but I must be able to read from a file share?

View 8 Replies View Related

Transact SQL :: Aggregate Function Causing Error In Complex Join

May 13, 2015

I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn. 

select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+

[Code] .....

I think I probable need to include a group by but can't figure out the correct syntax.

View 12 Replies View Related

Complex Insert Statement

Oct 9, 2006

I have this stored procedure that returns a rowid, distance.  It has a latitude, longitude, and range as inputs, it takes the latitude and longitude and computes a distance with every lat/long in a table PL_CustomerGeocode.  Once that distance is computed it compares that distance with the range, and then returns the rowid, distance if the distance is <= range.  I have the SELECT statement down, but now i just need to enter this information into a seperate table PL_Distance with (rowid, distance) as columns.  The sql statement is as follows, and i cant figure out where the rowid part is an the distance part is:      DECLARE @DegreesToRadians    float    SET @DegreesToRadians = Pi()/180    SELECT rowid, Cast(distance As numeric(9,3)) AS distance    FROM (SELECT rowid, CASE WHEN @srcLat = geocodeLat And @srcLong = geocodeLong THEN 0.0                             WHEN ABS(Arc) > 1 THEN 0.0                             ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance    FROM (SELECT Power(sin(DLat/2),2) + cos(@srcLat*@DegreesToRadians)*cos(geocodeLat*@DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong    FROM (SELECT @srcLong*@DegreesToRadians-geocodeLong*@DegreesToRadians AS DLong,    @srcLat*@DegreesToRadians-geocodeLat*@DegreesToRadians AS DLat,    rowid,    geocodeLat,    geocodeLong    FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z    WHERE distance <= @range

View 1 Replies View Related

Complex Insert Problem

Oct 16, 2007

I am not really sure how to phrase this problem so I have a picture to help

 
Okay the tables above allow me to create types of data for pages that collect small amounts of info without me having to create hundreds of small tables
so one type of data I collect is Employee First Aid training - that type of data is made up of three attributes EmployeeID (ID 3 in the Attributes table), FirstAidTrainingDate (ID 4 in the Attributes table), LocationID (id 6 in the attributes table) 
This data is to be saved in the Data Table as rows
so the data table looks like this assuming my DataThing is number 10 in the Objects table and its Type 1 in the Types table the data table will record
10,3,83
10,4,10/25/2007
10,6,35
 
My question is this without having to do three insert statements is there a way to pass in a pipedelimited string and have some dynamic sql do all the inserts for me?
If this makes no sense please tell me what  a biter I am :)
 
 

View 2 Replies View Related

Transact SQL :: Sending Auto Email With Queried Data (On INSERT And On Month Ends)

Oct 22, 2015

I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file 

I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.

USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]

[Code] ....

View 8 Replies View Related

Transact SQL :: Query For Yesterday Data

Dec 2, 2008

Howto write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i don't want. everyday i need to search the data from yesterday whole day.

View 13 Replies View Related

Transact SQL :: Looking For Data Pagination By Query

Sep 24, 2015

I need sql script where i will pass startindex, endindex and sortcol and order then query return result accordingly. it would be better if query looks small and dynamic.

Here one sample of dynamic sql but there case is used which is not require.

DECLARE @sql NVARCHAR(MAX)
SET @sql = ';WITH cte as(SELECT *,
ROW_NUMBER() OVER
(ORDER BY ' + CASE (@SortColumn + ':' + @SortDirection)

[Code] ...

My requirement is that i will create a sp where i will pass start index and end index and sort column,sort order and filter means where clause and sp will generate and execute dynamic sql and return data. i need a sample of that kind of sp.

View 9 Replies View Related

Transact SQL :: Query To Get Last Available Data Given Month And Year?

Jul 21, 2015

I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.

Client,Month,Year and Comments are columns in that table.

For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.

If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.

View 10 Replies View Related

Transact SQL :: Query To Extract Non Numeric Data?

Sep 8, 2015

i have dat like this

ab0001
a001
abc001

I need the following output

ab
a
abc

View 7 Replies View Related

Transact SQL :: Summarizing Data Returned By Join Query

Nov 20, 2015

I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM.  

I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D').  

When I run the query below it returns 4 million rows.  How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?

SELECT edw_ar_fl.ARTBASE.MA_NUM,
       edw_ar_fl.ARTBASE.MA_PAID_AMT
FROM   edw_ar_fl.ARTBASE
       JOIN edw_sbx_bt.jjenkins_OVERAGE2
         ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM
WHERE  edw_ar_fl.ARTBASE.CONTRACT_NUM = '593'
       AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = '

View 4 Replies View Related

Transact SQL :: Returning Different Data When Part Of Query Is Separate?

Aug 3, 2015

I am writing a stored procedure to prepare some reports.

The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).

When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?

alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status]
@Salesmen nvarchar(50),
@QuoteStatus nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;

[code]...

View 5 Replies View Related

Transact SQL :: Query To Update A Table With More Than 150 Million Rows Of Data?

Sep 17, 2015

I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:

Source Tables :

OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,

[Code] ....

The update requirement is as follows:

DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
    SET rowcount 10000
    update r
    set Comp=t.Comp

[Code] ....

The update took more than 48h and didn't terminate , how to accelerate it ?

View 6 Replies View Related

Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query

Sep 11, 2015

Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the following 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

But instead of TABLE_NAME i would like to give a query like

SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].

View 10 Replies View Related

Transact SQL :: Passing Multi-valued Data As Single Parameter In Query

Jul 2, 2015

I have a sp where query is as below.

DECLARE @ServerCIName varchar(5000)
SELECT * FROM  dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.

But @ServerName contains multiple values and it is dynamic (not constant).

How do I query the data?

View 6 Replies View Related

Transact SQL :: Query Based On Date - Get Data From Table Above WHERE DateofBirth Is Tomorrow

Oct 30, 2015

Lets say I have a table, tblPersons

FirstName |  LastName   |  DateofBirth
----------------------------------------
  Thomas   | Alva Edison |   10-10-2015
  Benjamin | Franklin    |     10-10-2015
  Thomas   | More        |     11-10-2015
  Thomas   | Jefferson   |    12-10-2015

Suppose today's date is 09-10-2015 in (dd-MM-yyyy format), I want to perform a query in such a way that I should get the data from the table above WHERE DateofBirth is tomorrow, so I could get the following result.

FirstName |  LastName   |  DateofBirth
----------------------------------------
  Thomas   | Alva Edison |   10-10-2015
  Benjamin | Franklin    |     10-10-2015

View 15 Replies View Related

I Need Some Help With A Complex Query

Jan 6, 2007

I've written a lot of queries in the past, but I'm having a lot of trouble with this one.

View 4 Replies View Related







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