Posts Tagged ‘Query’

By using the following query you will be able to get all the tables with at least one record in the selected database. It will be handy when you needed to get an idea of the number of records in a database. This uses two SQL Server system views, namely SysObjects and SysIndexes.

  1. SELECT sysobjects.name AS [Table Name],
  2.         MAX(sysindexes.rows) AS [Number of Records],
  3.         sysobjects.crdate AS [Created Date],
  4.         sysobjects.refdate AS [Referenced Date]
  5. FROM sysobjects
  6. INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
  7. WHERE sysobjects.xtype = ‘U’ — Filtering all the User Tables.
  8.     AND sysindexes.rows > 0 — Getting all the tables having at least one reoord.
  9. GROUP BY sysobjects.name,
  10.     sysobjects.crdate,
  11.     sysobjects.refdate
  12. ORDER BY 2 DESC — Ordering by the number of records in table.

 

Above query will bring the following results on the Northwind database.

image

When the need comes to port tables from one server / database to another server / database there is an easy way than creating the tables manually and importing data into table.

By using the following query you can import the table structure and the data of the required table.

  1. SELECT *
  2. INTO [DestinationServerName\SQLServerInstanceName].[DestinationDatabaseName].[OwnerName].[DestinationTableName]
  3. FROM [SourceServerName\SQLServerInstanceName].[SourceDatabaseName].[OwnerName].[SourceTableName]

Using the above method you can transfer tables between different databases, Server instances or different database servers.

Consider the following example in which I am transferring the Customers table from Northwind database to my Test database.

  1. SELECT *
  2. INTO [Test].[dbo].[Customers]
  3. FROM [Northwind].[dbo].[Customers]

Here SELECT statement will behave the same way as in a normal SELECT * FROM TableName statement meaning you can use all the techniques used in SELECT statements to filter the data needed to be ported. For example following query will only bring customers who are from UK.

  1. SELECT *
  2. INTO [Test].[dbo].[Customers]
  3. FROM [Northwind].[dbo].[Customers]
  4. WHERE Country = 'UK'

One thing to remember is, this method will not import the keys, meaning if you had a primary key set to CustomerId it will not exist in your new table. So you need to add the keys (primary and secondary) to the new table to match it with the source table. If you had identity specification turned on in your source table this will set it for you, so you do not need to set it again.

UPDATE with JOIN

Posted: January 30, 2012 in SQL Server
Tags: , , , , , ,

If you need to update data of a table (DestinationTable) with data of another table (SourceTable) there are 3 ways you can get this done in SQL.

Method 1

This is the most common and simple way with using a sub query.

  1. UPDATE DestinationTable SET DestinationColumn =
  2.     (SELECT ValueColumn FROM SourceTable WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn)

 

Method 2

This users the most common FROM clause to join the two tables as shown below.

  1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
  2. FROM SourceTable
  3. WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn

 

Method 3

Last method mentioned below uses the join clause to join both the tables to make the update happen properly.

  1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
  2. FROM DestinationTable
  3. INNER JOIN SourceTable ON DestinationTable.MappingColumn = SourceTable.MappingColumn

 

Hope these helps.