Bulk repopulation of a table

Hi, I have a table T with 3 int columns, I need to update it every X
hours with a query that returns the same 3 int columns. Currently I'm
doing it using a cursor iterating over each row and checking whether I
need to DELETE/UPDATE/INSERT it, this however isn't very efficient.
So I tested 2 additional methods, one involving deleting all the rows
from the target table and then inserting everything and the other by
dropping the table, recreating and inseting.
The results are not very surprising:
PROC,CPU,READS,WRITES,DURATION
DROP,2720,177757,218,4000
DELE,3150,183060,230,5300
CURS,8200,504818,247,8240
Which indicates that DROPing and reCREATing is the way to go, any
suggestions?
I don't like the DROP/CREATE scheme since it might involve the TABLEs
downtime (even when I CREATE a T1 then DROP T and sp_rename T1->T). If
there is no better way, will there be problems due to sp_rename
changing the name but not the ID of an object?
P.S. it's SQL2005, and the query itself executes in (2300,11596,0,2730)
[same column names as above].
Thanks.Can you give some more information so we can provide a more realistic
answer? The DDL for the existing table would help along with how or where
you are getting the data to update the table with. If the new data is a
complete up to date set then you have several options. I am assuming it is a
flat file but since you didn't provide that information it is only a guess.
I would look at using BULK INSERT to load the new data into a table that you
create. Lets call it X. Then you can prep this table to get it exactly like
you want it to be, drop the original table and rename X to what the old one
was. You may find that renaming the old one first, renaming X and then
dropping might yield the least time that the original table is off-line to
the users. But either way it should be a matter of a second or less.
Andrew J. Kelly SQL MVP
<johnsolver@.gmail.com> wrote in message
news:1137942204.960433.107600@.g49g2000cwa.googlegroups.com...
> Hi, I have a table T with 3 int columns, I need to update it every X
> hours with a query that returns the same 3 int columns. Currently I'm
> doing it using a cursor iterating over each row and checking whether I
> need to DELETE/UPDATE/INSERT it, this however isn't very efficient.
> So I tested 2 additional methods, one involving deleting all the rows
> from the target table and then inserting everything and the other by
> dropping the table, recreating and inseting.
> The results are not very surprising:
> PROC,CPU,READS,WRITES,DURATION
> DROP,2720,177757,218,4000
> DELE,3150,183060,230,5300
> CURS,8200,504818,247,8240
> Which indicates that DROPing and reCREATing is the way to go, any
> suggestions?
> I don't like the DROP/CREATE scheme since it might involve the TABLEs
> downtime (even when I CREATE a T1 then DROP T and sp_rename T1->T). If
> there is no better way, will there be problems due to sp_rename
> changing the name but not the ID of an object?
> P.S. it's SQL2005, and the query itself executes in (2300,11596,0,2730)
> [same column names as above].
> Thanks.
>|||Instead of deleting all rows from the table you can truncate it. Its not
a good idea to drop and recreate table.
Or
you can first store the data in one temporary and then update related
data from temporary table in permanent table using TSQL.
Please post data and how you want to update it.
Regards
Amish Shah
*** Sent via Developersdex http://www.examnotes.net ***|||Hi, sorry for not providing enough info:
the table:
CREATE TABLE [dbo].[T](
[tid] [int] NOT NULL,
[pid] [int] NOT NULL DEFAULT (0),
[r] [int] NOT NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
[t] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
The query returns the exact 3 columns, simplistically: SELECT tid,pid,r
FROM A (it returns ~90k rows) I don't think that the exact query
matters... I'll be happy to provide additional info if needed.
Thanks.|||As to updating data, assuming T contains the following:
tid,pid,r
1,3,6
2,3,40
5,4,60
and the query (Q) returns
tid,pid,r
1,3,50
8,1,1000
Then I would like T to contain exactly what Q returned, namely:
DELETE FROM T WHERE tid=2
DELETE FROM T WHERE tid=5
UPDATE T SET r=50 WHERE tid=1
INSERT INTO T (tid,pid,r) VALUES(8,1,1000)
etc.|||If you are updating the value for r in table T, based on a match of both tid
and pid from the source table, then
--create a temporary table that has the data to be imported.
SELECT tid, pid, r INTO #importtable FROM (...the rest of your import query
...)
--Delete records from T that aren't in the new import batch
DELETE FROM T WHERE NOT EXISTS
(SELECT i.tid FROM #importtable i WHERE i.tid = T.tid)
--update the records that have matching tid and pid values
UPDATE T SET r = i.r FROM #importtable i
INNER JOIN T ON T.tid=i.tid AND T.pid = i.pid
--INSERT new records into T that didn't exist before
INSERT INTO T
SELECT tid,pid,r FROM #importtable i
WHERE NOT EXISTS
(SELECT tid FROM T WHERE T.tid = i.tid)
This gets more complicated if the PK on T is composite; I couldn't really
tell from the DDL you posted earlier.
Of course, it may be faster to just truncate the table and import in the new
data, as suggested earlier by Amish.
Another suggestion is to encapsulate the above into an INSTEAD OF trigger,
and then you could simply run
INSERT INTO T SELECT ...rest of your import query here ...
The INSTEAD OF trigger would fire before any primary key constraints would
be checked, so it could acheive what you want.
"johnsolver@.gmail.com" wrote:

> As to updating data, assuming T contains the following:
> tid,pid,r
> 1,3,6
> 2,3,40
> 5,4,60
> and the query (Q) returns
> tid,pid,r
> 1,3,50
> 8,1,1000
> Then I would like T to contain exactly what Q returned, namely:
> DELETE FROM T WHERE tid=2
> DELETE FROM T WHERE tid=5
> UPDATE T SET r=50 WHERE tid=1
> INSERT INTO T (tid,pid,r) VALUES(8,1,1000)
> etc.
>|||Thanks for the reply Mark,
I've tried your suggestion, performance-wise it's better than the
CURSOR solution (not surprising) but it's not as good as DROP or
TRUNCATE approaches. The key isn't a composite it's only one column as
you guessed, tid.
Amish: Why isn't it a good idea to DROP/CREATE the table? (except for
the minimal downtime due to sp_rename)?
Thanks.|||Johnsol
I also suggested you
you can first store the data in one temporary and then update related
data from temporary table in permanent table using TSQL.
But I was unable to give solution untill go post DDL.
Second
when you change the table name causes the sp, view and references
invalid which uses this table until you recreate new table with old
name.
If you drop and recreate table you have to recreate all relations ,
indexes again. You can not guarantee referential integrity of data
of othe tables if this table is part of any foregin key relationship
with them, since this table drops many times.
In some cases we have seen that updating data in the table was not
possible using TSQL easily and you have to create large number of temp
tables and check number of conditions and check data from number of
tables.
So I have gave you all the options but untill I get the data I was not
able to give you some solution.
Regards
Amish Shah|||Thanks for the reply Amish,
in the end I'll probably go with CREATE and DROP simply because it's
performance is about 1.5 times faster than the temp table solution, I
don't have any foreign keys/views built on the table.
So I've opted for the following (DDL follows), btw. should I wrap the
whole sequence in BEGIN TRANS ... COMIT TRANS?
CREATE TABLE [dbo].[T_1](
[tid] [int] NOT NULL,
[pid] [int] NOT NULL DEFAULT ((0)),
[r] [int] NOT NULL,
CONSTRAINT [PK_T_1] PRIMARY KEY CLUSTERED
(
[tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO T_1 SELECT tid,pid,r FROM A;
DROP TABLE T;
EXEC sp_rename 'T_1','T';
EXEC sp_rename 'T.PK_T_1','PK_T','INDEX';|||Yes at least you should do all in transaction.
Regards
Amish Shah

BULK INSERT stop inserting rows

All,

I am just having this weird issue recently with the BULK INSERT command on one of our SQL2K servers which always returns the message "command(s) completed successfully" (Query Analyzer) but no rows get actually inserted into the bulk table.

BULK INSERT LOADTMP FROM 'D:\TEMP\MYFILE.CSV' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 20)

> The command(s) completed successfully.

The same BULK INSERT command used to work for the past year or so and still works just fine on the other servers but for some unknown reasons, it just stopped working on this one box while always returning the message: "the command(s) completed successfully"...

I did try to provide some bogus non-existent filename in the BULK INSERT command and it would also return "the command(s) completed successfully" as well - as long as the entire BULK INSERT command syntax and arguments are correct !!!

I thought that I found some kind of solution in the msdn knowledge database related to the issue by re-applying the SQL2K service pack 4 but it didn't fix it. Rebooting the instance and the server several times did not help either.

Have any of you had encountered similar issues before and how to resolve it? Would appreciate all you inputs...

Thanks,
JohnSince you have "MAXERRORS = 20" in your statement, it returns success unless 21 errors occur.

Try setting maxerrors = 1 and see what message you get.

BULK INSERT & DIRTY BUFFERS

Hi all

Using SQL 2000 MSDE

I'm bulk inserting about 3.200.000 records into a table

unfortunately all memory dissapears and never returns the dirty buffers count goes up to 48000 approx.

any ideas on how to rectify this ..... ?sqlserver is designed to take up as much as memory and only releases it back if the system needs it. If you must, restarting sqlserver will force the release.
Normally, I wouldn't want to pump that much data into the db in one shot. Rather, I would do it in batches. Please take a look at -b option of bcp or ROWS_PER_BATCH of bulk insert.|||Thanks for that
I have already figured out that restarting the service will do the trick .....
but it is a live database with many users .... (they will not thank me for doing such things)
thanks anyway ... i'll keep searching

Built in report tool returns dollar symbol not pound symbol.

I can't be the only one to of noticed this but my local setting are all set to United Kingdom, but when using the in built reporting tool I always get dollar symbols.
The command I used to format the text box was the formatcurrency() express which states it will format the text in accordance with the settings in control panel. The only thing I can think of is that it is either using a system default setting (were on a domain with roaming profiles) or it uses the language as set on the SQL server (which I haven't checked yet).

Anyone else noticed this odd behavior or know where you type the expression pattern to make a custom currency format for UK?

I use a custom format entered in the Format code: box

￡#,##0.00

|||Yeah thats, the method I used in the end. Shame the formatcurrency() function doesn't do what it should out of the box.
Thank you. |||

Yep, I'd be interested to know why we can't default to local currency settings as with say Office programs...

Let me know if you find out :)

|||Yes, would be useful. I might google once I have some spare time. Just hope one of the developers of that section who might know passes over this.|||

You may want to read this: http://msdn2.microsoft.com/en-us/library/ms156493.aspx

The Report has a Language property which could be set to a static value, such as en-UK. Or you could set it to the current user's language by using an expression: =User!Language. Note: you can also override the report's language on individual textboxes by explicitly setting the textbox.Language property.

-- Robert

|||I can confirm that setting the language of the report does in fact fix the local symbol's. I never noticed this property as it is tucked away. To get to it you have to select it from the drop down menu in properties ( http://www.devstuff.eu/images/stories/msdn/ReportSelecter.JPG ). Then you can set report wide settings.

Interesting points about this :
Text boxes local settings does not override form default.|||Thanks guys.

Building a table from SQL Query

I am hoping someone can point me in the right direction with this.

I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:

==============================
Column Name || Column Value
------||------
Column Name || Column Value
------||------

I hope I have explained myself properly. Any help would be greatly appreciated.

Do you want to transpose your data from row to column?

If this is what you want, it depends on which version of SQL server you are using. In SQL Server 2005, there is a PIVOT function which does this knid of job. In 2000, you can use CASE statement to construct a customized solution.

If you need help on this, you can post some sample data for others to look at. Plus the final result you are expecting.

|||Unless I misunderstood, PIVOT is not quite what I am looking for.

What I need is to take the name of the column that a field is in and use that as a header for table.

Any help would be greatly appreciated.

GKC|||

Could you test this out: (instead of PIVOT, use UNPIVOT)

step 1:

create table usingunpivot2005
(
myID varchar(10) primary key,
col1 varchar(50),
col2 varchar(50),
col3 varchar(50)
)
insert into usingunpivot2005 (myID, col1, col2, col3)
values('myid01','40','20','30')

Step 2:

with switchCTE as(
select cast(colname as varchar(5)) as colname, value
from usingunpivot2005 p

UNPIVOT
(value for colname in (col1, col2, col3)) as unpvt)
select *
from switchCTE

You should know your columns' name. If the value in that column is NULL, that column will be skipped.

|||SELECT ColName, MIN(CASE WHEN P.myID = 'myid01' THEN col1 END) AS Value
FROM
(SELECT 'Col 1' as ColName, myID, col1 FROM usingunpivot2005
UNION
SELECT 'Col 2', myID, col2 FROM usingunpivot2005
UNION
SELECT 'Col 3', myID, col3 FROM usingunpivot2005) P
GROUP BY ColName
ORDER BY ColName

Building a date in SQL

Is there any sql method that takes 3 parameter like, day, month and year . And return me the date.

For example

function(10,3,2007) and it returns 10-03-2007

Thanks,

There is no such function as part of SQL2000 or SQL2005, however you could write your own function. You refer to the function returning 10-03-2007 as an example, however is it as string (char(10)) or as datetime? Also what datatype are the input parameters - integer?

|||

I have find the solution.

Thanks