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


ADVERTISEMENT

SQL Server 2014 :: Data Profiling Tables Within Server

Jun 18, 2015

I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:

Null_Count
Null_Percentage
Total_Record_Count

I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.

View 2 Replies View Related

SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding

Apr 1, 2015

when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way

View 1 Replies View Related

SQL Server 2014 :: Need Output In Single Row

Oct 29, 2015

I need out put in single row for below 2 queries using joins

SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info

[code]....

View 4 Replies View Related

SQL Server 2014 :: Importing Spreadsheet In One Single Column

Feb 13, 2015

I am trying to import 1 spreadsheet to a database table. I am using SSMS export import wizard to import spreadsheet. My goal is to import whole spreadsheet in one single column.

View 7 Replies View Related

SQL Server Admin 2014 :: Restoring A Single Table From Backup

Dec 12, 2014

With all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).

View 6 Replies View Related

SQL Server Admin 2014 :: How To Convert Float To Timestamp In Single Select Query

Mar 18, 2015

How to convert float to timestamp in single select query

E.g., i have float as 1.251152515236 ,

I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00

View 1 Replies View Related

SQL Server Admin 2014 :: Logon Trigger Executing Multiple Times For Single Connection?

Jan 30, 2015

I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.

This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?

I also tried using the After Logon option, and got the same issue.

Here is the code:

CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),

[code]....

View 0 Replies View Related

Writing Data From Multiple Tables To A Single Flat File

Sep 13, 2005

I have a package that contains three database tables (Header, detail and trailer record) each table is connected via a OLE DB source in SSIS. Each table varies in the amount of colums it holds and niether of the tables have the same field names. I need to transfer all data, from each table, in order, to a flat file destination.

View 6 Replies View Related

How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?

Mar 18, 2008

This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.

I know how to get the user table names from the DB as follows:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'

What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".

delete from tableName where Date_Stamp = '2008-3-18'

View 7 Replies View Related

Integration Services :: How To Load Multiple Tables Data Into Single Excel File

Aug 26, 2015

My Requirement ,In Source Database 5 tables are there ( Emp,Loc,dept,Time,Product ), Destination is Single Excel file.But Dynamically how to load each table information to load into each sheet wise through SSIS Package?

View 3 Replies View Related

Integration Services :: SSIS Split Single Input File Data Over Multiple Tables?

Sep 30, 2015

I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes.  The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length. 

Can SSIS be used to created these two tables? (one without  description fields, the other with those field but arranged vertically in the table rows).

The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.

View 8 Replies View Related

SQL Server Admin 2014 :: Making Single Subnet Cluster Multi-subnet

Aug 18, 2015

We have a SQL 2014 AlwaysOn availability group running on two Windows 2012 R2 servers that are in the same subnet. We created a new server in a second subnet, installed SQL, joined the server to the Windows cluster, added a new IP resource for the new cluster, and performed the other remaining steps to add a new AG replica to the SQL instance on this new server. When we try to move the core cluster resources to the new node to test failover, we get an error. Here's the command we've been using:

Move-ClusterGroup "Cluster Group" -Node node3

and it returns the error: The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group...I've checked the ownership of the cluster groups and the cluster resources and it looks like they are set appropriately:

>Get-ClusterGroup | Get-ClusterOwnerNode
Cluster Object Owner Nodes
---------------- ---------------
Available Storage {}
Cluster Group {node1,node2,node3}
SQLAG {node1,node2,node3}

[code]....

We've double-checked that all IP resources are in the right subnets and that the dependencies for the Cluster Name resource and the Listener Name resource are set appropriately. I'm not sure what else to check since the PowerShell commands seem to indicate that node3 is an owner of the appropriate resources. What other things need to be checked or if the ownership being checked isn't the same as what PowerShell is checking?

View 3 Replies View Related

SQL Server 2014 :: Create Auto Relationship Between Tables

May 12, 2014

I would like to create a auto relationship between tables.

Currently I am using Northwind DB with tables (Orders, OrderDetails, Customers)

Orders ( OrderId, Customerid)
OrderDetails(OrderId)
Customers(CustomerID)

Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.

What is the approach?

View 9 Replies View Related

SQL Server Admin 2014 :: Rebuild CMS Tables From XML File?

Jul 23, 2014

I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the

msdb.dbo.sysmanagement_shared_server_groups_internal and
msdb.dbo.sysmanagement_shared_registered_servers_internal tables.

However I still have the local XML file that SSMS reads so I can still access the groups, I just get weird errors when trying to re-register my install as the new CMS. How to rebuilt those tables from the XML file or know of a way to repopulate?

View 3 Replies View Related

SQL Server 2014 :: Select Columns From Different Tables Without Join

Jan 20, 2015

How to join 2 heap tables with out any common fields.

for example tbl1 has
col1
1
2
3
4
5

and tbl1 2 has
col2
a
b
c

I want the output like
col1 col2
1 a
2 b
3 c
4
5

is this possible with out using row_number()?

View 9 Replies View Related

SQL Server Admin 2014 :: Storing Very Large Tables?

Jan 24, 2015

We are in the middle of re-designing few tables (namely transaction tables) that would store very large data and would be hosted on cloud (Azure). The old design of this product breaks transaction tables into monthly tables. i.e. say ORDERS Table would be physically broke into twelve monthly tables over a year like ORDERS0115 (mmyy), ORDERS0215 and so on.

We are in the opinion that keeping the entire transactions in one Table is better. Would like to know what's the best practices for transaction tables like the one mentioned above? Is it better to use one table with partitions. I read somewhere that partitions can slow down SELECT queries if not designed and thought properly.Since this would be hosted on cloud (Azure), do you think some additional things are to be taken care? How a site like Amazon keeps their transactions tables?

View 8 Replies View Related

SQL Server 2014 :: Duplicate Record Results On 2 One To Many Tables?

Feb 1, 2015

I have 3 Tables

TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]

A query returns duplicate results from TableB as well as TableC

TableB and TableC have nothing in common and should not interfere with each other.

with TwoTables as (
select
a.QuickSpec as QuickSpecId,

[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]

View 1 Replies View Related

SQL Server 2014 :: Memory Optimized Tables And Indexes

Feb 18, 2015

I'm just beginning to experiment with memory optimised tables.

I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.

I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.

(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)

The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?

View 1 Replies View Related

SQL Server 2014 :: Find Count For Two Tables And Put Into Other Table

Jun 18, 2015

I have two table in two different database. I have to found count for both table and put into other table.

example : Database --> A , table is T1
Database --> B , table is T2

I want count of both table and put into T3 table in database A or B .

View 7 Replies View Related

SQL Server Admin 2014 :: Partitioning Master And 4 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and need to be partitioned for better manageability.

Little info: Every day, 300 Million records are inserted and 300 million records are deleted in below 7 tables. we maintain only 8 days worth of data in below tables which is the reason records which are older than 8 days are continuously deleted.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table.
dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

I would like to partition the above child tables based on the IDs that are inserted every 4 hours. Meaning, All IDs that are inserted in 4 hours window should be in a partition.

View 1 Replies View Related

SQL Server Admin 2014 :: In-Memory Processing - Existing Tables

Nov 11, 2014

Is there a method of forcing existing tables into the in-memory filegroup so the table data can benefit from in-memory processing.

View 7 Replies View Related

SQL Server 2014 :: Display Column Headers And Rows From Different Tables?

Dec 18, 2014

I am looking for SQL query which uses 2 tables CASH and BALANCE.

eg: Need Tablename, ColumsList and data in the results set.

eg: 10 rows shown below and ordered based on Acct_number

Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE

Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY

Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05

Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07

Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05

Row6,BALANCE,10,500,750,DR,12/12/2014,USD

Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05

Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07

Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05

Row10,BALANCE,20,500,750,DR,12/12/2014,USD

View 7 Replies View Related

SQL Server 2014 :: Insert Dataset Into Two Tables And Grabbing Identity From One For Other

Jan 2, 2015

Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.

I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.

Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.

What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.

I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.

View 9 Replies View Related

SQL Server 2014 :: Query To Combine Two Tables Based Third Table

Feb 18, 2015

I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:

Table A:

Table B:

Table C:

So what query do I need write to have table like below?

Table D

View 7 Replies View Related

SQL Server 2014 :: How To Auto Update Multiple Tables Daily

Apr 22, 2015

I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.

I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.

I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.

View 4 Replies View Related

SQL Server Admin 2014 :: Columnstore Index On Large Tables

Jul 1, 2015

I created columnstore index on the table with 20 columns and about 1000 000 000 rows

every day added about 5M rows

"select" queries became faster because of batch mode and table demand less disk space then before

I have also 6 similar tables with 5 000 000 000 rows and plan to move them on columnstore index

server has 128 G RAM

What pitfalls I could face if I will have so many columnstore indexes on one server?

How a could see problems in DMV?

View 3 Replies View Related

SQL Server 2014 :: How To Systematically Manage Big List Of Queries And Tables

Sep 12, 2015

Suppose someone has to work on a lot of different SQL Server Databases which have got a lot of Tables and Queries / Views inside them.

After a period of time, it becomes very difficult to remember exactly what kind of columns are present within a given Table and View.

Any method by which one can keep a systematic list of all the Tables and Views that are present within a SQL Server Database, along with the columns that are present within them.

Are there any Add-on products or services etc. available in making this type of work systematic?

Currently I add comments to each queries inside SQL Server to remind me of what this query is doing, but this method is not great.

View 2 Replies View Related

SQL Server 2014 :: 2 Tables Combined Left Join To Primary Key Of A 3 Third Table

Sep 1, 2014

Looking to improve performance of the following code.

It basically generates future days for each dog. So there is a dog table and a day table with every day.

These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.

All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.

How to make the last query as fast as possible.

IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS

[Code] .....

View 2 Replies View Related

SQL Server Admin 2014 :: Update Statistics On Frequently Updated Tables

Dec 23, 2014

I'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).

When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.

View 2 Replies View Related

SQL Server 2014 :: Eliminate Duplicate Rows When Joining Multiple Tables

Jun 8, 2015

We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.

SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,

[Code] ....

View 9 Replies View Related

SQL Server 2014 :: Find Common Link Between Two Tables In Database Without Key Constraints

Jul 16, 2015

Any tool, script, procedure, or otherwise that will solve this problem?

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )

CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

View 7 Replies View Related

SQL Server Admin 2014 :: Import Tables From Azure To Premise Database

Oct 26, 2015

We would like to import some tables from Microsoft azure data mart to our local database on the server of our premise for some purpose.

View 4 Replies View Related







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