Restore a table from another table
SQL Server 2000 I backup a table like below: select * into bkp_table from src_table; How can I restore from backup table to source table? Truncate the src_table? Thanks. ...
tsql
Function to generate incrementing numbers in SQL Server
Is there any way to select from a function and have it return incrementing numbers? For example, do this: SELECT SomeColumn, IncrementingNumbersFunction() FROM SomeTable And have it return: SomeColumn | IncrementingNumbers -------------------------------- some text | 0 something | 1 foo | 2 ...
replace string in sql
i have 500 records in a table. one column holds all html data. so e.g - html> body> ... /body> /html>. What i want to do is a find and replace. i have this tag in every single record - <table id="something something" /> i want to replace that in all the rows with <table id="" /> now the difficult part is all the "something something" is...
SQL Server 2005 - Rows to Cols
I have a table CSFT_SuggestionItem with cols like: SuggestionItemID Title Description, etc. ------------------------------------------------------------ 1 Item 1 Do more work 2 Item 2 I think more is good For each SuggestionItem, there can by multiple "Applic...
How to Develop TSQL in Visual Studio 2010 Database Projects
Silly sounding question, I know... Let me lay some groundwork first. I have successfully created a database project comprised of the hundreds of tables, stored procedures, indexes, et.al. that make up our production database. I have successfully added the solution to source control (TFS). I have made a change (as a test) to some of th...
Control store procedure outputs
I have the following store proc SET @sql = 'RESTORE DATABASE ' + quotename(@dbname) + ' FROM DISK = N''E:\sql\template_' + @dbnamebak + '.bak'' WITH FILE = 1, MOVE N''FromTemplate' + @dbname + '.Data'' TO N''E:\sql\' + @dbname + '.mdf'', MOVE N''FromTemplate' + @dbname + '.Log'' TO N''E:...
Crosstab/Pivot query in TSQL on nvarchar columns
I have a Table1: ID Property 1 Name 2 City 3 Designation and Table2: ID RecordID Table1ID Value 1 1 1 David 2 1 2 Tokyo 3 2 1 Scott 4 2 3 Manager The Table1ID of Table2 maps to Table1's ID. Now I wish to show the Table1 Property column values as colum...
t-sql recurrency procedure problem
Hi. I have this code: CREATE PROCEDURE AddTask @SessionID uniqueidentifier, @Login nvarchar(max), @Uni nvarchar(max), @Time datetime, @MaxOverviewTime int, @Date datetime, @DoTaskTime int, @Priority int, @TaskID int, @TaskWillBeDoIn datetime output AS BEGIN SET NOCOUNT ON; /*Algorytm 1. Pobranie wszystkich zadań do tymczasowej tabelki ...
t-sql dynamic cursors
hi. May I use variable to declaring cursors?? I want to creating dynamic cursor, how can i do this?? Regards I have table: CREATE TABLE [UsersUniTask] ( [id] uniqueidentifier CONSTRAINT [DF_UsersUniTask_id] DEFAULT newid() NOT NULL, [userUniID] uniqueidentifier NOT NULL, [taskID] int NOT NULL, [time] datetime NOT NULL, [doT...
T-SQL 2005: combine multiple create/alter procedure calls in one transaction
Hi all, I want to build a T-SQL change script that rolls out database changes from dev to test to production. I've split the script into three parts: DDL statements changes for stored procedures (create and alter procedure) data creation and modification I want all of the changes in those three scripts to be made in a transaction. E...
T-SQL A question about inner join table variable
Hi, in my stored procedure I have a table variable contains rows ID. The are 2 scenarios - that table variable is empty and not. declare @IDTable as table ( number NUMERIC(18,0) ) In the main query, I join that table: inner join @IDTable tab on (tab.number = csr.id) BUT: as we know how inner join works, I need that my query retu...
sql server 2000 instance specific setting to cause varchar to int error
Hi I am having an inconsistent issue being produced on one of my servers whereby I have the following Select * from SomeVarcharTable v join SomeIntTable i on i.MyInt=v.MyVarchar Where v.Id = SomeID The "MyVarChar" column is surprisingly of type varchar, and "MyInt" is of type int. Whats curious is that when I run this on my develop...
Cannot insert duplicate key in object (GetReparentedValue / hierarchyid)
Hello guys/girls, Using examples I found on the web I have created a function which reparents children using the GetReparentedValue. However when I have ran the code I get the following error: Cannot insert duplicate key in object. I understand why (because I am trying to reparent the children and the new parent already has children s...
Is a dynamic pivot using LINQ possible?
I have a T-SQL 2005 query which returns: pid propertyid displayname value ----------- ----------- --------------- --------------- 14270790 74 Low Price 1.3614 14270790 75 High Price 0 14270791 74 Low Price 1.3525 14270791 75 High Price 0 14270792 74 ...
T-SQL: Creating Stored Procedure that gets Path Folder and inserts into File Path
I have a stored procedure called "sp_BulkInsert" that inserts one .csv file into my database, where you specify the full path of the file when you execute it. I am trying to create another stored procedure called "sp_ResultsDump" where you specify the folder path, which then searches the folder for all .csv files, creates a table with fi...
A column named [column name] already belongs to this DataTable
private void BindFields() { DataTable table = Globals.ConvertDataReaderToDataTable(DataProvider.GetFields()); _fieldCount = table.Rows.Count; dataGrid.DataSource = table; dataGrid.DataBind(); } The ConvertDataReaderToDataTable, provided by the DotNetNuke platform, throws this exception : A column named [column name...
determining histogram bin size
I'm looking to create a histogram in SQL (which in itself isn't too tricky), but what I'm looking for is a way of splitting the bins so that each bin / band has the same proportion of the data included within. For example if I have the sample data (the value column) and I want to divide it into 5 bins, I know that I can work out the num...
TableCardinality
Hi, in MS SQL 2010 What does it means TableCardinality in execution plan? I am looking at data base tuning performances Thanks ...
I need to improve my T-SQL query so that i can return the sum of columns
Here is my current query: SELECT patron_name, producer.federal_number, hst_number, average_bf_test, (SELECT MAX(s.statement_number) FROM statement s) AS statement_number, (SELECT MAX(s.period_ending) FROM statement s) AS period_ending FROM producer JOIN producer_details ON producer.federal_number = pro...
tsql Loop with external query
I am looping through all my databases and aggregating the results into an aggregates database. In my loop I call master.dbo.xp_cmdshell osql C:\whatever.SQL As the loop progresses, the cmdshell takes longer and longer to execute. If I stop the loop and run a single aggregate for one database it executes quickly. Is there anything...