Wednesday, July 9, 2008

Select Insert Into

SELECT DISTINCT d.DestinationID, d.ClientID, d.[Name], c.[Name] AS ClientName
INTO #query
FROM DestinationDetails dd
RIGHT JOIN Destinations d ON d.DestinationID = dd.DestinationID
INNER JOIN Clients c ON c.ClientID = dd.ClientID
WHERE d.ClientID = @ClientID
AND (d.[Name] LIKE @Name OR @Name IS NULL)
ORDER BY d.[Name] DESC

Later we can use that newly created temp table

declare @totalCount int
select @totalCount = count(DestinationID) from #query

SELECT *, @totalCount AS TotalCount FROM #query


----------------------------------------------------------------------

CREATE TABLE #tempTable
(
Number varchar(10),
[Name]
varchar(100),
ClientID int,
Alias
varchar(100)
)

INSERT INTO #tempTable
SELECT DISTINCT
dd.Number,
ISNULL(d.Alias, dbo.FormatNumber(dd.Number)) AS [Name],
d.ClientID,
d.Alias
FROM Destinations d
INNER JOIN DestinationDetails dd ON (dd.DestinationID = d.DestinationID)
WHERE d.UserID = @UserID

----------------------------------------------------

INSERT INTO ClientItems (ClientID, Amount, Item)
SELECT ClientID, Amount, Item
FROM Clients WHERE ClientID = 1234 AND Amount > 0

------------------------------------------------------

This will create a new table based on an existing one



SELECT *
INTO users_backup
FROM Users

No comments: