I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing …
Tag: sql-server-2005
How can I do an UPDATE statement with JOIN in SQL Server?
I need to update this table in SQL Server with data from its ‘parent’ table, see below: Table: sale Table: ud sale.assid contains the correct value to update ud.assid. What query will do this? I’m thinking of a join but I’m not sure if it’s possible. Answer Syntax strictly depend…
SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a ‘running total’ into a third temp table which is created using CREATE. Then this r…
SQL Server table creation date query
How can I get the table creation date of a MS SQL table using a SQL query? I could not see any table physically but I can query that particular table. Answer For 2005 up, you can use I think for 2000, you need to have enabled auditing.
Column name or number of supplied values does not match table definition
In SQL server, I am trying to insert values from one table to another by using the below query: delete from tblTable1 insert into tblTable1 select * from tblTable1_Link I am getting the following …
Calculating the closest future periodic payment date in SQL
I’m working on an application that deals with periodic payments Payments are done fortnightly i.e. payment 1: 2009-06-01 payment 2: 2009-06-15 payment 3: 2009-06-29 and now I need a SQL statement …
SQL Bulk Insert with FIRSTROW parameter skips the following line
I can’t seem to figure out how this is happening. Here’s an example of the file that I’m attempting to bulk insert into SQL server 2005: ***A NICE HEADER HERE*** 0000001234|SSNV|00013893-03JUN09 …
Delete duplicate records from a SQL table without a primary key
I have the below table with the below records in it I dont have any primary key in this table .But i have the above records in my table already. I want to remove the duplicate records which has the same value in EmpId and EmpSSN fields. Ex : Emp id 5 How can I frame a query to delete
Use bcp to import csv file to sql 2005 or 2008
I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a ‘;’ . Example CSV FILEcontents: SQL Person Table Answer I’d create a temporary table, bulk insert the …
SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE
I have a SQL 2005 database with approx 250 tables. I want to temporarily enable ON DELETE CASCADE to all of the Foreign Keys so that I can do a bulk delete easily. I then want to turn off ON DELETE CASCADE on all Foreign Keys. The only way I know of doing this, is to use Management Studio to