SQL Server 2014 :: Update Old Data With New Data

Jun 5, 2014

I got this script but all I get are the errors:

Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()

[code]...

View 8 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Change Data Capture(CDC) For Data Warehouse / Reporting?

Aug 12, 2015

I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.

The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?

Is there any performance impact if we enable CDC on OLTP db?

Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?

What is the best way to implement CDC to take incremental changes for reporting.

View 0 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

SQL Server 2014 :: How To Sum And Group Data

Jan 20, 2015

I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.

Is this possible?

This is my current SQL

SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,

[Code] ....

View 9 Replies View Related

SQL Server 2014 :: Cursor To Delete Data?

Nov 5, 2013

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.

I will have to place the query in a SQL job and run that weekly once

View 9 Replies View Related

SQL Server 2014 :: Transform Data Into Column

Jan 8, 2014

I have data like this in the table :

IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40

Desired Output required:

IntRecpieID StrName 1 2 3

100 'A' 20 Null 40

101 'B' Null 30 Null

View 5 Replies View Related

SQL Server 2014 :: Insert Data From PDF Files

Mar 23, 2014

We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?

View 6 Replies View Related

SQL Server 2014 :: One To Many Relationship In Data Processing

Jun 24, 2014

I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.

I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.

What would be the best option to select this kind of records.

View 9 Replies View Related

SQL Server 2014 :: Un-Group Data From Report

May 28, 2015

We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.

Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department

If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data

--===== Create the test table with
SET ANSI_NULLS ON

[Code] .....

View 3 Replies View Related

SQL Server 2014 :: Extracting Data From Database?

Nov 5, 2015

I am running an hotel application for all the bookings.

I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.

The application has an SQL database locally on my computer.

Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?

View 8 Replies View Related

SQL Server Admin 2014 :: Cannot Export All Data From Database

Dec 4, 2013

And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql

View 4 Replies View Related

SQL Server 2014 :: Enquiry On Retrieving Data From Database

Jul 18, 2014

I have a drop down menu for transmission. Inside the drop down menu, there are 1.Any 2.Auto 3.Manual options

Now I want to retrieve from my database, if the client select '1.Any' option. Meaning the output I want it to display all the products which have both auto and manual for transmission.

I have no problem retrieving all the products which either only have auto option or manual option.

View 2 Replies View Related

SQL Server 2014 :: Bulk Insert Data Into Table

Jul 29, 2014

I need to load the following data into a SQL table. This is how the vendor is able to provide it to us.

CRCorp Daily Report,,,,,,
,,,,,,
Facility,Location,Purchase Order #,Vendor,Inventory #,Date Ordered,Extended Cost
09-Mowtown 495 CRST,09-402A Women's Imaging,327937,"BARD PERIPHERAL VASCULAR, INC.",113989,7/25/2014,650
09-Mowtown 495 CRST,09-402A Women's Imaging,327936,"WB MASON CO., INC.",112664,7/25/2014,8.64
01-Mowtown 499 CRST,01-302B Oncology,327947,McKesson General Medical,n/a,7/25/2014,129.02

[Code] ....

I have attempted to bulk insert it into this table with no luck.

CREATE TABLE POMaster
(Facility VARCHAR(75),
Location VARCHAR(75),
PONum INT,
VendorNm INT,
INVENTORYNUM VARCHAR(25),
orderDte DATE,
ExtendedPrice NUMERIC(10,2)
)
GO

It does not like the double quotes. How to make this format work? Do I need a format file?

View 2 Replies View Related

SQL Server 2014 :: Combine Data From Historical Table?

Aug 13, 2014

Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.

View 9 Replies View Related

SQL Server 2014 :: Using Data From Two Tables In A Single Script?

Jan 20, 2015

Basically I've got two tables with a common column (in this case it's UserID)

The structure of the table Users is basically:

UserID FirstName LastName
1 Jim Smith
2 Nancy Orr
3 Ken Stephenson

And the structure of the classes table is

UserID ClassID
1 AAA
1 BBB
2 AAA
3 BBB

What I'd like to do is select the FirstName and LastName the users table who have a UserID in both Classes and Users.

