"select into" vs "insert into" question

Hello,

I have read in a number of forums that when using temporary tables, it is best to create the tables first before inserting the records rather than simply executing a "select into" to ensure that locking issues are alleviated.

I have to say that I am one of those developers who has relied on using the "select into" statement - the main reason being that I have almost always found the "select into" statement to be much faster than an "insert into" statement.

Obviously, the number of users running queries simultaneously when scripts that use "select into" statements are executed is a big deciding factor on whether "select into" or "insert into" should be used.

I'd appreciate it if someone can shed more light on other factors that should be taken into consideration when deciding to use "select into" vs "insert into".

Thanks in advance for your time and reply.It used to be that SELECT INTO was a no-no because it was an unlogged transaction, after which database recoverability was questionable. With SQL Server 2000 I do not believe this is still an issue, although I have never found documentation clarifying the matter.

INSERT INTO is slower partly because it verifies unique keys and other data restraints as the data is entered. SELECT INTO does not do this, but if you are selecting from a table that already has these restrictions inforced then it is not an issue.

Also, in many cases a table variable is the fastest method to use. Check into them and see if they might be more appropriate for your application.

"insert on existing update" in MS SQL Server?

Hello

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.

Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

Greetz,

BartBart op de grote markt wrote:

Quote:

Originally Posted by

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

In ANSI/SQL it's called a MERGE statement.
In SQL Server I'd do an UPDATE FROM, if no row found follow up with an
INSERT.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Bart op de grote markt (bartwarnez@.freegates.be) writes:

Quote:

Originally Posted by

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

I'm afraid that you will have to chalk up one minus for MS SQL Server.

You will have to do:

UPDATE ...

INSERT ...
SELECT ...
WHERE NOT EXISTS (...)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok, thank you all for the replies! It's a pity but well :-). If MS
reads this post: hello MS, you know what to do next.

Grtz,

Bart|||On Feb 6, 7:28 am, "Bart op de grote markt" <bartwar...@.freegates.be>
wrote:

Quote:

Originally Posted by

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).
>
Greetz,
>
Bart

Also look up "Mimicking MERGE Statement in SQL Server 2005 ":

http://sqlserver-tips.blogspot.com/...ent-in-sql.html

"insert into" with text type - newbie

Hello,

