SQL - Insert Multiple Records into one tables at one time

How are you going to insert multiple records into a table at one time?

Here is the code you can do that:

Insert into Table
(Column1, Column2, Column3)

select Value01, Value02, Value03
Union ALL

select Value11, Value12, Value13
Union ALL

select Value21, Value22, Value23

It works and save time by avoiding multiple insert scripts.

calling a stored procedure from asp.net

If you would like to use the asp.net code to call a stored procedure from SQL, the following article will show you how to do it step-by-step.

http://www.macronimous.com/resources/calling_stored_procedures_from_ASP.NET_and_VB.NET.asp

SQL insert,

If you like to insert value in SQL table based on a selected data set from another table, you can use the following syntax:

Insert into table1 (Col1, Col2, col3)
select Col1, Col2, col3 from table2 where Col1 > 'abcde'

common table expression (CTE) in SQL

Have you ever used the Common Table Expression in SQL query scripts? It is very useful feature. CTE make the scripts easier to read and maintain. And it is also easier to write sql scripts with CTE for complex queries. CTEs can reference themselves.

With CTE_Table as
(
select column1, column2, column3 ... from table1 join table2 on column1 = column3
)

select *, table3.column1 from CTE_Table join Table3 on .........................


The script is very clean and simple

SQL functions - UNION, EXCEPT, INTERSECT

The three functions in SQL 2005 cam combine the data from two or more tables and display them in one dataset.

Union (all) - union two or more table data together, by default it will only show the DISTINCT row data unless the "all" option is used

EXCEPT - pick the data from first table but the data not exist in the second table

INTERSECT - Pick the data rows only exist in the first table and the second table.

EXCEPT and INTERSECT function can be implemented by using the join function in SQL queries. but these two features are sometime more simplified and the code is easier to read.

How to delete duplicate record in SQL Database

After you identify duplicate records in a table in SQL database, the next step is how to remove duplicate records.

One way to do this is to copy unique records into temporary table, then delete the original table and then copy the temporary table back.

The below is a web link about how to delete duplicate record.

Deleting Duplicate Records

If you have other ways to remove duplicate records, please share it by leaving comments.

Duplicate Record in SQL

How do you find duplicate records in a table in SQL database, here is a script

Select First_Name, Last_Name, Email, Phone, count(*)
from Table_ABC
having count(*) > 1
group by First_Name, Last_Name, Email, Phone

How to update record in a table based on values in another table

Update Table_ABC
set First_Name = (select FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)
where exists (select FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)

If the table_BCD have duplicate emails, you can pick the first one by using the following script.

Update Table_ABC
set First_Name = (select top(1) FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)
where exists (select top(1) FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)

I found these scripts are very useful.

SQL code

Have you ever had the need to list all the column names in a table in SQL database, do you know how to do it. Here is the code:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE_NAME'