How To Use BulkCopy To Insert A DataTable In C#



TODO:

Have you ever wanted to insert the contents of a DataTable efficiently using SqlClient.BulkCopy?

 

SOLUTION:

public static void FillMonteCarloSimulationResults(DataTable InDataTable)
{
     using (SqlBulkCopy bulkCopy = new SqlBulkCopy("myconnectionstring")
     {
          //***NOW Do column mappings.  This maps columns from DataTable (left) to DB Table (right)
          bulkCopy.DestinationTableName = "Results";
          bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
          bulkCopy.ColumnMappings.Add("Name", "Name");
          bulkCopy.WriteToServer(InDataTable);            
     }
}

 

NOTES:

This example will take the DataTable, map the columns, then preform BulkCopy

How To Attach A SQL Server Database MDF File Without A Log File



TODO:

Recently I had a corrupt log file, and my database was continuously 'Suspect'.  Since this was a test database, I did not care about my corrupt .ldf file.  Therefore, I simply wanted to attach my .mdf file and let it create a new .ldf file.  The SQL below will do just that.

 

SOLUTION:

CREATE DATABASE myDatabase ON
( FILENAME = N'G:\DATA\myDatabase.mdf')
FOR ATTACH
GO

 

NOTES:

This method works when there is only one log file and it is missing.

IF there is more than 1 log file, use 'FOR ATTACH_REBUILD_LOG' rather than 'FOR ATTACH'

 

How To Fix A Database Stuck In [Recovery Pending] Status In SQL Server Management Studio



TODO:

SQL Server Management Studio reports a database in "Recovery Pending" status. 

 

SOLUTION:

use master

ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE YourDatabase SET ONLINE WITH ROLLBACK IMMEDIATE


NOTES:

There are no notes on this topic.

How To INIT CAP Data In T-SQL



TODO:

Have you ever wanted to convert the data in a column to INIT CAP?  For example you have data 'TEST DATA' and you want to change it to 'Test Data'?

 

SOLUTION:

 

CREATE FUNCTION dbo.fCapFirst(@pcIn NVARCHAR(4000))
RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @i		INT
,  @cWork		NVARCHAR(4000)

SET @cWork = Upper(Left(@pcIn, 1)) + Lower(SubString(@pcIn, 2, 4000))

SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
,  @cWork COLLATE Latin1_General_BIN)

WHILE 0 < @i
   BEGIN
      SET @cWork = Left(@cWork, @i) 
+        Upper(SubString(@cWork, 1 + @i, 1)) + SubString(@cWork, 2 + @i, 4000)
      SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
,        @cWork COLLATE Latin1_General_BIN)
   END

RETURN @cWork
END
GO

 

NOTES:

This solution was posted by user Pat Phelan on this site. 

To run issue this command:  update MyTable set Mycolumn= dbo.fCapFirst(Mycolumn)

How To Populate A Table With ISO 3166-1 Country Data



TODO:

Did you ever need a reference table of ISO country data?  Is so, create a table called 'Country', and add an identity column named 'CountryId', IsoCode, and Name, then execute this SQL. 

 

SOLUTION:

 

insert into country(IsoCode, Name) Values ('AF','AFGHANISTAN');
insert into country(IsoCode, Name) Values ('AX','åLAND ISLANDS');
insert into country(IsoCode, Name) Values ('AL','ALBANIA');
insert into country(IsoCode, Name) Values ('DZ','ALGERIA');
insert into country(IsoCode, Name) Values ('AS','AMERICAN SAMOA');
insert into country(IsoCode, Name) Values ('AD','ANDORRA');
insert into country(IsoCode, Name) Values ('AO','ANGOLA');
insert into country(IsoCode, Name) Values ('AI','ANGUILLA');
insert into country(IsoCode, Name) Values ('AQ','ANTARCTICA');
insert into country(IsoCode, Name) Values ('AG','ANTIGUA AND BARBUDA');
insert into country(IsoCode, Name) Values ('AR','ARGENTINA');
insert into country(IsoCode, Name) Values ('AM','ARMENIA');
insert into country(IsoCode, Name) Values ('AW','ARUBA');
insert into country(IsoCode, Name) Values ('AU','AUSTRALIA');
insert into country(IsoCode, Name) Values ('AT','AUSTRIA');
insert into country(IsoCode, Name) Values ('AZ','AZERBAIJAN');
insert into country(IsoCode, Name) Values ('BS','BAHAMAS');
insert into country(IsoCode, Name) Values ('BH','BAHRAIN');
insert into country(IsoCode, Name) Values ('BD','BANGLADESH');
insert into country(IsoCode, Name) Values ('BB','BARBADOS');
insert into country(IsoCode, Name) Values ('BY','BELARUS');
insert into country(IsoCode, Name) Values ('BE','BELGIUM');
insert into country(IsoCode, Name) Values ('BZ','BELIZE');
insert into country(IsoCode, Name) Values ('BJ','BENIN');
insert into country(IsoCode, Name) Values ('BM','BERMUDA');
insert into country(IsoCode, Name) Values ('BT','BHUTAN');
insert into country(IsoCode, Name) Values ('BO','BOLIVIA');
insert into country(IsoCode, Name) Values ('BQ','BONAIRE');
insert into country(IsoCode, Name) Values ('BA','BOSNIA AND HERZEGOVINA');
insert into country(IsoCode, Name) Values ('BW','BOTSWANA');
insert into country(IsoCode, Name) Values ('BV','BOUVET ISLAND');
insert into country(IsoCode, Name) Values ('BR','BRAZIL');
insert into country(IsoCode, Name) Values ('IO','BRITISH INDIAN OCEAN TERRITORY');
insert into country(IsoCode, Name) Values ('BN','BRUNEI DARUSSALAM');
insert into country(IsoCode, Name) Values ('BG','BULGARIA');
insert into country(IsoCode, Name) Values ('BF','BURKINA FASO');
insert into country(IsoCode, Name) Values ('BI','BURUNDI');
insert into country(IsoCode, Name) Values ('KH','CAMBODIA');
insert into country(IsoCode, Name) Values ('CM','CAMEROON');
insert into country(IsoCode, Name) Values ('CA','CANADA');
insert into country(IsoCode, Name) Values ('CV','CAPE VERDE');
insert into country(IsoCode, Name) Values ('KY','CAYMAN ISLANDS');
insert into country(IsoCode, Name) Values ('CF','CENTRAL AFRICAN REPUBLIC');
insert into country(IsoCode, Name) Values ('TD','CHAD');
insert into country(IsoCode, Name) Values ('CL','CHILE');
insert into country(IsoCode, Name) Values ('CN','CHINA');
insert into country(IsoCode, Name) Values ('CX','CHRISTMAS ISLAND');
insert into country(IsoCode, Name) Values ('CC','COCOS (KEELING) ISLANDS');
insert into country(IsoCode, Name) Values ('CO','COLOMBIA');
insert into country(IsoCode, Name) Values ('KM','COMOROS');
insert into country(IsoCode, Name) Values ('CG','CONGO');
insert into country(IsoCode, Name) Values ('CD','THE DEMOCRATIC REPUBLIC OF THE CONGO');
insert into country(IsoCode, Name) Values ('CK','COOK ISLANDS');
insert into country(IsoCode, Name) Values ('CR','COSTA RICA');
insert into country(IsoCode, Name) Values ('CI','CôTE D''IVOIRE');
insert into country(IsoCode, Name) Values ('HR','CROATIA');
insert into country(IsoCode, Name) Values ('CU','CUBA');
insert into country(IsoCode, Name) Values ('CW','CURAçAO');
insert into country(IsoCode, Name) Values ('CY','CYPRUS');
insert into country(IsoCode, Name) Values ('CZ','CZECH REPUBLIC');
insert into country(IsoCode, Name) Values ('DK','DENMARK');
insert into country(IsoCode, Name) Values ('DJ','DJIBOUTI');
insert into country(IsoCode, Name) Values ('DM','DOMINICA');
insert into country(IsoCode, Name) Values ('DO','DOMINICAN REPUBLIC');
insert into country(IsoCode, Name) Values ('EC','ECUADOR');
insert into country(IsoCode, Name) Values ('EG','EGYPT');
insert into country(IsoCode, Name) Values ('SV','EL SALVADOR');
insert into country(IsoCode, Name) Values ('GQ','EQUATORIAL GUINEA');
insert into country(IsoCode, Name) Values ('ER','ERITREA');
insert into country(IsoCode, Name) Values ('EE','ESTONIA');
insert into country(IsoCode, Name) Values ('ET','ETHIOPIA');
insert into country(IsoCode, Name) Values ('FK','FALKLAND ISLANDS (MALVINAS)');
insert into country(IsoCode, Name) Values ('FO','FAROE ISLANDS');
insert into country(IsoCode, Name) Values ('FJ','FIJI');
insert into country(IsoCode, Name) Values ('FI','FINLAND');
insert into country(IsoCode, Name) Values ('FR','FRANCE');
insert into country(IsoCode, Name) Values ('GF','FRENCH GUIANA');
insert into country(IsoCode, Name) Values ('PF','FRENCH POLYNESIA');
insert into country(IsoCode, Name) Values ('TF','FRENCH SOUTHERN TERRITORIES');
insert into country(IsoCode, Name) Values ('GA','GABON');
insert into country(IsoCode, Name) Values ('GM','GAMBIA');
insert into country(IsoCode, Name) Values ('GE','GEORGIA');
insert into country(IsoCode, Name) Values ('DE','GERMANY');
insert into country(IsoCode, Name) Values ('GH','GHANA');
insert into country(IsoCode, Name) Values ('GI','GIBRALTAR');
insert into country(IsoCode, Name) Values ('GR','GREECE');
insert into country(IsoCode, Name) Values ('GL','GREENLAND');
insert into country(IsoCode, Name) Values ('GD','GRENADA');
insert into country(IsoCode, Name) Values ('GP','GUADELOUPE');
insert into country(IsoCode, Name) Values ('GU','GUAM');
insert into country(IsoCode, Name) Values ('GT','GUATEMALA');
insert into country(IsoCode, Name) Values ('GG','GUERNSEY');
insert into country(IsoCode, Name) Values ('GN','GUINEA');
insert into country(IsoCode, Name) Values ('GW','GUINEA-BISSAU');
insert into country(IsoCode, Name) Values ('GY','GUYANA');
insert into country(IsoCode, Name) Values ('HT','HAITI');
insert into country(IsoCode, Name) Values ('HM','HEARD ISLAND AND MCDONALD ISLANDS');
insert into country(IsoCode, Name) Values ('VA','HOLY SEE (VATICAN CITY STATE)');
insert into country(IsoCode, Name) Values ('HN','HONDURAS');
insert into country(IsoCode, Name) Values ('HK','HONG KONG');
insert into country(IsoCode, Name) Values ('HU','HUNGARY');
insert into country(IsoCode, Name) Values ('IS','ICELAND');
insert into country(IsoCode, Name) Values ('IN','INDIA');
insert into country(IsoCode, Name) Values ('ID','INDONESIA');
insert into country(IsoCode, Name) Values ('IR','IRAN');
insert into country(IsoCode, Name) Values ('IQ','IRAQ');
insert into country(IsoCode, Name) Values ('IE','IRELAND');
insert into country(IsoCode, Name) Values ('IM','ISLE OF MAN');
insert into country(IsoCode, Name) Values ('IL','ISRAEL');
insert into country(IsoCode, Name) Values ('IT','ITALY');
insert into country(IsoCode, Name) Values ('JM','JAMAICA');
insert into country(IsoCode, Name) Values ('JP','JAPAN');
insert into country(IsoCode, Name) Values ('JE','JERSEY');
insert into country(IsoCode, Name) Values ('JO','JORDAN');
insert into country(IsoCode, Name) Values ('KZ','KAZAKHSTAN');
insert into country(IsoCode, Name) Values ('KE','KENYA');
insert into country(IsoCode, Name) Values ('KI','KIRIBATI');
insert into country(IsoCode, Name) Values ('KP','DEMOCRATIC PEOPLE''S REPUBLIC OF KOREA');
insert into country(IsoCode, Name) Values ('KR','REPUBLIC OF KOREA');
insert into country(IsoCode, Name) Values ('KW','KUWAIT');
insert into country(IsoCode, Name) Values ('KG','KYRGYZSTAN');
insert into country(IsoCode, Name) Values ('LA','LAO PEOPLE''S DEMOCRATIC REPUBLIC');
insert into country(IsoCode, Name) Values ('LV','LATVIA');
insert into country(IsoCode, Name) Values ('LB','LEBANON');
insert into country(IsoCode, Name) Values ('LS','LESOTHO');
insert into country(IsoCode, Name) Values ('LR','LIBERIA');
insert into country(IsoCode, Name) Values ('LY','LIBYA');
insert into country(IsoCode, Name) Values ('LI','LIECHTENSTEIN');
insert into country(IsoCode, Name) Values ('LT','LITHUANIA');
insert into country(IsoCode, Name) Values ('LU','LUXEMBOURG');
insert into country(IsoCode, Name) Values ('MO','MACAO');
insert into country(IsoCode, Name) Values ('MK','MACEDONIA');
insert into country(IsoCode, Name) Values ('MG','MADAGASCAR');
insert into country(IsoCode, Name) Values ('MW','MALAWI');
insert into country(IsoCode, Name) Values ('MY','MALAYSIA');
insert into country(IsoCode, Name) Values ('MV','MALDIVES');
insert into country(IsoCode, Name) Values ('ML','MALI');
insert into country(IsoCode, Name) Values ('MT','MALTA');
insert into country(IsoCode, Name) Values ('MH','MARSHALL ISLANDS');
insert into country(IsoCode, Name) Values ('MQ','MARTINIQUE');
insert into country(IsoCode, Name) Values ('MR','MAURITANIA');
insert into country(IsoCode, Name) Values ('MU','MAURITIUS');
insert into country(IsoCode, Name) Values ('YT','MAYOTTE');
insert into country(IsoCode, Name) Values ('MX','MEXICO');
insert into country(IsoCode, Name) Values ('FM','MICRONESIA');
insert into country(IsoCode, Name) Values ('MD','MOLDOVA');
insert into country(IsoCode, Name) Values ('MC','MONACO');
insert into country(IsoCode, Name) Values ('MN','MONGOLIA');
insert into country(IsoCode, Name) Values ('ME','MONTENEGRO');
insert into country(IsoCode, Name) Values ('MS','MONTSERRAT');
insert into country(IsoCode, Name) Values ('MA','MOROCCO');
insert into country(IsoCode, Name) Values ('MZ','MOZAMBIQUE');
insert into country(IsoCode, Name) Values ('MM','MYANMAR');
insert into country(IsoCode, Name) Values ('NA','NAMIBIA');
insert into country(IsoCode, Name) Values ('NR','NAURU');
insert into country(IsoCode, Name) Values ('NP','NEPAL');
insert into country(IsoCode, Name) Values ('NL','NETHERLANDS');
insert into country(IsoCode, Name) Values ('NC','NEW CALEDONIA');
insert into country(IsoCode, Name) Values ('NZ','NEW ZEALAND');
insert into country(IsoCode, Name) Values ('NI','NICARAGUA');
insert into country(IsoCode, Name) Values ('NE','NIGER');
insert into country(IsoCode, Name) Values ('NG','NIGERIA');
insert into country(IsoCode, Name) Values ('NU','NIUE');
insert into country(IsoCode, Name) Values ('NF','NORFOLK ISLAND');
insert into country(IsoCode, Name) Values ('MP','NORTHERN MARIANA ISLANDS');
insert into country(IsoCode, Name) Values ('NO','NORWAY');
insert into country(IsoCode, Name) Values ('OM','OMAN');
insert into country(IsoCode, Name) Values ('PK','PAKISTAN');
insert into country(IsoCode, Name) Values ('PW','PALAU');
insert into country(IsoCode, Name) Values ('PS','PALESTINIAN TERRITORY');
insert into country(IsoCode, Name) Values ('PA','PANAMA');
insert into country(IsoCode, Name) Values ('PG','PAPUA NEW GUINEA');
insert into country(IsoCode, Name) Values ('PY','PARAGUAY');
insert into country(IsoCode, Name) Values ('PE','PERU');
insert into country(IsoCode, Name) Values ('PH','PHILIPPINES');
insert into country(IsoCode, Name) Values ('PN','PITCAIRN');
insert into country(IsoCode, Name) Values ('PL','POLAND');
insert into country(IsoCode, Name) Values ('PT','PORTUGAL');
insert into country(IsoCode, Name) Values ('PR','PUERTO RICO');
insert into country(IsoCode, Name) Values ('QA','QATAR');
insert into country(IsoCode, Name) Values ('RE','RéUNION');
insert into country(IsoCode, Name) Values ('RO','ROMANIA');
insert into country(IsoCode, Name) Values ('RU','RUSSIAN FEDERATION');
insert into country(IsoCode, Name) Values ('RW','RWANDA');
insert into country(IsoCode, Name) Values ('BL','SAINT BARTHéLEMY');
insert into country(IsoCode, Name) Values ('SH','SAINT HELENA');
insert into country(IsoCode, Name) Values ('KN','SAINT KITTS AND NEVIS');
insert into country(IsoCode, Name) Values ('LC','SAINT LUCIA');
insert into country(IsoCode, Name) Values ('MF','SAINT MARTIN (FRENCH PART)');
insert into country(IsoCode, Name) Values ('PM','SAINT PIERRE AND MIQUELON');
insert into country(IsoCode, Name) Values ('VC','SAINT VINCENT AND THE GRENADINES');
insert into country(IsoCode, Name) Values ('WS','SAMOA');
insert into country(IsoCode, Name) Values ('SM','SAN MARINO');
insert into country(IsoCode, Name) Values ('ST','SAO TOME AND PRINCIPE');
insert into country(IsoCode, Name) Values ('SA','SAUDI ARABIA');
insert into country(IsoCode, Name) Values ('SN','SENEGAL');
insert into country(IsoCode, Name) Values ('RS','SERBIA');
insert into country(IsoCode, Name) Values ('SC','SEYCHELLES');
insert into country(IsoCode, Name) Values ('SL','SIERRA LEONE');
insert into country(IsoCode, Name) Values ('SG','SINGAPORE');
insert into country(IsoCode, Name) Values ('SX','SINT MAARTEN (DUTCH PART)');
insert into country(IsoCode, Name) Values ('SK','SLOVAKIA');
insert into country(IsoCode, Name) Values ('SI','SLOVENIA');
insert into country(IsoCode, Name) Values ('SB','SOLOMON ISLANDS');
insert into country(IsoCode, Name) Values ('SO','SOMALIA');
insert into country(IsoCode, Name) Values ('ZA','SOUTH AFRICA');
insert into country(IsoCode, Name) Values ('GS','SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS');
insert into country(IsoCode, Name) Values ('SS','SOUTH SUDAN');
insert into country(IsoCode, Name) Values ('ES','SPAIN');
insert into country(IsoCode, Name) Values ('LK','SRI LANKA');
insert into country(IsoCode, Name) Values ('SD','SUDAN');
insert into country(IsoCode, Name) Values ('SR','SURINAME');
insert into country(IsoCode, Name) Values ('SJ','SVALBARD AND JAN MAYEN');
insert into country(IsoCode, Name) Values ('SZ','SWAZILAND');
insert into country(IsoCode, Name) Values ('SE','SWEDEN');
insert into country(IsoCode, Name) Values ('CH','SWITZERLAND');
insert into country(IsoCode, Name) Values ('SY','SYRIAN ARAB REPUBLIC');
insert into country(IsoCode, Name) Values ('TW','TAIWAN');
insert into country(IsoCode, Name) Values ('TJ','TAJIKISTAN');
insert into country(IsoCode, Name) Values ('TZ','TANZANIA');
insert into country(IsoCode, Name) Values ('TH','THAILAND');
insert into country(IsoCode, Name) Values ('TL','TIMOR-LESTE');
insert into country(IsoCode, Name) Values ('TG','TOGO');
insert into country(IsoCode, Name) Values ('TK','TOKELAU');
insert into country(IsoCode, Name) Values ('TO','TONGA');
insert into country(IsoCode, Name) Values ('TT','TRINIDAD AND TOBAGO');
insert into country(IsoCode, Name) Values ('TN','TUNISIA');
insert into country(IsoCode, Name) Values ('TR','TURKEY');
insert into country(IsoCode, Name) Values ('TM','TURKMENISTAN');
insert into country(IsoCode, Name) Values ('TC','TURKS AND CAICOS ISLANDS');
insert into country(IsoCode, Name) Values ('TV','TUVALU');
insert into country(IsoCode, Name) Values ('UG','UGANDA');
insert into country(IsoCode, Name) Values ('UA','UKRAINE');
insert into country(IsoCode, Name) Values ('AE','UNITED ARAB EMIRATES');
insert into country(IsoCode, Name) Values ('GB','UNITED KINGDOM');
insert into country(IsoCode, Name) Values ('US','UNITED STATES');
insert into country(IsoCode, Name) Values ('UM','UNITED STATES MINOR OUTLYING ISLANDS');
insert into country(IsoCode, Name) Values ('UY','URUGUAY');
insert into country(IsoCode, Name) Values ('UZ','UZBEKISTAN');
insert into country(IsoCode, Name) Values ('VU','VANUATU');
insert into country(IsoCode, Name) Values ('VE','VENEZUELA');
insert into country(IsoCode, Name) Values ('VN','VIET NAM');
insert into country(IsoCode, Name) Values ('VG','BRITISH VIRGIN ISLANDS');
insert into country(IsoCode, Name) Values ('VI','U.S. VIRGIN ISLANDS');
insert into country(IsoCode, Name) Values ('WF','WALLIS AND FUTUNA');
insert into country(IsoCode, Name) Values ('EH','WESTERN SAHARA');
insert into country(IsoCode, Name) Values ('YE','YEMEN');
insert into country(IsoCode, Name) Values ('ZM','ZAMBIA');
insert into country(IsoCode, Name) Values ('ZW','ZIMBABWE');

 