The tricky part of it is that I only want to return the FirstName and LastName of userID who have multiple ClassID in the classes table. (hopefully I'm making sense when I explain this)

In this case I'd like it to return just Jim Smith's name because he's the only userID who has two ClassID entries.

So far I've come up with

select UserId, count(*) as MultipleClasses
from classes
group by UserID
having count(*) > 1;

which outputs the UserID and a colum called MultipleClasses with how many classes each ID returned has.

how would I do something like

Select firtname, lastname
from users
where Userid=

The output from that initial bit finding users with multiple classes.

View 3 Replies View Related

SQL Server 2014 :: Encryption And Data Length Limitations?

Feb 2, 2015

I'm having an issue in encrypting large documents. I know that previous editions ENCRYPTBYKEY had a maximum size of 8,000 byte limitation. Does SQL Server 2014 have any new features that overcomes this limitation?

Using
SQL Server 2014 Std
Symmetric key with AES_256

View 2 Replies View Related

SQL Server 2014 :: Select Data And Count In View

May 26, 2015

How can I select data from a table and row counts from multiple tables in a view. For example:

Select * from Settings -- it gets 1 row only
Select count(*) from NewApps where Status = 'False'
Select count(*) from myUsers where Status = 'Pending'

I just want to get them all in 1 view...

View 1 Replies View Related

SQL Server 2014 :: Return Data Between Two Dates In The Past?

Jun 6, 2015

why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates.

View 9 Replies View Related

SQL Server Admin 2014 :: TDE Table Data Encryption?

Jun 8, 2015

I'm having problems with the following code:

--DROP MASTER KEY
--GO
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO
USE master;

[code]....

What am I missing? What do I have to do if I get in a situation where I need to back out and start over?

[URL]

View 9 Replies View Related

SQL Server 2014 :: Eliminate Used TempDB To Spill Data

Jul 10, 2015

I've been trying to make the following query more performant by breaking it up into smaller pieces.

SELECT MT.A3+MT.A4 AS A34,MT.A3
-- ,M.*
FROM
Master_TAB M
JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum

[Code] ....

I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB

Here's my attempt...

-- MASTER_TAB has 1.7 million rows and 50 columns
CREATE TABLE [dbo].[tmp_ABC](
[A1] [varchar](13) NOT NULL,
[A2] [varchar](5) NOT NULL,
[A3] [varchar](4) NOT NULL,
[A4] [varchar](4) NOT NULL,
[A5] [int] NULL
) ON [PRIMARY]

[Code] ...

This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)

Select c.A3+c.A4 as A34, c.A3, c.A1
-- M.*
from tmp_DEF c
join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2
order by c.A3, C.A4

if I just run the following I get no spill:

Select c.A3+c.A4 as A34, c.A3, c.A1
from tmp_DEF c
order by c.A3, C.A4

as soon as I add the Master table as a Join I get the Spill...

I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?

View 9 Replies View Related

SQL Server 2014 :: Export Data Without HTML Formatting

Oct 22, 2015

I need to export some Database data into a text file. My Query looks like this:

SELECT Category1, Category2, Category3
FROM dbo.tbl1
WHERE Category1 = 'JP-4'
AND Category2> 4;

This works fine to get the data, however there is some html formatting in the table entries such as

`<p>,</p>,
,</br>` etc.

So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work. I've also got an issue with line splits and would need to remove the ( ).

The Data format is something like this:

Category1: JP-4
Category2: 4
Category3:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p> <p>amet, consectetur, adipisci velit</p>
Category4:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>

I got it to work like this with the replace function:

SELECT REPLACE(REPLACE("PHOTOGRAPHS",'<p>',''),'</p>','')
FROM dbo.khia_tbl
WHERE Category1= 'JP-4'
AND Category2> 4;

But the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so
,
</br>,

as well as "" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it...

View 1 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server

Mar 11, 2015

I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:

We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.

Here is what I want to do:

I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.

View 2 Replies View Related

SQL Server 2014 :: How To Import Data To Server From Access Database Using Script

Sep 22, 2015

I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO

[code]...

The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

Secondly I need to make 2 more changes to the code posted above.

1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.

2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

View 7 Replies View Related

SQL Server Admin 2014 :: Space Did Not Gets Released After Removing Data

Nov 21, 2013

We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? tell me how exactly it releases the space after truncating a 120GB table?

View 8 Replies View Related

SQL Server Admin 2014 :: How Data Will Transfer From Source To Destination

Aug 6, 2014

Does Replication use linked server.

If not then how data will transfer from source to destination.

Note : As per my knowledge it is not backup and restore like logshipping.

View 2 Replies View Related

SQL Server Admin 2014 :: Replicating Data To A Table Via A View

Aug 11, 2014

I am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.

Is this possible?

Running SQL Server 2008 R2 Enterprise.

View 1 Replies View Related

SQL Server 2014 :: Move Data / Findings Into A Perm Table?

Oct 18, 2014

I would like to move the data / findings into a perm table?The reason for this is so clients can connect to the table using excel. I have another stored procedure which is setup in this process already and it works well. I basically have the stored procedure setup on a task to run early in the morning so when clients get up they connect and get their data.

USE [MMAUDIT]
GO
CREATE PROCEDURE [dbo].[MMA_AUDIT_QUESTIONS_SUMM_STG] AS
BEGIN

[code]....

View 4 Replies View Related

SQL Server Admin 2014 :: Full Backup While Data Is Modifying

Nov 13, 2014

If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?

View 2 Replies View Related

SQL Server Admin 2014 :: How To Identify Data Leakage In A Database

Dec 29, 2014

how to identify the data leakage in a database , as I heard in one of my environment?

what is the meaning for data leakage ?

View 3 Replies View Related

SQL Server Admin 2014 :: How To Identify Empty Data File

Jan 21, 2015

I was running an operation to shrink file/emptyfile a data file, and then remove it.

It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).

How does the engine know not to put any more data in there, and how long does that situation last?

View 3 Replies View Related

SQL Server 2014 :: Change Data Capture - Minimum Latency?

Jan 28, 2015

I am trying to use change data capture to load the data into the secondtable from table 1 which is coming from UI.

What will be the minimum latency??? Can we use incase of latency less than 5 seconds.

View 1 Replies View Related

SQL Server Admin 2014 :: Structure Of Log Saving (for Data Mining)

May 12, 2015

We saved huge log data from user behaviour in our site .

But In data mining time , we saw that most of them cant use for data mining

What is the best practice about data gathering from user movement in site?

is there any best practice Template for this ?

View 0 Replies View Related







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