I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.
The initial solution is:
1 Create a table (table_b) which structur is as the same as table_a
2 Use BCP to import updated records into table_b
3 Remove outdated data in table_a:
delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields
4 Append updated or new data into table_a:
insert into table_a select * from table_b
As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?
I have to tables say 'employee1' and 'employee2'
employee1 has lastname
employee2 has firstname, lastname
i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday
Here was my latest attempt
;with cte as
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
[Code] ....
I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
With cte_table (columna)
As (select a from cust)
Update columna
Set a = 'hello'
Question: would a in cust be updated even though the update is to the CTE column named columna? Or would there be an error where the column name must match?
I need to update a column with AutoNumber which is depending on some other column. The output should be like following..
Obj1Obj2
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0002ijk-001
0002ijk-001
0002ijk-001
0002ijk-001
I want to Update The column Obj2 based on the values of Obj1..
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
[Code]...
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.
View 3 Replies View RelatedI receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies View RelatedI've got a table with 6 fields :
EmployeeAccess
(MasterID, LoginID, AccessID, Storage1, Storage2, Storage3)
that needs to be updated using the data in the following spreadsheet
NewEmployeeAccessData
(ID, MasterID, AccessID1, LoginID1)
There is a 1:1 relationship between the two tables..I'm trying to code a pair of update statements on the EmployeeAccess table (1 for LoginID, 1 for AccessID) with the following logic:
If LoginID is NULL, then Update LoginID with new LoginID1 value,
If LoginID is not null, and Storage1 is NULL then Update Storage1 with New LoginID1 values
If LoginID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New LoginID1 values
etc etc...
The same applies when trying to populate the AccessID column
If AccessID is NULL, then Update AccessID with new AccessID1 value,
If AccessID is not null, and Storage1 is NULL then Update Storage1 with New AccessID1 values
If AccessID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New AccessID1 values
etc etc.
I have no control over the schema of this table so I'm trying to work the logic on how to update the columns in my table only if the corresponding column data is NULL, else update the next non NULL Storage column.
Here is the sample data for table.
create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())
insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())
[Code] ...
I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.
It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.
So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.
Before the update :
empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
After the update:
empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [ServersList] AS 'ID'
FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies View RelatedHow to update column with multiline comments.
eg. I need to update Notification column in my table as below.
Exit Formalities Notification sent to:
Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
'Mailing1, Mailing2, Mailing3'
Hi ,
I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
Thanks in advance
regards,
Srinivas Govada
How to resolve the below task
create table #temp ( idx int identity(1,1), col1 int, col2 int )
Here i want a flag success or fail on basis of below conditions.
I need to take all the col1 values and then i need to compare to each other.
if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".
if all the col1 values are ok , then we need to check Col2 values same as above.
--case 1
insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522
--select * from #temp
--truncate table #temp
Because of difference in col1 values . the value of flag should be fail.
--case 2
insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522
Here also the col1 is ok but col2 values have difference so it should be Fail.
Otherwise it should be success.
i made a procedure to the below output now i need to add column percentage depends on these output
block Response Heads Dept Actuals
1Sales Sales01.Sales (net of Sales Tax)1087.5999999999999
1Finance Sales02.LESS:-EXCISE DUTY 22.800000000000001
1Sales Sales03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60000000000002
3Materials & productionCOGS 05.Material Cost 376.70000000000005
(.i.e)
block Response Heads Dept Actuals percentage
2HR HR 04.Personnel Cost 170.60 170.60*100/1064.8(dept=03.Net
Sales)
3Materials & productionCOGS 05.Material Cost 376.70 376.70*100/1064.8(dept=03.Net
Sales)
How to write make a query for that?
I am using SQL Server 2008. In one of my table, one column has values like
"MFY RLHH CSQÉ"
"Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"
So to fetch the data having only special characters in it, I used below query
Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].
Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e
select * from (select table1.theSql,table1.userid from table1 ) as a
inner join (execute a.thesql) as b
on a.userId=b.userid
I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
Does null column take storage in table ? I created the following table:
drop table [dbo].[TBL_orig]
CREATE TABLE [dbo].[TBL_orig](
[1] [bigint] NOT NULL,
[2] [bigint] NULL,
[3] [bigint] NULL,
[4] [bigint] NULL,
[5] [float] NULL,
[Code] ....
Does null value will take a place even no data insert to this columns and if yes this answer is 4 mb is logical gap ?
Hi,
I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:
ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)
which executed OK. When I went to synchronize the db on the mobile device I got the following error:
Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]
Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?
Regards,
Greg
We have a requirement to add a not null column to an existing table.
I created the column with null and updated with single quotes, and then converted the column into not null.
Now, I can see in the publisher, it went fine. but in the subscribers, i'm getting the error as, can not insert null value. I then tried to make column as null to resolve the issue, but still it is holding the same error and not proceeding further.
Now I even dropped the column. still same issue. how can I resolve this issue? I'm using sql server 2008 R2 with SP3. replication type is Merge replication.
I'm new to replication and am trying to determine the best approach to add a column (NOT NULL with no DEFAULT) to a replicated table. The only success I have had is if I do the following:
Delete entire Subscription
Delete entire Publication
Add column to table
Create new Publication
Create new Subscription
Run SnapShot
The problem with this approach is that each step affects the entire database and not just the modified table. I think it is inefficient to redo replication for a simple object change. What am I missing? Is there a way to only replicate the changes made to the one table without having to run a SnapShot for the entire publication?
Keep in mind the column must be defined as NOT NULL and cannot have a Default.
Thanks, Dave
I am trying to drop the "allow nulls" characteristics on an existing table
column. I know that there are not Nulls currently in this column, nor will
there ever be. How do I get rid of that "allow nulls" checkmark on an
existing table structure? Thanks, Craig.
hello, i'm using sql200 and i am attempting to create a table that has an hourly-incrementing 'Date_Time' column, with a corresponding 'Total' column (which keeps a running total of values off of another table) . The code I am using right now is...
declare @date as smalldatetime
set @date = dateadd(yy, -1, cast(convert(char(11), current_timestamp, 101) + '00:00:00' as smalldatetime))
select dateadd(hh, i, @date) as Date_Time, sum(Subtotal) as Total
into #POGtable
from Pivot, OrderGroup
where
i between 0 and 24 and
CreationDate between @date and dateadd(hh, i, @date)
group by i
select dateadd(hh, i, @date) as Date_Time, 0 as Total
into #Ptable
from Pivot
where i between 0 and 24
group by i
select *
from #POGtable
union
select * from #Ptable p
where not exists(
select * from #POGtable pog
where p.Date_Time >= pog.Date_Time)
the solution is ugly, but the problem i'm having is that values for 'SubTotal' don't usually appear before 8 or 9 am. what you see above is me getting all the times (hours) that a subtotal present, creating another table with every possible hour in it (and with a 'Total' column as just zero), and then combining the two tables to create one flowing table over a 24-hour period.
there has GOT to be a better way to do this; the main point being that i want the sum( ) function to start adding up values immediately so i don't have to union two tables
Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one emptyexisting table!That is, A is the existing table and it is emtpy, I want to add one NOTNULL COLUMN (col_new) to A using following T-SQL statement, then itwill fail.ALTER TABLE A ADDcol_new varchar(600) NOT NULLGOYou should change it to these statements in SQLServer2000:ALTER TABLE A ADDcol_new varchar(600) NULLALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULLGOah, ridiculous! right?Fortunately, this stupid behavior is changed in SQLServer2005. Thefirst T-SQL statements works.
View 1 Replies View RelatedI am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)