NOTES:

This solution assumes you have a table name 'Country', with columns of 'CountryId (identity), Name, and IsoCode

How To Insert Data From A Select



TODO:

Have you ever wanted to insert data by selecting values from a table?


SOLUTION:

Consider we have a table called XMLData, and it has a column called Id, Type, RawData

 

INSERT INTO XMLData (RawType, RawData)
SELECT 'Client', RawData FROM XMLData WHERE RawType = 'Customer'

 

NOTES:

The example above will copy records flagged as RawType = 'Customer', and insert them as new records, changing the RawType to 'Client'

How To Reseed An Identity In SQL Server Using T-SQL



TODO:

Have you ever wanted to reseed an Identity column in your database?

 

SOLUTION:

DBCC CHECKIDENT (yourtablename, reseed, 1)

 

NOTES:

The above command will reset the identity to start at 1.  You can set that to any value you would like.

How To List All User Tables In SQL Server



TODO:

Have you ever wanted to list all tables that were user created using T-SQL?

 

SOLUTION:

 

SELECT name FROM SYS.OBJECTS WHERE TYPE='U' order by name

 

 

NOTES:

There are no notes on this topic

How To Script All Indexes Of A Table Using SQL Server



TODO:

Have you ever wanted to script all indexes for a particular table in SQL Server?

 

SOLUTION:

The solution is simple and requires no code.

  1. Open SQL Server Management Studio
  2. Click "Tools"
  3. Click "Options"
  4. Expand "SQL Server Object Explorer"
  5. Click "Scripting"
  6. Scroll to "Table And View Options"
  7. Click "Script Indexes" and set it to "True"
  8. Now right click on your Table in Object  Explorer
  9. Now Click "Script Table As" -> "CREATE TO" => "New Query Editor Window"
  10. All indexes for the table will be in the script generated.

 

NOTES:

There are no notes on this topic.

How To Create A Table From A SELECT Statement



TODO:

Have you ever wanted to create a table from the results of a SELECT statement?

 

SOLUTION:

 

select * into newtable_tmp from oldtable

 

 

NOTES:

This statement will select all columns and records from the table "oldtable", and create a new table called "newtable_tmp" that contains the results of the SELECT statement.