There are few methods of copying a table to another database, depending on your situation. Same SQL Server Instance If trying to copy a table to a database that is on the same instance of SQL Server, The easiest solution is to use a SELECT INTO while using the fully qualifed database names. [cc lang=”sql”] SELECT * INTO Database2.dbo.TargetTable FROM Database1.dbo.SourceTable [/cc] This will create a copy of the entire table across two different databases and will definitely be the quickest way. This method utilizes minimal inserts. The caveat is that it will not copy over the primary keys, foreign keys or the indexes. These will have to be scripted out and added after the insert. Different SQL Server Instance (and/or different server) If trying to copy a table to a different instance (be it on the same server or not), there are two basic routes. OPENROWSET is an easy way to do the insert. Just replace the server names below and instance name (if the database is defined by an instance). [cc lang=”sql”] SELECT * INTO dbo.TargetTable FROM OPENROWSET(‘SQLNCLI’, ‘server=SourceServerNameSourceInstance;trusted_connection=yes’, ‘SELECT * FROM SourceDatabase.dbo.SourceTable’) [/cc] The other way is to utilize the SSIS Import/Export Wizard. This always works well too. In order to initialize, right click on the database, select tasks, then import/export.
Continue reading ...
There are many good uses of the SELECT TOP 1 method of querying. Essentially, the select top 1 method is used to find the min or max record for a particular column’s value. There is some debate as to whether this is the ‘correct’ method of querying, however it should be known that this method does not break any guidelines and is supported by all standards of SQL. The TOP 1 means to only return one record as the result set. which record is returned, depends on the column that is specified in the order by clause. If you want to find the record with the minimum value for a particular column, you would query the record with the ORDER BY being ascending (ASC). If you want to find the maximum record with that value, you would query it with the ORDER BY descending (DESC). For example, say you wanted to find the record in a table for a customer that has the largest order. Traditionally, the way to do this would be to find the maximum order value then join back on the table in order to find the record. Like the following: [cc lang=”sql”] SELECT * FROM SalesLT.SalesOrderHeader soh WHERE TotalDue = ( SELECT MAX(TotalDue) FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = soh.SalesOrderID ) [/cc] However, using the SELECT..TOP 1 method of querying, you can perform the same query using the following notation. [cc lang=”sql”] SELECT TOP 1 * FROM SalesLT.SalesOrderHeader ORDER BY TotalDue DESC [/cc] One of the major […]
Continue reading ...
In creating a database you also need to check whether or not the database already exists. In order to do so, simply use the ‘if exists’ method and select the name of the database from sysdatabases. [cc lang=”sql”] IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N’SQLServerPlanet’) CREATE DATABASE [SQLServerPlanet] [/cc] The code below will drop an existing database if it exists so be careful. [cc lang=”sql”] IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N’SQLServerPlanet’) DROP DATABASE [SQLServerPlanet] [/cc] An alternate method is to use the db_id to convert the db_name and see if it is null. [cc lang=”sql”] if db_id(‘SQLServerPlanet’) is not null BEGIN SELECT ‘database does exist’ END ELSE BEGIN select ‘database does not exist’ END [/cc]
Continue reading ...
This error message is relatively straight forward. The way it normally happens is when you are trying to insert data from a table that contains values that have larger data lengths than the table you are trying to insert into. An example of this would be trying to insert data from a permanent table, into a temporary table that has data types defined at a fixed length. In order to illustrate the example, we’ll create two temporary tables below and try to insert a value that will not fit into the second temporary table. [cc lang=”sql”] — First create the table that will hold the initial value CREATE TABLE #Employee ( ID int IDENTITY(1,1) ,FirstName varchar(50) ,LastName varchar(50) ) — Insert the example value INSERT INTO #Employee ( FirstName ,LastName ) SELECT FirstName = ‘Billie’ — 6 characters ,LastName = ‘Jean’ — Create the smaller table that will not fit the value CREATE TABLE #Employee_Truncate ( ID int ,FirstName varchar(5) — 5 characters ,LastName varchar(49) ) — Attempt to fit the smaller value into the table INSERT INTO #Employee_Truncate ( FirstName ,LastName ) SELECT FirstName ,LastName FROM #Employee [/cc] From this we see the following error: In order to fix the error, either the second temp table needs to have its data type expanded to be able to fit the data, or you could use the SELECT..INTO method of inserting the data. That way, no matter what size of data the source table changed to, the destination table would automatically […]
Continue reading ...
Sometimes after one or more large transactions, the t-log (transaction log) will become full. In these particular cases you may receive an error message indicating the transaction log is full. In order to alleviate this issue, you need to find the names of the transaction logs on your system and then shrink them. To find the names, execute the following statement: [cc lang=”sql”] SELECT name FROM sys.database_files WHERE TYPE = 1 [/cc] Once you’ve found the names of your transaction logs, use them in the DBCC SHRINKFILE command in order to make them smaller. DBCC SHRINKFILE(‘AdventureWorksLT2008_Log’); If your databases are in full recovery mode, then using this methodology will shrink the transaction logs while retaining full recovery mode. A riskier, yet highier yielding way to free disk space is to utilize: [cc lang=”sql”] — database name below BACKUP LOG AdventureWorksLT2008 — database name WITH TRUNCATE_ONLY — t-log name below DBCC SHRINKFILE ( ‘AdventureWorksLT2008_Log’ — transaction log name ,1 ) [/cc] While this command will shrink the transaction log to a greater degree, it will drop the previous transactions since the last T-Log backup. Essentially changing the recovery model of the database to simple since the last transaction log backup. Depending upon the backup schedule you have, this may or may not be risky. If you backup the transaction logs every hour, then the max possible data loss (should a complete loss occur) would be one hours worth of data. While the chance of this are very slim, it should be […]
Continue reading ...