When I try "Insert Into" with a text column value that's very long, I
only get a cropped version inserted (I know because when I
programmatically access the field, it's cropped-- I'm not just
assuming this from query manager). The text column has a default size
of small, however I don't understand why it is only inserting the
default amoutn instead of the whole string. I am using "Insert Into
table (blah, blah) values (blah, 'loooooooooooooooooooooooooooong
entry')", and programmatically running that command.

I'm stumped. Why is it inserting only part of the long entry value?"vavavoomy2" <vavavoomy2@.yahoo.com> wrote in message
news:c5e42899.0310280942.177d251c@.posting.google.c om...
> Hello,
> When I try "Insert Into" with a text column value that's very long, I
> only get a cropped version inserted (I know because when I
> programmatically access the field, it's cropped-- I'm not just
> assuming this from query manager). The text column has a default size
> of small, however I don't understand why it is only inserting the
> default amoutn instead of the whole string. I am using "Insert Into
> table (blah, blah) values (blah, 'loooooooooooooooooooooooooooong
> entry')", and programmatically running that command.
> I'm stumped. Why is it inserting only part of the long entry value?

What does a "default size of small" mean? Text columns in MSSQL don't have a
variable width, are you talking about a char/varchar column?

In any case, you should probably check if you have ANSI_WARNINGS set OFF in
the connection properties for your client. By default this is on (for OLE
DB/ODBC at least), and inserting data which would be truncated gives an
error. Setting it to off suppresses the error and allows the insert to
happen.

If that isn't the case, can you post your CREATE TABLE statement, and an
INSERT statement which reproduces the problem? Also, does the error happen
when you execute the INSERT manually in Query Analyzer or only
programmatically?

Simon|||"vavavoomy2" <vavavoomy2@.yahoo.com> wrote in message
news:c5e42899.0310280942.177d251c@.posting.google.c om...
> Hello,
> When I try "Insert Into" with a text column value that's very long, I
> only get a cropped version inserted (I know because when I
> programmatically access the field, it's cropped-- I'm not just
> assuming this from query manager). The text column has a default size
> of small, however I don't understand why it is only inserting the
> default amoutn instead of the whole string. I am using "Insert Into
> table (blah, blah) values (blah, 'loooooooooooooooooooooooooooong
> entry')", and programmatically running that command.
> I'm stumped. Why is it inserting only part of the long entry value?

What does a "default size of small" mean? Text columns in MSSQL don't have a
variable width, are you talking about a char/varchar column?

In any case, you should probably check if you have ANSI_WARNINGS set OFF in
the connection properties for your client. By default this is on (for OLE
DB/ODBC at least), and inserting data which would be truncated gives an
error. Setting it to off suppresses the error and allows the insert to
happen.

If that isn't the case, can you post your CREATE TABLE statement, and an
INSERT statement which reproduces the problem? Also, does the error happen
when you execute the INSERT manually in Query Analyzer or only
programmatically?

Simon

"INSERT INTO" - Question

Hello,

if I have a row full of Data, lets call it Row "A". Now i have the "Insert Into" Command in my Application. So, when the User executes the Command again, will it update Row "A" or will it produce just a double?

This is important for my further Work with SQL.

I am using SQL Server 2006 Express or something like this. The Table in which Row "A" is has no primary key!

Greetz,

Eroli

Unless you have constraints/triggers or your INSERT violates the defaults/constraints, it will add a NEW ROW to the table irrespective of the existing data.

|||

Hi,

but when i put it all into a for-command like this one

for(int i = 0; i != 5;i++)

{

//All the Commands

}

I get only one row.

So, whats right now?

Greetz,

Eroli

|||Depends on what the commands are. Post all the relevant code and that might help.|||

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "INSERT INTO UsersTable([User], [FirstStart], [Commercial], [Name], [MailAddresse], [Addresse], [Addresse2], [Location], [PostCode], [Country], [PhoneNumber], [PhoneNumber2], [Sex], [ShowLastBoughts], [StartPage], [Notifications], [RemainingTime], [HomepageUrl], [Employees], [Owner], [Evaluations]) VALUES (@.User,@.FirstStart,@.Commercial, @.Name,@.MailAddresse,@.Addresse,@.Addresse2,@.Location,@.PostCode,@.Country,@.PhoneNumber,@.PhoneNumber2,@.Sex,@.ShowLastBoughts,@.StartPage,@.Notifications,@.RemainingTime,@.HomepageUrl,@.Employees,@.Owner,@.Evaluations)";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", -1);
SqlCommand.Parameters.AddWithValue("@.Commercial", -1);
SqlCommand.Parameters.AddWithValue("@.Name", "");
SqlCommand.Parameters.AddWithValue("@.MailAddresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse2", "");
SqlCommand.Parameters.AddWithValue("@.Location", "");
SqlCommand.Parameters.AddWithValue("@.PostCode", -1);
SqlCommand.Parameters.AddWithValue("@.Country", "");
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", -1);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", -1);
SqlCommand.Parameters.AddWithValue("@.Sex", "");
SqlCommand.Parameters.AddWithValue("@.ShowLastBoughts", "");
SqlCommand.Parameters.AddWithValue("@.StartPage", -1);
SqlCommand.Parameters.AddWithValue("@.Notifications", -1);
SqlCommand.Parameters.AddWithValue("@.RemainingTime", -1);
SqlCommand.Parameters.AddWithValue("@.HomepageUrl", "");
SqlCommand.Parameters.AddWithValue("@.Employees", -1);
SqlCommand.Parameters.AddWithValue("@.Owner", "");
SqlCommand.Parameters.AddWithValue("@.Evaluations", "");
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

I've got only one Row.

|||

(1) Do you have any constraints/triggers on the table?

(2) Are you changing the values of the parameters for each loop or are youe xpecting to insert 5 rows with same values?

|||

The User Column will be everytime the same, but maybe some other colums will change.

Is the UPDATE-Command better? When yes, so how can i use this?

Im confused, because i have now more rows when i tried my application again. Seems your are right.

|||You need to explain more clearly what you are trying to do and what you intend to do. INSERT and UPDATE are 2 different commands that accomplish 2 different things. so there's no question of one "better" over another. You need to use either one depending on what how application needs to behave. Simply, INSERT inserts data into the table (meaning rows will increase), UPDATE updates te existing data (and how many rows affected depends on the WHERE condition in the query).|||

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

|||

Eroli:

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Yes.

Eroli:

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

Yes. You need to do put your logic accordingly.

|||Ok, thanks for your advice!|||

Ok, i changed my Commands.

This is my UPDATE Command:

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();</p><p>

It is in an ASP.NET Webapplication in C#! I get no failure, but nothing gets updated...

The obversely Row is filled with Dummy-Values before starting this Command.(MS SQL Server 2005 Express or so)

Greetz,

Eroli

|||

I hope this looks better...

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

|||

Ok, i solved it: I have just forgotten to put the [ ] round my Columns.

But there is another Problem: THe Condition does not work, because the User Column is no Identify-Column. How to solve this?

|||I did not understand your question. Can you rephrase?

"Input string was not in a correct format"

I am trying to do an insert into a SQL server table but I am getting an "input string was not in correct format" error. Does this error always refer to SQL string problem? I put in a breakpoint and looked at the SQL and it looks good but until I figure this out I will be tracing through the code. Any input greatly appreciated.

MyCmd.CommandText = sSQL
MyCmd.ExecuteNonQuery() 'Line where I get the error
Thanks,
JoeThis usually indicates there is a data type mismatch problem. You've probably assigned a value to a parameter which was expecting avalue with a different data type.
|||Ok, in response to your feedback, I've shortened the SQL insert string and I plan on trying to insert first just one field and then next two fields and so on so I can determine which field is giving me a problem, however I can't even insert one field. My code:
Try
MyCmd.CommandText = "INSERT INTO tblTest (intProjectID) VALUES (1)"
MyCmd.ExecuteNonQuery()
Catch e As System.Data.SqlClient.SqlException
Response.Write(e.Message)
End Try
Is still giving me the same errror:
"Input string was not in a correct format" for the MyCmd.ExecuteNonQuery line.
intProjectID of tblTest field is type "int". What am I doing wrong?
Thanks in advance,
Joe

"Error creating MDAC instance" performing Bulk Insert

I get this error when trying to perform a bulk insert. This started
happening after the latest security fixes were loaded on the server.
Anyone know what this error means?
I was able to correct this by installing the latest sp of MDAC and SP3 of
SQLXML.
"DavidSp8" wrote:

> I get this error when trying to perform a bulk insert. This started
> happening after the latest security fixes were loaded on the server.
> Anyone know what this error means?

"Error creating MDAC instance" performing Bulk Insert

I get this error when trying to perform a bulk insert. This started
happening after the latest security fixes were loaded on the server.
Anyone know what this error means?I was able to correct this by installing the latest sp of MDAC and SP3 of
SQLXML.
"DavidSp8" wrote:

> I get this error when trying to perform a bulk insert. This started
> happening after the latest security fixes were loaded on the server.
> Anyone know what this error means?

"Conversion failed when converting from a character string to uniqueidentifier."

This is probably a simple wuestion but i would appreciate some help

I am trying to get and insert theuserId into a table called "Orders"

I got the user id as

Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString

The button on click command is

worldshop.ShoppingCart.CreateOrder(CustomerId,...................

The Function Has the parameter for Customer Id as follows

Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_CustomerId.ParameterName = "@.CustomerId"
dbParam_CustomerId.Value = CustomerId
dbParam_CustomerId.DbType = System.Data.DbType.String
command.Parameters.Add(dbParam_CustomerId)

When i run this I am getting the error message

"Conversion failed when converting from a character string to uniqueidentifier."

Can anyone tell me where I am going wrong

many thanks

Martin

What type is CustomerID defined as in the database? It sounds like it is NOT a string, but rather a uniqueidentifier (a GUID).|||

thanks for your reply. i got it sorted out

martin

|||Do you mind sharing with us what your solution was? as I am having the same problem...|||

I'll check and get back to you

martin

|||

sorry about the delay in getting back to you

I was having the same problem. I solved it by instantiating the userid as an object

Dim userId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey

i passed the object to the function as a guid

ByVal userId As Guid
Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userId.ParameterName = "@.UserId"
dbParam_userId.Value = userId
dbParam_userId.DbType = System.Data.DbType.Guid
command.Parameters.Add(dbParam_userId)

last i put the guid into the database as char 36

@.UserId char (36)

HTH

"Column name or number of supplied values does not match table definition" when trying to

Hello,
I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @.Gender = 'Male', @.Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a"SELECT * FROM tbl_User WHERE gender = @.Gender AND culture = @.Culture",so why am I receiving this error when both tables have the samestructure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
Thanks,
Greg.
hello...
this might be simplified version..
SELECT * INTO #tbl_User FROM tbl_User WHERE Gender='Male' AND Culture='en-GB'
and there might be idenity column on your original tbl_User..?
i tested a table with identity column, same error msg (just guess)|||In addition to busyweb's reply, if you do need to create an empty temptable using SELECT INTO in the future, try the following instead of TOP1 + TRUNCATE:
SELECT *
INTO #Tbl
FROM SomeTable
WHERE 1=0

"Bulk copy insert failed"

I'm copying data from tables in one server to tables in another via DTS.

This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.

When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.

Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.

But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?