Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Check if table exist and if it doesn't exist create table in sql server 2008 database?

Yo can check if table exits or not using sys.tables and NOT EXISTS keyword.

Example:

IF NOT EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'Products' )
 
--IF not exist create new one
CREATE TABLE [dbo].[Products](
            [ProductID] [int] IDENTITY(1,1) NOT NULL,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            [CategoryID] [int] NULL
            )
 
Print('Table Products Created')

 

Insert into select statement

Insert into select statement:

With SQL you can copy data from one table and insert into another table using SQL Insert into select statement.

Syntax:

If using different table with same columns

INSERT INTO FirstTable
SELECT * FROM SecondTable;

Different table with some same columns only

INSERT INTO FirstTable
(column_name(s))
SELECT column_name(s)
FROM SecondTable;

You can use same table also

INSERT INTO FirstTable
SELECT * FROM FirstTable;
OR
INSERT INTO FirstTable
(column_name(s))
SELECT column_name(s)
FROM FirstTable;


Example:

How to Insert duplicate records from same table ?

Below example explain how I can insert duplicate records from same table,my requirment is I want to duplicate product with different countryid same discount price for country india.

INSERT INTO ProductDiscounts ([ProductId],
             [CountryId] ,
            [DiscountPrice],
            [TaxRate])
             
            SELECT [ProductId],
             2,
            [DiscountPrice],
            [TaxRate] FROM ProductDiscounts WHERE ProductId=(SELECT TOP 1 id FROM products WHERE
            productcode='MX-IND-001') AND
            countryId=(SELECT TOP 1 id from Country WHERE CountryCode='IN')

 

Example with different tables:

How to Insert records from one table to another table?

INSERT INTO Products ([ProductCode],
            [Type] ,
            [Description],
            [isActive])
             
            SELECT [ProductCode],
            [ProductType],
            [Description],
            [IsAvailable] FROM SellableProducts WHERE
            countryCode='IN'