Database

-- The following wont work at all if inserted contains
-- more than one row (which it will if the statement
-- firing the trigger affects more than one row):
set @x = (select x from inserted)
if @x = 10 then set @y = 20 else set @y = 30
update target set y = @y

update target set y = (case x when 10 then 20 else 30 end)
where target.id in (select id from inserted)

<br />
<br />
SELECT P.TABLE_NAME AS ParentTable, P.CONSTRAINT_NAME AS PrimaryKey, FK.TABLE_NAME AS ChildTable, FK.CONSTRAINT_NAME AS ForeignKey <br />
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS P<br />
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME<br />
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME<br />
WHERE P.CONSTRAINT_TYPE = 'PRIMARY KEY'<br />
ORDER BY ParentTable, ChildTable<br />
<br />

soniasan wrote:

i want to copy entire data from one of my table to that of another
table. source table is created and having 1000 of rows and
destination table is not created this i want to do in a single statment.

SELECT * INTO new_table FROM old_table WHERE 1 = 2 -- creates the new table
INSERT INTO new_table SELECT * FROM old_table -- populate it

declare @a varchar(max)

select @a = replicate('a',7999) + 'b'
print @a

--prints 7999 a's and a b

select @a = replicate('x',8000) + 't'
print @a

--prints 8000 x's but no t

select count(occupants) from tablename<br />
group by [datecol],roomtype<br />
having datecol between '2008-05-01' and '2008-05-31'

insert into [Table] (Field) Values (0x1232132132)

<br />
CREATE PROCEDURE myProc<br />
  @param1 INT = NULL<br />
  @param2 VARCHAR(50) = NULL<br />
AS<br />
  SELECT * FROM [sometable] WHERE col1= COALESCE(@param1, col1) AND col2 = COALESCE(@param2, col2)<br />
GO<br />

<br />
public class DataLayer {<br />
  public DataSet GetSomeData(int param1, string param2){<br />
    DataSet myData = new DataSet();<br />
<br />
    using(DbConnection conn = GetConnectionFactoryMethod()){<br />
      using(DbCommand cmd = new DbCommand(conn, "SELECT .... FROM .... WHERE col1 = @param1 AND col2 = @param2")){<br />
         cmd.Parameters.Add("@param1", .....);<br />
         cmd.Parameters.Add("@param2", .....);<br />
<br />
         cmd.Parameters["@param1"].Value = param1;<br />
         cmd.Parameters["@param2"].Value = param2;<br />
<br />
         DbDataAdapter da = new DbDataAdapter(cmd);<br />
         da.Fill(myData);<br />
      }<br />
    }<br />
<br />
    return myData;<br />
  }<br />
<br />
  public DataSet GetSomeDifferentData(string param1, bool param2){<br />
    // ...... implementation .....<br />
  }<br />
<br />
  private DbConnection GetConnectionFactoryMethod(){<br />
    return new SqlConnection("... connection string ...");<br />
  }<br />
}<br />

<br />
  DataLayer myDataLayer = new DataLayer();<br />
<br />
  DataSet myData = myDataLayer.GetSomeData(5, "my string");<br />
  DataSet myData2 = myDataLayer.GetSomeDifferentData("some value", true);<br />
<br />

MozhdehQeraati wrote:

if I want to take for example an exclusive lock at a row-level.