If you want to update the data in a table by getting information from another table.  You can acheive this by using an update inner join query.

For example if i wanted to update the phone numbers in my table using a list of contacts from a secondary table, the UPDATE INNER JOIN Query would be like the one below:

UPDATE c
SET c.PhoneNumber = c2.PhoneNumber
FROM Contact AS c
INNER JOIN ContactTable2 c2 on c.Id = c2.Id
WHERE DateCreated > GETDATE()-60

I have come across this error when using a Indentity In a stored procedure.

In a stored procedure you need to define the scope indentity in a variable before you can use it in a select statement

So instead of

SELECT * FROM table WHERE id = SCOPE_IDENTITY()

You need to use 

 

SET @var = SCOPE_IDENTITY();

SELECT * FROM table WHERE id = @var

 

 @@IDENTITY  will return the last indentity across the whole database whilst SCOPE_IDENTITY() will return only the indentity related to the scope of the operation

To get the column names of a table in MSSQL 

SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

This is basically the MSSQL equivalent for The MySQL Command DESCRIBE TableName which displays the table details

 

I often use this so i can copy and paste into another query etc.

SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

then i have the commas for use in any INSERT or SELECT etc

 

 

USING A CUSROR

DECLARE @FirstName varchar(255), @LastName varchar(255)

DECLARE user_cursor CURSOR FOR

SELECT FirstName,LastName FROM Users

OPEN user_cursor

FETCH NEXT FROM user_cursor

INTO  @FirstName, @LastName

WHILE @@FETCH_STATUS = 0

BEGIN

print @FirstName

FETCH NEXT FROM user_cursor INTO  @FirstName, @LastName

END

CLOSE user_cursor

DEALLOCATE user_cursor

 

USING A TEMPORARY TABLE/VARIABLE

SET NOCOUNT ON
DECLARE @Users TABLE (
 RowID int IDENTITY(1, 1),
 FirstName varchar(255),
 LastName varchar(255)
)

DECLARE @NumberOfRecords int, @RowCount int
DECLARE @FirstName varchar(255)

INSERT INTO @Users(FirstName,LastName)
SELECT FirstName,LastName FROM Users

SET @NumberOfRecords = @@ROWCOUNT
SET @RowCount = 1


WHILE @RowCount <= @NumberOfRecords
BEGIN        

  -- Do Operations

SET @RowCount = @RowCount + 1
END