SQL Queries

 create table #t(Id int,

Destination varchar(20))

insert into #t values(1, 'x'),(2,'y'), (3,'x'),(4, 'y'), (5,'x');


select * from #t

select t.id,d.Id, t.Destination from #t as t
inner join  #t as d on t.Destination = d.Destination
where t.Id  <> d.Id

          OR    

select t.id, STRING_AGG(d.Id, ',')  from #t as t
inner join  #t as d on t.Destination = d.Destination
where t.Id  <> d.Id
group by t.Id

================================

SELECT

t.NAME AS TableName,

s.Name as SchemaName,

p.rows AS RowCounts,

Sum(a.total_pages) * 8 AS TotalSpaceKb,

Sum(a.used_pages) * 8 AS UsedSpaceKb,

(Sum(a.total_pages) - Sum(a.used_pages)) * 8 as UnusedSpaceKb

FROM

SYS.TABLES t

INNER JOIN

SYS.INDEXES i on t.Object_ID = i.object_ID

INNER JOIN

Sys.partitions p on i.object_ID = p.object_ID and i.index_id = p.index_id

INNER JOIN

sys.allocation_units a on p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s on t.schema_id = s.schema_id

where

t.name not like 'dt%'

and t.is_ms_shipped = 0

and i.object_ID > 255

GROUP BY 

t.name, s.name, p.rows

order by

UsedSpaceKb DESC

====================== 

SELECT * FROM AnalyticsCx.dbo.data_Calls

INSERT INTO AnalyticsCx.dbo.data_Calls([DeviceId], [ExtNo], [CallDate], [CallTime], [RingTime], [Duration], [DestCli], [FormattedDestCli], [DestCliUnmasked], [IncomingNumber], [GroupNo], [IsExternal], [Direction], [CallOutcome], [CallReturnStatus], [ReturnDate], [ReturnRecord], [ReturnDirection], [CallId], [CallLegId], [TenantId], [PlatformId], [CallLegPosition], [RecordInsertTimestamp], [TotalDuration], [WaitTime], [FirstAnswerPoint], [TotalCallLegs], [IsCompleted], [PresenceStatusId], [IsLicensed], [RecordingLink])

SELECT [DeviceId], [ExtNo], [CallDate], [CallTime], [RingTime], [Duration], [DestCli], [FormattedDestCli], [DestCliUnmasked], [IncomingNumber], [GroupNo], [IsExternal], [Direction], [CallOutcome], [CallReturnStatus], [ReturnDate], [ReturnRecord], [ReturnDirection], [CallId], [CallLegId], [TenantId], [PlatformId], [CallLegPosition], [RecordInsertTimestamp], [TotalDuration], [WaitTime], [FirstAnswerPoint], [TotalCallLegs], [IsCompleted], [PresenceStatusId], [IsLicensed], [RecordingLink] FROM OPENQUERY([DEVSERVER], 'SELECT [DeviceId], [ExtNo], [CallDate], [CallTime], [RingTime], [Duration], [DestCli], [FormattedDestCli], [DestCliUnmasked], [IncomingNumber], [GroupNo], [IsExternal], [Direction], [CallOutcome], [CallReturnStatus], [ReturnDate], [ReturnRecord], [ReturnDirection], [CallId], [CallLegId], [TenantId], [PlatformId], [CallLegPosition], [RecordInsertTimestamp], [TotalDuration], [WaitTime], [FirstAnswerPoint], [TotalCallLegs], [IsCompleted], [PresenceStatusId], [IsLicensed], [RecordingLink] FROM [ICS360V1.0].dbo.data_Calls')

SELECT STRING_AGG(QUOTENAME(COLUMN_NAME), ', ') AS column_list

FROM (

    SELECT COLUMN_NAME

    FROM AnalyticsCx.INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'data_Calls'

      AND TABLE_SCHEMA = 'dbo'

) AS columns;

================================

-- Declare variables

DECLARE @remote_server NVARCHAR(MAX) = 'DEVSERVER';

DECLARE @remote_database NVARCHAR(MAX) = 'ICS360V1.0';

DECLARE @local_database NVARCHAR(MAX) = 'AnalyticsCx';

DECLARE @schema_name NVARCHAR(MAX) = 'dbo';

DECLARE @table_name NVARCHAR(MAX);

DECLARE @column_list NVARCHAR(MAX);

DECLARE @sql NVARCHAR(MAX);

DECLARE @identity_column BIT; -- Change to BIT for flagging existence

-- Backup local database

DECLARE @backup_path NVARCHAR(MAX) = 'D:\SQLBackups\' + @local_database + '_' + CONVERT(NVARCHAR, GETDATE(), 112) + '.bak';

SET @sql = 'BACKUP DATABASE ' + QUOTENAME(@local_database) + ' TO DISK = ''' + @backup_path + ''' WITH FORMAT, INIT, NAME = ''' + @local_database + ' Full Backup'';';

-- Print the backup SQL statement for verification

PRINT 'Executing: ' + @sql;

-- Execute the backup statement

EXEC sp_executesql @sql;

-- Cursor to iterate over all tables in the remote database

DECLARE table_cursor CURSOR FOR

SELECT TABLE_NAME

FROM [DEVSERVER].[ICS360V1.0].INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = @schema_name;

OPEN table_cursor;

-- Fetch each table name and process

FETCH NEXT FROM table_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Generate column list from the remote table with proper ordering

    WITH OrderedColumns AS (

        SELECT COLUMN_NAME

        FROM [DEVSERVER].[ICS360V1.0].INFORMATION_SCHEMA.COLUMNS

        WHERE TABLE_NAME = @table_name

          AND TABLE_SCHEMA = @schema_name

    )

    SELECT @column_list = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')

    FROM OrderedColumns;

    -- Determine if the table has an identity column

    SET @identity_column = (

        SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END

        FROM [DEVSERVER].[ICS360V1.0].INFORMATION_SCHEMA.COLUMNS

        WHERE TABLE_NAME = @table_name

          AND TABLE_SCHEMA = @schema_name

          AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

    );

    IF @identity_column = 1

    BEGIN

        -- Construct the SQL statements to enable IDENTITY_INSERT, insert data, and then disable IDENTITY_INSERT

        SET @sql = '

        SET IDENTITY_INSERT ' + QUOTENAME(@local_database) + '.' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' ON;

        INSERT INTO ' + QUOTENAME(@local_database) + '.' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' (' + @column_list + ')

        SELECT ' + @column_list + '

        FROM OPENQUERY([' + @remote_server + '], ''SELECT ' + @column_list + ' FROM ' + QUOTENAME(@remote_database) + '.' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ''');

        

        SET IDENTITY_INSERT ' + QUOTENAME(@local_database) + '.' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' OFF;';

    END

    ELSE

    BEGIN

        -- Construct the SQL statement to insert data from the remote table without IDENTITY_INSERT

        SET @sql = '

        INSERT INTO ' + QUOTENAME(@local_database) + '.' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' (' + @column_list + ')

        SELECT ' + @column_list + '

        FROM OPENQUERY([' + @remote_server + '], ''SELECT ' + @column_list + ' FROM ' + QUOTENAME(@remote_database) + '.' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ''');';

    END

    -- Print the SQL statement for verification

    PRINT 'Executing: ' + @sql;

    -- Execute the SQL statement

    EXEC sp_executesql @sql;

    -- Fetch the next table name

    FETCH NEXT FROM table_cursor INTO @table_name;

END

-- Clean up

CLOSE table_cursor;

DEALLOCATE table_cursor;

=======================

WHILE LEN(@FilteredDataCallsSql) > 0 BEGIN PRINT LEFT(@FilteredDataCallsSql, 4000); SET @FilteredDataCallsSql = SUBSTRING(@FilteredDataCallsSql, 4001, LEN(@FilteredDataCallsSql)); END