Error with the below script

I am performing the below operation. I am getting the error and unable to find what the error is.Could any one help me finding it. a) Check for the availability of DESTINATION data base. If it is not exist, create the data base and move the tables to the data base. b) If the table exists in the DESTINATION data base then no process required for the table.

if db_id('Destination')is null
begin
Create database Destination
select * into TabDestination from [Source].[dbo].[TabSource]
end 
else
begin
use Destination
go
if('TabDestination' in (select name from sys.objects where type = 'u'))
insert into TabDestination select * from [Source].[dbo].[TabSource]
end

I am getting fallowing error

Msg 911, Level 16, State 1, Line 8
Database 'Destination' does not exist. Make sure that the name is entered correctly.
Msg 102, Level 15, State 1, Line 3
Incorrect 开发者_StackOverflow社区syntax near 'end'.

Your problem is with the USE, from the documentation:

USE is executed at both compile and execution time...

If the database specified doesn't exist at compile time then the query will fail. You can see this by trying to run the following query:

IF 1 = 2
BEGIN
    USE NonexistantDatabase
END

This query fails despite the fact that the USE statement is never executed.

You should instead change your query to use database qualified names, for example:

INSERT INTO Destination.dbo.Table SELECT * FROM Source.dbo.Table

Few problems here:

• After Create database Destination you need to use that database before you do the select * into TabDestination... as you will create TabDestination in some other DB.
• The Go in the middle of the begin...end block won't work.
• To specify your database for the inserts to TabDesitination you'd be better to use the fully qualified name of the table than calling Use, eg Insert Destiation.dbo.TabDestination...
• You need to use If Exists (select... for the second if statement.
• Because your Database may not exists when the script compiles, a lot of the sql needs to be exec'd dynamically.

So your script could be re-written as:

if db_id('Destination')is null
begin
    Create database Destination    
    exec ('select * into Destination.dbo.TabDestination from [Source].[dbo].[TabSource]')
end 
else
begin

    if exists (select name from Destination.sys.objects where name = 'TabDestination' and type = 'u')
    insert into Destination.dbo.TabDestination select * from [Source].[dbo].[TabSource]

end

A variation on @Jon Egerton's answer, however there is one case you've neglected to cover: the database exists but the table does not.

DECLARE @sql NVARCHAR(MAX) = N'SELECT * 
    INTO Destination.dbo.TabDestination 
    FROM Source.dbo.TabSource;';

IF DB_ID('Destination') IS NULL
BEGIN
    PRINT 'Creating database...';
    CREATE DATABASE Destination;
    PRINT 'Selecting into new table...';
    EXEC sp_executeSQL @sql;
END
ELSE
BEGIN
    IF EXISTS (SELECT 1 FROM Destination.sys.tables WHERE schema_id = 1 
        AND name = N'TabDestination')
    BEGIN
        PRINT 'Inserting into existing table...';
        INSERT Destination.dbo.TabDestination SELECT * FROM Source.dbo.TabSource;
    END
    ELSE
    BEGIN
        PRINT 'Selecting into new table...';
        EXEC sp_executeSQL @sql;
    END
END

EDIT

Added PRINT statements for debugging purposes, as I suggested in the follow-up to @Jon's answer.

You just need to get rid of the GO command, its a batch separator so it breaks your begin/end. Oh and you can't use USE like that either.