Executing Large SQL Statements

Apr 15, 2004

Hello,

I am trying to import a very large amount of data into an SQL database. The data is in a format of SQL statements already - it is all in one large text file consisting of a number of CREATE TABLE X and INSERT INTO X VALUES().

The problem is that the amount of values being inserted into some of the tables is so large, that I am unable to open the .SQL file using query analyzer to run it, because the line-size limit for query analyzer is 64kb, whereas actual line-size in the file is in some cases in excess of 15MB.

I would appreciate any advice on how to get all this data into a managable format. I keep thinking that there simply has to be a way to execute these over-size SQL statements.

Thank you in advance!

-Sergey

View 1 Replies


ADVERTISEMENT

Executing Sql Statements

Jul 23, 2005

When i execute the following in Sql query anlyzerDeclare @dbname varchar(30),@str varchar(500),@emailID varchar(50)set @EmailID='santosh@yahoo.com'set @dbname='DB_kms_prv'set @str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +@dbname + '.dbo.tblEmployee where officialEmail=' + @emailIDexec (@str)I get error messageThe column prefix 'santosh@yahoo' does not match with a table name oralias name used in the query.How to get rid of it..?

View 1 Replies View Related

Executing SQL Statements Through Variables

Sep 23, 2002

Hi,

I try to run sql statement by placing them in a variable as given below but receive the following error message

set @sql_string= 'BACKUP DATABASE '+ @temp_db+' TO '+ @bk_device+' WITH FORMAT,INIT, NAME = '''+ @bk_name+''''
EXECUTE IMMEDIATE @sql_string


Error Message

Server: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'IMMEDIATE'.
Server: Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'BACKUP DATABASE DTSTEST TO bk_dev1 WITH FORMAT,INIT, NAME = 'Full backup''.


Thanks
John Jayaseelan

View 2 Replies View Related

Executing SQL Statements In Another Database

Apr 9, 2008

I have a stored procedure named usp_CreateSomeAdminObjects that resides in say a database named AdminStuff (ok, fictional but it gets the point across :) ). This proc generates a series of dynamic SQL statements (DROP TRIGGER, CREATE TRIGGER and EXECUTE sp_settriggerorder) against tables that reside in different databases. However I am unable to change the context of the database from AdminStuff to the database I am cycling. Any suggestions on how I can have execute this dynamic SQL in the context of a specific database (other than the one the proc resides in)?

I can not put the USE statement in a stored procedure. Attempts to use sp_executesql, EXECUTE and even run the script through sqlcmd using either (a) the -d option or (b) a USE <dbname>; statement as part of the -q parameter have all been unsuccessful.

Heck, even if I execute this code through SSMS it does not work as I need it to:

USE OtherDatabaseName;
EXEC DBA.dbo.usp_CreateSomeAdminObjects

I confirmed this by adding these statements to the proc:

/* Gets list of tables from the current database */
SELECT name, create_date FROM sys.objects WHERE type = 'U' ORDER BY name

SELECT DB_NAME();

These statements return a list of user tables that reside in AdminStuff and not OtherDatabaseName (the same goes for the DB_NAME() statement.

Any suggestions on how I can accomplish what I'm looking to do - have this proc execute dynamically generated DDL scripts for tables that reside in a different database. I'd rather not name this procedure sp_CreateSomeAdminObjects and compile it in the master database. If that is my only option I will but figured I'd check out other options first.

View 10 Replies View Related

Executing Statements W/o Explicit Transaction

May 30, 2008

Hi

What is SQL Server 2005 default behavior with statement like this:


SELECT * FROM dbo.Products

Will it take any shared locks on Products table? Is it different than:


BEGIN TRAN



SELECT * FROM dbo.Products WITH NOLOCK

COMMIT



TIA

View 2 Replies View Related

Large Number Of INSERT Statements - Not All Are Executed

Feb 9, 2007

Hello!I have a developer that is playing around with some SQL statementsusing VB.NET. He has a test table in a SQL 2000 database, and he hasabout 2000 generated INSERT statements.When the 2000 INSERT statements are run in SQL query analyzer, all2000 rows are added to the table. When he tries to send the 2000statements to SQL Server through his app., a random number ofstatements do not get executed. But, SQL Profiler shows that each ofthe 2000 statements are getting sent to the server.I suggested that he add a "GO" statement at the end of the INSERTblock, but the statement fails when that is sent to the server.I know that this is not the ideal manner to insert bulk data to thesystem, but now we are all just curious as to why SQL server doesn'texecute each individual INSERT.Any thoughts?

View 3 Replies View Related

Check If A Remote Server Is Online/available Before Executing A Series Of Sql Statements

Sep 30, 2007

Hi,

We have unreliable circuits connecting our office to remote sql servers. At our office I need to delete/insert a bunch of records from those remote locations. Since the connection is unreliable what is the best way to check if a server is available before executing delete/insert statements. A few points to consider:

1. I want this to be done in SQL
2. I have a cursor that cycles through a tabe that contains the name of the linked servers. The cursure is used to delete/insert a bunch of records from each remote server.
3. If one server is unavailable the stored proc should continue to the next sql server (next record in the cursor.)


Thanks,
Bradley

View 3 Replies View Related

Large Databases - Create Indexes For Fields That Are Used In Where Statements?

Aug 29, 2013

For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?

View 4 Replies View Related

SQL Server 2008 :: How To Find Statements That Cause Large Memory Paging

Apr 22, 2015

I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).

How to find particular queries/stored procedures that causing this?

View 5 Replies View Related

Transact SQL :: GO Statements To Execute Large Stored Procedure In Batches

Jun 19, 2015

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

View 9 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

SQL Statements

Aug 17, 2006

Hi guys,
Need some help in some query processing...
Here goes,this is the results i have obtained so far
|id |cat | name |
1 .....a... apple
1 .....b .. banana
2 .....a ...austria
2 .....b ...brazil
2 .....c ...china
3 .....a ...abraham
3 .....c ...clinton

Column cat can have at most 3 different types of values..{a,b,c}

I wanna write a query such that the result comes out as such
<(....id ......a .......b......... c..) corresponding to these fields.>

|Col 1| Col 2 | Col 3 | Col 4|
....1 ...apple ...banana
....2 ...austria .brazil... china
....3 ...abraham ..........clinton

Anyone can help...thanks guys

View 1 Replies View Related

If Statements In SP

Sep 21, 2006

I use SQL Server 2005.I want to create a SP "Search". With this function a user can serach the member database on several criteria: age minimal, age maximal, name, city..BUT these criteria do not necessarily have to be defined by the user, so it might be that a user searches for all members whose age is between ageMin and ageMax leaving the name and city criteria empty.So in my SP I have to check whether these values are empty or not. If a parameter is not empty I have to add it to my selection query.. BUT, I know that SQL Server makes an execution plan, and understood that when you use if-statements the procedure needs to be recompiled every time?Is this true? If so:how does this work then?Is there another way for me to still be able to create this SP?

View 2 Replies View Related

Help With If-Else Statements In SQL...please...

Dec 11, 2006

Hello guys. How is this declared in SQL?
Select * FROM my_table
     if my_column = "1" UPDATE my_table SET  my_other_column= "a"       ////my_column and my_other_column belong to my_table
     else if my_column="2" UPDATE my_table SET  my_other_column= "b" ////my_column and my_other_column belong to my_table
   

View 4 Replies View Related

Sql Statements Per Second

Jan 27, 2008

Hi, I would like to know the number of SQL statements per second being generated by my web site. How can I know this?  Best Regards,MeeNge  

View 6 Replies View Related

If Statements

Apr 8, 2008

 Hello I have an SQL Data Source  i want to compare 2 dates if one is equal to or less than the other i want to return either a string or a true i have been trying combinations for about an hour and it's getting a little frustrating i;ve tried searching for an example but cant find one so somesthing like Select ValuesWhere Date1 <= Date2 Return "String"(orTrue?) also IF date1 <= Today() Return "String OR True" thanks Chris 

View 3 Replies View Related

Sql If Statements

Feb 22, 2006

I have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!

View 14 Replies View Related

IF Then Statements

Jun 3, 2008

I need to create a brief statement in Design View for SQL 2000.

I successfully wrote "isnull(dbo.Data.Parcel,'')in the Column in Design View, which would ensure that a null value would be listed as a blank. Now I need to have dbo.data.status show as 'C&G' in any instance of data, otherwise it will be listed as blank. I tried writing "isnotnull(db0.data.status,'Homestead', '')", but it does not work.

Is there a simple statement I can write in "column" of design view that can get the results that I need?

-Steve

View 4 Replies View Related

Using Sum In Sql Statements

May 21, 2007

I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null.

View 5 Replies View Related

SQL If Statements

Oct 19, 2007



I am totally new to creating IF statements inside stored procedures. I am passing some parameters from a ColdFusion form to a SP and if the form is empty for the small date I get an error. I would like to be able to check in the procedure to see if the parameter is empty and if it is set a default value for it. Here is my code.
@JOB1,
@COLLEGEDATE smalldatetime,
If(@COLLEGEDATE <> ''")
@COLLEGEDATE smalldatetime,
ELSE
@COLLEGEDATE = getDate(),
RETURN
@JOB2

View 3 Replies View Related

Different T-SQL Statements?

Sep 16, 2006

Hi, i just want to know if the T-SQL statements in SQL server 2005 are different from SQL Server 2000???

If they are different, where can i find the Server 2005 T-SQL Statements?

View 6 Replies View Related

SUM And Iif Statements

Apr 16, 2007

Hello,



I have a report with 1 field that I need to sum into 2 different textboxes based on another field's value.



Specifically, Is it simply doing something like this below?



=Iif(Fields!iBB.Value=1, Sum(Fields!Billed.Value)

=Iif(Fields!iBB.Value=0, Sum(Fields!UnBilled.Value)



Which will not get past intellisense checking, -or- do I have to do something different here?



Thanks in advance.

View 10 Replies View Related

Help With Insert Sql Statements...

Dec 15, 2006

Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.
Is there a way for the insert statements to be merged so that I won't be executing two statements? 
Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)
Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo   (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)     
 
Thanks.

View 1 Replies View Related

Several Select Statements?

Jan 16, 2007

Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data

View 5 Replies View Related

Max SQL Statements In One Page

Feb 21, 2007

I'm running a custom built report on .net page  Basically there are a lot categories.  I need to run SQL statements for and for each category, the catch is these statements are all in loops for i = 1 to 12.  Therefore 108 categories X 12 iterations of SQL calls =  a little more that 1200 sql calls from one .net page. 
 There is obviously a major problem with load time and running these reports are painful to the user and the system.
 
Is there any easier way to possibly pre-compile all the data into a temp database so I wouldn't need to make as many sql calls???????  If so, would a stored procedure handle something like this.
 Sorry for being somewhat vague.

View 7 Replies View Related

Apostrophes, Etc. In WHERE Statements?

Apr 11, 2007

How do I handle and apostrophes and other punctuation in stored procedure Sql statements:
SELECT        L_ID, L_NameFROM            tblHVACContractorsWHERE        (L_Name = 'Mare's Heating & Cooling Services')

View 5 Replies View Related

Use Two SQL Statements In One SqlDataSource ?

May 4, 2007

Hello,
I have SqlDataSource to retrieve a data into DetailsView, or GridView, whatever.
I want to use two Select command like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=AHMED-4714D04B3;Initial Catalog=mp;Integrated Security=True"
<%
if (( parID == 3) || (ParID ==4)
{
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ParID] = @ParID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ParID" PropertyName="SelectedValue" />
</SelectParameters>
}
else
{
 
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ChiID] = @ChiID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ChiID" PropertyName="SelectedValue" />
</SelectParameters>
}
</asp:SqlDataSource>
Is it possible to do something like that ?

View 3 Replies View Related

Help Writing SQL Statements

Jun 5, 2008

Hey everyone,
  I'm making a site and I need to write some code that will do the following things: 


Write a statement for the Page_Load event to pull the information from the db and load it into a textbox and a textbox with multiline attribute.

View 13 Replies View Related

Help With Combining Sql Statements

Mar 7, 2006

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName  = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.

View 2 Replies View Related

Concatenating Sql Statements

Jan 30, 2002

I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to

SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table

If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.

The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.

Thanks,
Jeff

View 2 Replies View Related

HELP....&#34;OR&#34; Conditions In SQL Statements

May 31, 2000

Folks,

I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround.

I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys:

EQUIPMENT_NO TEXT 12
CHASSIS_IN TEXT 12
CHASSIS TEXT 12
SVC_DATE_TIME SMALLDATETIME

Here's the SQL statement:

SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM'
ORDER BY EQUIPMENT_NO

This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records.

If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute.

Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy.

Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much.

Clark R. Farabaugh, Jr.
Financial Systems Analyst
VIT
Norfolk, VA

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved