insert or replace: February 2012

Friday, February 24, 2012

Insert Query - Please help

Does anyone know how to insert records into another table based on a range.
See the following example
Table A contains
Col1 (From), Colb (To), Colc (Date)
902,905,01/01/2005
906,907, 01/03/2005
Table B Need to contain
Col1 (From), Colb (To), Colc (Date)
902,902,01/01/2005
903,903,01/01/2005
904,904,01/01/2005
905,905,01/01/2005
906,906,01/03/2005
907,907,01/03/2005
Any help gratefully appreciated.
Thanks
Hope to understood you right:
-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================
DECLARE Looper CURSOR
READ_ONLY
FOR Select [From],[To],[Date] From TableA
DECLARE @.From Smallint
DECLARE @.TO Smallint
DECLARE @.Date varchar(40)
OPEN Looper
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
While @.From <= @.To
BEGIN
Insert Into TableB Values(@.From,@.To,@.Date)
SET @.From = @.From +1
END
END
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
END
CLOSE Looper
DEALLOCATE Looper
GO
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"Sarah Kingswell" <skingswell@.xonitek.co.uk> schrieb im Newsbeitrag
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>
|||Create a table of numbers if you aren't using one already:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
Then do this:
INSERT INTO TableB (col1, colb, colc)
SELECT N.num, N.num, A.date
FROM Numbers AS N, TableA AS A
ON N.num BETWEEN A.col1 AND A.colb
It may be useful to put the above query in a view rather than a table.
David Portas
SQL Server MVP
|||Here goes:
-- Auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 1000 -- change @.max according to your needs
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums SELECT n + @.rc FROM Nums WHERE n + @.rc <= @.max
COMMIT TRAN
ALTER TABLE Nums ADD PRIMARY KEY(n)
CREATE TABLE A
(
a INT,
b INT,
c DATETIME
)
INSERT INTO A VALUES(902, 905, '20050101')
INSERT INTO A VALUES(906, 907, '20050301')
CREATE TABLE B
(
a INT,
b INT,
c DATETIME
)
INSERT INTO B
SELECT a + n - 1 AS a, a + n - 1 AS b, c
FROM A JOIN Nums
ON n <= b - a + 1
SELECT * FROM B
a b c
-- -- --
902 902 2005-01-01 00:00:00.000
903 903 2005-01-01 00:00:00.000
904 904 2005-01-01 00:00:00.000
905 905 2005-01-01 00:00:00.000
906 906 2005-03-01 00:00:00.000
907 907 2005-03-01 00:00:00.000
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>
|||Thanks every much for your fast responses.. I have managed to get this
working with Jens answer. Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113557510.015892.253740@.o13g2000cwo.googlegr oups.com...
> Create a table of numbers if you aren't using one already:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp
> Then do this:
> INSERT INTO TableB (col1, colb, colc)
> SELECT N.num, N.num, A.date
> FROM Numbers AS N, TableA AS A
> ON N.num BETWEEN A.col1 AND A.colb
> It may be useful to put the above query in a view rather than a table.
> --
> David Portas
> SQL Server MVP
> --
>

Insert Query - Please help

Does anyone know how to insert records into another table based on a range.
See the following example
Table A contains
Col1 (From), Colb (To), Colc (Date)
902,905,01/01/2005
906,907, 01/03/2005
Table B Need to contain
Col1 (From), Colb (To), Colc (Date)
902,902,01/01/2005
903,903,01/01/2005
904,904,01/01/2005
905,905,01/01/2005
906,906,01/03/2005
907,907,01/03/2005
Any help gratefully appreciated.
ThanksHope to understood you right:
-- ========================================
=====
-- Declare and using a READ_ONLY cursor
-- ========================================
=====
DECLARE Looper CURSOR
READ_ONLY
FOR Select [From],[To],[Date] From TableA
DECLARE @.From Smallint
DECLARE @.TO Smallint
DECLARE @.Date varchar(40)
OPEN Looper
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
While @.From <= @.To
BEGIN
Insert Into TableB Values(@.From,@.To,@.Date)
SET @.From = @.From +1
END
END
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
END
CLOSE Looper
DEALLOCATE Looper
GO
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Sarah Kingswell" <skingswell@.xonitek.co.uk> schrieb im Newsbeitrag
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>|||Create a table of numbers if you aren't using one already:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
Then do this:
INSERT INTO TableB (col1, colb, colc)
SELECT N.num, N.num, A.date
FROM Numbers AS N, TableA AS A
ON N.num BETWEEN A.col1 AND A.colb
It may be useful to put the above query in a view rather than a table.
David Portas
SQL Server MVP
--|||Here goes:
-- Auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 1000 -- change @.max according to your needs
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums SELECT n + @.rc FROM Nums WHERE n + @.rc <= @.max
COMMIT TRAN
ALTER TABLE Nums ADD PRIMARY KEY(n)
CREATE TABLE A
(
a INT,
b INT,
c DATETIME
)
INSERT INTO A VALUES(902, 905, '20050101')
INSERT INTO A VALUES(906, 907, '20050301')
CREATE TABLE B
(
a INT,
b INT,
c DATETIME
)
INSERT INTO B
SELECT a + n - 1 AS a, a + n - 1 AS b, c
FROM A JOIN Nums
ON n <= b - a + 1
SELECT * FROM B
a b c
-- -- --
902 902 2005-01-01 00:00:00.000
903 903 2005-01-01 00:00:00.000
904 904 2005-01-01 00:00:00.000
905 905 2005-01-01 00:00:00.000
906 906 2005-03-01 00:00:00.000
907 907 2005-03-01 00:00:00.000
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>|||Thanks every much for your fast responses.. I have managed to get this
working with Jens answer. Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113557510.015892.253740@.o13g2000cwo.googlegroups.com...
> Create a table of numbers if you aren't using one already:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp
> Then do this:
> INSERT INTO TableB (col1, colb, colc)
> SELECT N.num, N.num, A.date
> FROM Numbers AS N, TableA AS A
> ON N.num BETWEEN A.col1 AND A.colb
> It may be useful to put the above query in a view rather than a table.
> --
> David Portas
> SQL Server MVP
> --
>

Insert Query - Please help

Does anyone know how to insert records into another table based on a range.
See the following example
Table A contains
Col1 (From), Colb (To), Colc (Date)
902,905,01/01/2005
906,907, 01/03/2005
Table B Need to contain
Col1 (From), Colb (To), Colc (Date)
902,902,01/01/2005
903,903,01/01/2005
904,904,01/01/2005
905,905,01/01/2005
906,906,01/03/2005
907,907,01/03/2005
Any help gratefully appreciated.
ThanksHope to understood you right:
-- =============================================-- Declare and using a READ_ONLY cursor
-- =============================================DECLARE Looper CURSOR
READ_ONLY
FOR Select [From],[To],[Date] From TableA
DECLARE @.From Smallint
DECLARE @.TO Smallint
DECLARE @.Date varchar(40)
OPEN Looper
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
While @.From <= @.To
BEGIN
Insert Into TableB Values(@.From,@.To,@.Date)
SET @.From = @.From +1
END
END
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
END
CLOSE Looper
DEALLOCATE Looper
GO
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"Sarah Kingswell" <skingswell@.xonitek.co.uk> schrieb im Newsbeitrag
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>|||Create a table of numbers if you aren't using one already:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
Then do this:
INSERT INTO TableB (col1, colb, colc)
SELECT N.num, N.num, A.date
FROM Numbers AS N, TableA AS A
ON N.num BETWEEN A.col1 AND A.colb
It may be useful to put the above query in a view rather than a table.
--
David Portas
SQL Server MVP
--|||Here goes:
-- Auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 1000 -- change @.max according to your needs
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums SELECT n + @.rc FROM Nums WHERE n + @.rc <= @.max
COMMIT TRAN
ALTER TABLE Nums ADD PRIMARY KEY(n)
CREATE TABLE A
(
a INT,
b INT,
c DATETIME
)
INSERT INTO A VALUES(902, 905, '20050101')
INSERT INTO A VALUES(906, 907, '20050301')
CREATE TABLE B
(
a INT,
b INT,
c DATETIME
)
INSERT INTO B
SELECT a + n - 1 AS a, a + n - 1 AS b, c
FROM A JOIN Nums
ON n <= b - a + 1
SELECT * FROM B
a b c
-- -- --
902 902 2005-01-01 00:00:00.000
903 903 2005-01-01 00:00:00.000
904 904 2005-01-01 00:00:00.000
905 905 2005-01-01 00:00:00.000
906 906 2005-03-01 00:00:00.000
907 907 2005-03-01 00:00:00.000
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>|||Thanks every much for your fast responses.. I have managed to get this
working with Jens answer. Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113557510.015892.253740@.o13g2000cwo.googlegroups.com...
> Create a table of numbers if you aren't using one already:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp
> Then do this:
> INSERT INTO TableB (col1, colb, colc)
> SELECT N.num, N.num, A.date
> FROM Numbers AS N, TableA AS A
> ON N.num BETWEEN A.col1 AND A.colb
> It may be useful to put the above query in a view rather than a table.
> --
> David Portas
> SQL Server MVP
> --
>

Insert Query - Basic Question

Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.

I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.

What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.

Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,

Regards

You can update all that does not have a siteref set with something like this

update t
set siteref = left(t.unitref, 2)
from dbo.table t
where t.siteref is null and len(t.unitref) > 1;

This will find all records in table where siteref is not set and has a unitref with 2 or more characters (otherwise the left(, 2) will be illegal) and set it as the siteref for the row

EDIT: Might have misunderstood you...

Insert Query

Hi ,
What is the query to insert the table A to table b if the table b primary
key did not
exist in table A ?
Travis Tan
Hello
if in table B primary key collumn is uniqueidentifier then i would try
insert into table b ( primary_key_collumns, columnA, columnB...)
(
select newid(), , columnA, columnB...
from table A
)
Wojciech Znaniecki
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan
|||Try this
INSERT INTO b SELECT * FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE
a.PK=b.PK)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan
|||Hi Travis
Just try this query
insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
from TableB B)
thanks and regards
Chandra
"Travis" wrote:

> Hi ,
> What is the query to insert the table A to table b if the table b primary
> key did not
> exist in table A ?
> --
> Travis Tan
|||Hi
I may get a wrong output if b.key is NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:93AF9ED8-273F-4180-842B-EA33A2F52CA3@.microsoft.com...[vbcol=seagreen]
> Hi Travis
> Just try this query
> insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
> from TableB B)
> thanks and regards
> Chandra
>
> "Travis" wrote:
primary[vbcol=seagreen]

Insert Query

how can i do an insert into a table, only if the record doesnt exist,
ie insert into tb1
values ('my name', 'myage', 'mynumber')Peter
IF NOT EXISTS (SELECT * FROM Table WHERE...)
INSERT here
ELSE
......
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:8A2461A6-CCA2-4B19-B041-338582E5AC10@.microsoft.com...
> how can i do an insert into a table, only if the record doesnt exist,
> ie insert into tb1
> values ('my name', 'myage', 'mynumber')
>|||Or in an inline query like:
insert into tb1
SELECT 'my name', 'myage', 'mynumber'
WHERE NOT EXISTS
(SELECT * FORM tb1 Where col1 = 'my name' AND
colb = 'myage' AND
colc = 'mynumber'
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Insert Query

Hi ,
What is the query to insert the table A to table b if the table b primary
key did not
exist in table A ?
Travis TanHello
if in table B primary key collumn is uniqueidentifier then i would try
insert into table b ( primary_key_collumns, columnA, columnB...)
(
select newid(), , columnA, columnB...
from table A
)
Wojciech Znaniecki
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Try this
INSERT INTO b SELECT * FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE
a.PK=b.PK)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Hi Travis
Just try this query
insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
from TableB B)
thanks and regards
Chandra
"Travis" wrote:

> Hi ,
> What is the query to insert the table A to table b if the table b prima
ry
> key did not
> exist in table A ?
> --
> Travis Tan|||Hi
I may get a wrong output if b.key is NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:93AF9ED8-273F-4180-842B-EA33A2F52CA3@.microsoft.com...[vbcol=seagreen]
> Hi Travis
> Just try this query
> insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
> from TableB B)
> thanks and regards
> Chandra
>
> "Travis" wrote:
>
primary[vbcol=seagreen]

insert query

Plz send me insert, delete stored procedure ... very urgent.....

Quote:

Originally Posted by palanivel

Plz send me insert, delete stored procedure ... very urgent.....

create procedure <Procedure Name> (<@.Parameter1> <DataType>, <@.Parameter2> <DataType>,...etc)

begin
what ever insert, update, delete qry here
end|||

Quote:

Originally Posted by palanivel

Plz send me insert, delete stored procedure ... very urgent.....

create procedure <procedure name>

drop procedure <procedure name>

Insert Query

Hi ,
What is the query to insert the table A to table b if the table b primary
key did not
exist in table A ?
--
Travis TanHello
if in table B primary key collumn is uniqueidentifier then i would try
insert into table b ( primary_key_collumns, columnA, columnB...)
(
select newid(), , columnA, columnB...
from table A
)
--
Wojciech Znaniecki
U¿ytkownik "Travis" <Travis@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Try this
INSERT INTO b SELECT * FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE
a.PK=b.PK)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:AF89E88B-F942-41CF-9D95-7BD08FECA853@.microsoft.com...
> Hi ,
> What is the query to insert the table A to table b if the table b
primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Hi Travis
Just try this query
insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
from TableB B)
thanks and regards
Chandra
"Travis" wrote:
> Hi ,
> What is the query to insert the table A to table b if the table b primary
> key did not
> exist in table A ?
> --
> Travis Tan|||Hi
I may get a wrong output if b.key is NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:93AF9ED8-273F-4180-842B-EA33A2F52CA3@.microsoft.com...
> Hi Travis
> Just try this query
> insert into tableB Select * from TableA A Where A.Key NOT IN (SELECT B.KEY
> from TableB B)
> thanks and regards
> Chandra
>
> "Travis" wrote:
> > Hi ,
> >
> > What is the query to insert the table A to table b if the table b
primary
> > key did not
> > exist in table A ?
> >
> > --
> > Travis Tan

Insert querry

Hello

Can anyone give me the code to insert date from textbox to database(SQL Server 2000). The date fromat in SQL is dd-mm-yyyy.

Rathish

rathish,

here is some code if you wish to take the code behind approach.

string yourDate;yourDate = txtBox.Text;string insertStr ="insert into yourTable values ('" + yourDate +"')";SqlConnection conn =new SqlConnection(connectionStr);SqlCommand cmd =new SqlCommand(insertStr, conn);using(conn){using(cmd){ conn.Open(); cmd.ExecuteNonQuery(); }}you can try something like that. hope it helps! -- jp

|||

Hello

Its giving me the following error:

Syntax error convertig datetime from character string.

The datatype is datetime in the database.

Rathish

|||

Hi,

I would suggest you use parameters update database

string yourDate;
yourDate = txtBox.Text;
string insertStr = "insert into yourTable(datefield) values (@.datefield)";
SqlConnection conn = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(insertStr, conn);
cmd.Parameters.Add("@.datefield", SqlDbType.DateTime, 8)
cmd.Parameters["@.datefield"].Value = DateTime.Parse(yourDate)
using(conn){
using(cmd){
conn.Open();
cmd.ExecuteNonQuery();
}
}

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Insert produces error

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@.AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@.AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

ToddHi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.newssvr21.news.prodigy.co m...
> Hi,
> Using SQL Server 2000 with Windows 2000 Adv Server
> &
> Microsoft Access linked table (running stored procedure using ADO as
> follows:
> ************************************************** ********
> Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim msg As String
> On Error GoTo Err_AddrType_NotInList
> 'Exit the procedure if the combo box was cleared
> If Trim(NewData) = "" Then Exit Sub
> 'Confirm that the user wants to add AddrType
> msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
> msg = msg & "Do you want to add it?"
> If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
> 'If the user chose not to add AddrType, set the response
> 'argument to supress an error message and undo changes.
> Response = acDataErrContinue
> MsgBox "No record added.", vbOKOnly, "Action Cancelled"
> Else
> 'If the user chose to add AddrType, open a recordset
> 'using the AddrType table
>
> Set cmd = New ADODB.Command
> Set cnn = New ADODB.Connection
> cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
> Catalog=groomery;Integrated Security=SSPI;"
> cmd.ActiveConnection = cnn
> cmd.CommandText = "spInsertAddrType"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParameter("AddrType", adVarChar,
> adParamInput, , Trim(NewData))
> cmd.Execute Parameters:=prm
> 'Set Response argument to indicate that new data is being added
> Response = acDataErrAdded
> cnn.Close
> Set cnn = Nothing
> End If
> Exit_AddrType_NotInList:
> Exit Sub
> Err_AddrType_NotInList:
> MsgBox Err.Description
> Response = acDataErrContinue
> ************************************************** ********
> "NewData" is a text string - in this case "Test"
> The stored procedure referenced in the code is:
> ************************************
> CREATE PROCEDURE [spInsertAddrType]
> (@.AddrType [nvarchar](50))
> AS
> INSERT INTO [groomery].[dbo].[tblAddrTypes]
> ([fldAddrType])
> VALUES
> (@.AddrType)
> GO
> *************************************
> When I execute this code, I receive the following error
> "Cannot update identity column 'fldAddrTypeID'."
> fldAddrTypeID is configured as follows:
> ***************************
> Data Type = int
> Identity = Yes
> Identity Seed = 1
> Identity Increment = 1
> ***************************
> The documentation I've found online concerning this error says that it is
> produced when you try to supply a value for an identity field without SET
> IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
> figure why I'm getting this error.
> Thanks for any help you can offer.
> Todd

Insert procedure in two tables with Foreign Key relation ship

I was wondering how I do to insert values in two tables that are related each other by a FK?

That is the procedure that illustrate what I meant to be.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

Begin

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (Selectuser_idfrom userswhere nick=@.master,@.e_mail)

End

Thank you very much.

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginSET NOCOUNT ONDeclare @.useridintINSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)SELECT @.userid = SCOPE_IDENTITY()INSERT INTO friends ([user_id], e_mail)VALUES (@.userid, @.e_mail)SET NOCOUNT OFFEnd

|||

Thanks to reply ndinakar.

The value inserted "@.userid" in friends table, it is not the same just inserted in the users table. This value is determined by a consult in users table, where nick = "@.master", the return of this consult will say which user_id I will insert in friends table.

|||So do you still need to do an INSERT into the users table with the values you receive in the stored proc or are the values just to be used for lookup?|||

The value @.master is for lookup the table users and gets the user_id. The values @.nick, @.fish, @.e_mail, are for create a new user. And is inserted a new record in friends table with user_id equals to the value consulted.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

Begin

Declare@.returned_valuenchar(10)

Selectuser_idfrom userswhere nick=@.master

--I garante it will return a unique value. Supose to be called @.returned_value.

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (@.returned_value,@.e_mail)

End

That will result a data base. Which the new user created will be related with the person who added.

|||

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginDeclare @.returned_valueint-- assuming userid is numeric if not I would recommend nvarchar instead of nchar.Select @.returned_value = [user_id]from userswhere nick=@.master--I garante it will return a unique value. Supose to be called @.returned_value.INSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)INSERT INTO friends ([user_id], e_mail)VALUES (@.returned_value,@.e_mail)End

|||Thank you very much indeed. But at last why you would recommend nvarchar instead of nchar?|||

When you use char, the length becomes a fixed size. So even if you send in a value less than the specified length, SQL pads the value with blank spaces to make it the fixed length.

So 'abc' <> 'abc '. Besides it is also a waste of space/memory. If you use varchar (Variable char) it allocates only as much is required, up to the specified length.

|||

On the select statement I mentioned that it will return a unique value, but if it return nothing or more fields?

If don't want waste your time explaining to me, could just give me the word that I look for on the internet? I tried some but I would never find about this problem specifically. And just wondering, do you know if Microsoft pays some one to stay here in this forum answering questions?

Thank you very much indeed.

Insert procedure from Excel

Hi all

I need to create a stored procedure that will insert from an Excel document. And I'm not exaclty sure of how to do that.

CREATE PROCEDURE [Insert_ActiveSuspensions]

AS
INSERT INTO [GamingCommissiondb].[dbo].[License_SuspensionsView]
([TM #],
[FIRSTNAME],
[LASTNAME],
[SS #],
[REASONFORSUSPENSION],
[ENDDATE]
[BEGINDATE])

SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM "C:\Documents and Settings\Desiree Stevenson\My Documents\TerminationInserts.xls"
IF @.@.Error <> '0'

is this correct??create a linked server to the excel file. look up sp_addlinkedserver in BOL.|||I am wanting to export from a table to an Excel spreadsheet and then to a table in another database.

I should explained that better sorry|||Consider the import/export wizard in Enterprise Manager (SQL 2000) or Management Studio (SQL 2005).|||Try with BCP command......... Google "BCP"......... There is your answer.......

Thanks,
Rahul Jha|||With Excel, make sure SET NOCOUNT ON is specified in the procedure. It causes problems called by an xla.|||Try with BCP command......... Google "BCP"......... There is your answer.......

Thanks,
Rahul Jha

excuse me?

this message...|||I am wanting to export from a table to an Excel spreadsheet and then to a table in another database.

I should explained that better sorry

ummm, why?

is the database on the same server/instance?

Insert Proc With Both Select And Values

I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.

I created a test in MS Access and it loooks like this:

INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country

This works great in Access but not in SQL Server. In SQL Server 2 = @.PatientTripID

ANY SUGGESTIONS ON HOW TO HANDLE THIS?Hey, I tested your script. It works for me. Could you specify the error message and under what circumstance you are running this command and fail?|||Are you looking for something more like:CREATE PROCEDURE dbo.s2164
@.piPatientTripID INT
AS

INSERT INTO PatientTripRegionCountry_Temp (
CountryID, RegionID
, Country, PatientTripID)
SELECT Country.CountryID, Country.RegionID
, Country.Country, @.PatientTripID
FROM Country

RETURN-PatP|||This is my Stored Proc. It executes but the field PatientTripID is set to <Null>

CREATE PROCEDURE [dbo].[sp_PatientTripRegionCountryTemp_Insert_ForRegionID ]
@.RegionID int,
@.PatientTripID int,
@.PatientID int
AS
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, Country, RegionID, PatientTripID )
SELECT C.CountryID, C.Country, C.RegionID, @.PatientTripID
FROM Country C
WHERE (RegionID=@.RegionID)
GO

Any Suggestions?|||When you execute it from Query Analyzer, it should show "N row(s) affected" when it executes. Zero would be a bad thing in this case.

-PatP|||?? How are you calling the procedure? Can you give a couple examples?|||Thanks for all your help

Don't ask me why, but I retried the versions shown in #4 above and this time it worked.|||Way more gooder yet even! Glad you are back in business.

-PatP

Insert Problem.

I am using a DTS package to insert data into a SQL table. This has worked
correctly for the last 2 years. However when I run the code now I get a
Timeout error. I have rebooted the server with no success. If I try and run
the data into a temporary table it works fine.
Is the table corrupt ? If so how can I uncorrupt it ?
Si
Can you provide a little more information on where the table is stored, how
the dts package looks like?
What's the source? What's the destination?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CA096C29-74E5-48F2-923A-A00D2321BDF4@.microsoft.com...
>I am using a DTS package to insert data into a SQL table. This has worked
> correctly for the last 2 years. However when I run the code now I get a
> Timeout error. I have rebooted the server with no success. If I try and
> run
> the data into a temporary table it works fine.
> Is the table corrupt ? If so how can I uncorrupt it ?
> Si

Insert Problem.

I am using a DTS package to insert data into a SQL table. This has worked
correctly for the last 2 years. However when I run the code now I get a
Timeout error. I have rebooted the server with no success. If I try and run
the data into a temporary table it works fine.
Is the table corrupt ? If so how can I uncorrupt it ?
SiCan you provide a little more information on where the table is stored, how
the dts package looks like?
What's the source? What's the destination?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CA096C29-74E5-48F2-923A-A00D2321BDF4@.microsoft.com...
>I am using a DTS package to insert data into a SQL table. This has worked
> correctly for the last 2 years. However when I run the code now I get a
> Timeout error. I have rebooted the server with no success. If I try and
> run
> the data into a temporary table it works fine.
> Is the table corrupt ? If so how can I uncorrupt it ?
> Si

Insert problem with single quotes

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

blumonde

blumonde:
I have a problem with inserting a string with single quotes. For instance,
string testme = "we don't have anything";
insert into tableone (buff) values ("'" + testme + "'");
I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

No, it's not a bug with SQL Server 2005. To use that method, you would need to double up any single quotes within the testme string in order to "escape" them so that your INSERT statement works correctly.

However, you should be using parameters to pass UI-supplied values to your SQL statement. Here's the why:

Please, please, please, learn about injection attacks!

And here's the how:

How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

|||

tmorton:
blumonde:
I have a problem with inserting a string with single quotes. For instance,
string testme = "we don't have anything";
insert into tableone (buff) values ("'" + testme + "'");
I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

No, it's not a bug with SQL Server 2005. To use that method, you would need to double up any single quotes within the testme string in order to "escape" them so that your INSERT statement works correctly.
However, you should be using parameters to pass UI-supplied values to your SQL statement. Here's the why:
Please, please, please, learn about injection attacks!

And here's the how:
How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

Thank you Tmorton. I think parameters will have solved the problem.

blumonde

Insert problem with linked server

Both servers running SQL 2000

I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.

The following three queries work:

insert into LinkedServer.dbname.dbo.Table2
select *
from LinkedServer.dbname.dbo.Table1

select *
into LocalTable
from LinkedServer.dbname.dbo.Table1

insert into LocalTable
select *
from LinkedServer.dbname.dbo.Table1

This query, which is what we really want to do, does not work:

insert into LinkedServer.dbname.dbo.Table1
select *
from LocalTable

and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'

I am new to all this and would welcome some help.

AdrianI believe I have now resolved this

In fact the example below would work

> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable

I was trying to insert into a table on the linked server that was not owned
by the dbo but by the remote username. It seems that providing the owner of
the table is dbo it will be OK.

Adrian.

"Adrian" <NoSpam@.hotmail.com> wrote in message
news:ANReb.6576$8_4.54623402@.news-text.cableinet.net...
> Both servers running SQL 2000
> I have set up on our local SQL server (using Enterprise Manager) a linked
> server running on our ISP. Just did new linked server and added remote
> password and login.
> The following three queries work:
> insert into LinkedServer.dbname.dbo.Table2
> select *
> from LinkedServer.dbname.dbo.Table1
> select *
> into LocalTable
> from LinkedServer.dbname.dbo.Table1
> insert into LocalTable
> select *
> from LinkedServer.dbname.dbo.Table1
>
> This query, which is what we really want to do, does not work:
> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable
> and returns the error: 'The cursor does not include the table being
modified
> or the table is not updatable through the cursor.'
> I am new to all this and would welcome some help.
> Adrian

Insert Problem in MS SQL

hey, I have written a bunch of insert statements and basically all I am trying to do is fill up columns of one table with values from columns of the second table.

My insert statement looks like:

INSERT table1(column)
SELECT column
FROM table2

Now, this works for one column , but when I run another statement like this trying to update another column, the second time around it does not work.
It does not error out, it shows that it runs fine, but the data is not shown on the table. Some of the data which is shown removes the data from the first column in the adjacent row. I am sure I am missing something here, but not able to figure it out, please HELP .Does your code look like this.

INSERT INTO Table1
( Col1, Col2, Col3....Etc)
SELECT col1, Col2, Col3
FROM Table2

If not then it won't work. Post your code and I'll have a look

Cheers
C|||Hi!
I think you have to do some change.you may use following codes,else email me your total code .Than i heartly solve thats....
INSERT INTO Table1
( Col1, Col2, Col3....Etc)
VALUES(SELECT col1, Col2, Col3....etc
FROM Table2)

Ok...bye

INSERT problem

I'm not sure if this is the right place to post this, if not, maybe
someone could give me a pointer to where to ask the question...
A friend and I are trying to figure out if it's possible to create a
table that will allow an insert of a value into column A only if that
value is already in column B or if it being inserted into column B by
that insert statement.
We've done some looking around, but haven't found an answer yet...
Sounds like you want a FOREIGN KEY constraint on a self-referencing table:
create table MyTable
(
ColA int primary key
, ColB int not null
references MyTable (ColA)
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Jakanapes" <Jakanapes@.aol.com> wrote in message
news:1130156166.728791.38140@.g49g2000cwa.googlegro ups.com...
I'm not sure if this is the right place to post this, if not, maybe
someone could give me a pointer to where to ask the question...
A friend and I are trying to figure out if it's possible to create a
table that will allow an insert of a value into column A only if that
value is already in column B or if it being inserted into column B by
that insert statement.
We've done some looking around, but haven't found an answer yet...
|||Yes, you can use an INSERT trigger for that, using the virtual inserted table inside your trigger
code. Or possibly a self referencing foreign key, if column B is a key of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jakanapes" <Jakanapes@.aol.com> wrote in message
news:1130156166.728791.38140@.g49g2000cwa.googlegro ups.com...
> I'm not sure if this is the right place to post this, if not, maybe
> someone could give me a pointer to where to ask the question...
> A friend and I are trying to figure out if it's possible to create a
> table that will allow an insert of a value into column A only if that
> value is already in column B or if it being inserted into column B by
> that insert statement.
> We've done some looking around, but haven't found an answer yet...
>

INSERT problem

I'm not sure if this is the right place to post this, if not, maybe
someone could give me a pointer to where to ask the question...
A friend and I are trying to figure out if it's possible to create a
table that will allow an insert of a value into column A only if that
value is already in column B or if it being inserted into column B by
that insert statement.
We've done some looking around, but haven't found an answer yet...Sounds like you want a FOREIGN KEY constraint on a self-referencing table:
create table MyTable
(
ColA int primary key
, ColB int not null
references MyTable (ColA)
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jakanapes" <Jakanapes@.aol.com> wrote in message
news:1130156166.728791.38140@.g49g2000cwa.googlegroups.com...
I'm not sure if this is the right place to post this, if not, maybe
someone could give me a pointer to where to ask the question...
A friend and I are trying to figure out if it's possible to create a
table that will allow an insert of a value into column A only if that
value is already in column B or if it being inserted into column B by
that insert statement.
We've done some looking around, but haven't found an answer yet...|||Yes, you can use an INSERT trigger for that, using the virtual inserted tabl
e inside your trigger
code. Or possibly a self referencing foreign key, if column B is a key of th
e table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jakanapes" <Jakanapes@.aol.com> wrote in message
news:1130156166.728791.38140@.g49g2000cwa.googlegroups.com...
> I'm not sure if this is the right place to post this, if not, maybe
> someone could give me a pointer to where to ask the question...
> A friend and I are trying to figure out if it's possible to create a
> table that will allow an insert of a value into column A only if that
> value is already in column B or if it being inserted into column B by
> that insert statement.
> We've done some looking around, but haven't found an answer yet...
>

insert problem

hi
when i tried to store Arabic chars (from a textbox) in a db table all i got is "?" chars instead of the actual letters.that happens on my database on the server
but in my machine everything works fine
i changed the responseEncoding/requestEncoding/fileEncoding to "windows-1256" but there is no benefit .

any idea ?

thanks in advanceis the db column an nvarchar or ntext ?
what is the default encoding of the db itself ?|||thanks for replying

the db column is nvarchar

the default encoding of the db exactly i don't know ? how can i figure it out ?

INSERT Problem

I'm having an issue inserting records into an SQL server. We're using Access 2003 as the front end with an SBS2003 box running sql with all the latest patches.

Everything works fine with one computer accessing but when we have multiple computers (5) we experience problems. Each order has multiple details rows that are being inserted into a table using ADO w/SQL commands. The rows for each order are entered into the table and the appear correct. Then the rows for the specific order appear to get removed and then all added again (with the same time stamp) but with some of the data missing.

Any help would be appreaciated.

Cheers,
Jon_got code? see Brett's sticky at the top of the page. It sounds like you have an application bug dealing with concurrency.|||got code? see Brett's sticky at the top of the page. It sounds like you have an application bug dealing with concurrency.

Exactly the problem! We found a bug in the code and it was a concurrency issue.

Thanks for your help!

Cheers,
Jon_|||I am awesome at blind chess but not chess with the blindman|||Well, I read the title and I was thinking that some sort of initimate relationship manual might come in handy

Insert Problem

I am fairly new to SQL and have been given a horrible task. I have 2 tables, table 1 is eng_routing and table 2 routing the rows in table 1 are
engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 30 now machine here
1 40 end

the rows in table 2 are

part opnumber rdesc
1 10 goodsin
1 20 treat
1 30 final

the task I have been given is to insert the ops from table 2 into table 1 where engpart=part and where table 1 has a treatments, but the data from table 2 needs to be inserted at the next number after treatments (in this example opnumber 21 and so on) for all opnumber and rdesc,so the outcome would look like

engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 21 goodsin
1 22 treat
1 23 final
1 30 now machine here
1 40 end

I have been given the following code but an error occurs on line 7 on keyword 'where'

use efacdb
begin tran
INSERT INTO eng_routing
SELECT routing.part, TBL3.engopnumber + (SELECT count(opnumber) FROM routing T1
Where T1.part = T.Part AND T1.opnumber<=T.opnumber ) opnumber ,routing.rdesc FROM routing T
INNER JOIN (SELECT engpart,engopnumber,engrdesc FROM eng_routing
WHERE eng_routing.engrdesc= 'treatments') TBL3 where T.part=TBL3.engpart

select * from eng_routing
--commit tran
--rollback tran

Any help would be gratefully recieved--hope this is what you are after, if not my appologies

--first select distinct opnumber into temp table
select distinct opnumber into #c from #b

--now you should be able to do your insert from a select statement like
--this
SELECT b.part, a.engopnumber + (SELECT count(c.opnumber) FROM #c c
Where b.opnumber>=c.opnumber ) opnumber ,b.rdesc FROM eng_routing a, routing b
where a.engrdesc= 'treatments'
order by opnumber|||Thanks for your quick reply. The code falls over on line 1 Invalid object name #b. Thanks for your help|||if you cant figure to replace #b with routing

INSERT problem

I'm not sure if this is the right place to post this, if not, maybe
someone could give me a pointer to where to ask the question...
A friend and I are trying to figure out if it's possible to create a
table that will allow an insert of a value into column A only if that
value is already in column B or if it being inserted into column B by
that insert statement.
We've done some looking around, but haven't found an answer yet...Sounds like you want a FOREIGN KEY constraint on a self-referencing table:
create table MyTable
(
ColA int primary key
, ColB int not null
references MyTable (ColA)
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jakanapes" <Jakanapes@.aol.com> wrote in message
news:1130156166.728791.38140@.g49g2000cwa.googlegroups.com...
I'm not sure if this is the right place to post this, if not, maybe
someone could give me a pointer to where to ask the question...
A friend and I are trying to figure out if it's possible to create a
table that will allow an insert of a value into column A only if that
value is already in column B or if it being inserted into column B by
that insert statement.
We've done some looking around, but haven't found an answer yet...|||Yes, you can use an INSERT trigger for that, using the virtual inserted table inside your trigger
code. Or possibly a self referencing foreign key, if column B is a key of the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jakanapes" <Jakanapes@.aol.com> wrote in message
news:1130156166.728791.38140@.g49g2000cwa.googlegroups.com...
> I'm not sure if this is the right place to post this, if not, maybe
> someone could give me a pointer to where to ask the question...
> A friend and I are trying to figure out if it's possible to create a
> table that will allow an insert of a value into column A only if that
> value is already in column B or if it being inserted into column B by
> that insert statement.
> We've done some looking around, but haven't found an answer yet...
>

Insert Primary Keys to all tables in a database

I have a database of 300 tables that needed to insert Primary Key:
1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
RPT_NAME.
Is there a fast way to insert it once and for all rather than inserting
it one by one?
Another issue is to change the width of field named COST_CENTR of
VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Currently using MsSQL 2K. Help is greatly appreciated. ThanksYou can speed this up by scripting.
You can derive every table and column name from various system calls, e.g
use informationschema for tables and
exec sp_columns 'myTable' will list the column names.
You could add some logic to check the coloumn names and then perform an
action , such as add PK
A similar process as above for your second problem - check for column
COST_CENTR and do an ALTER TABLE to widen the col width
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ymcj" <june.ymc@.gmail.com> wrote in message
news:1145520225.026547.254790@.u72g2000cwu.googlegroups.com...
> I have a database of 300 tables that needed to insert Primary Key:
> 1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
> 2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
> RPT_NAME.
> Is there a fast way to insert it once and for all rather than inserting
> it one by one?
> Another issue is to change the width of field named COST_CENTR of
> VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
> Currently using MsSQL 2K. Help is greatly appreciated. Thanks
>|||On 20 Apr 2006 01:03:45 -0700, ymcj wrote:

>I have a database of 300 tables that needed to insert Primary Key:
>1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
>2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
>RPT_NAME.
Hi ymcj,
This is a very strange request. If alll tables share the same primary
key, then why is your data spread out over 300 tables? The idea of
normalization is to use different tables for data that needs DIFFERENT
keys.
Can you elaborate a bit on what you're trying to achieve and why?
(snip)
>Another issue is to change the width of field named COST_CENTR of
>VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Execute the following SQL in Query Analyzer (using the results to text
option instead of the results to grid option!!)
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '
' + 'ALTER COLUMN ' + COLUMN_NAME + ' varchar(12)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'COST_CENTR'
Check the result. If it looks okay, select it, copy and paste it to the
query window and execute it.
Test this on a test database first. After that, make sure that you
perform this task during scheduled down time, and make sure that you
have a good backup.
Hugo Kornelis, SQL Server MVP

Insert picture

Access allows to insert manually an image into OLE Object type field very
easy. I was wondering if there is a simple way to insert an image into image
type field in SQL Server using Enterprise Manager (not programmatically)
Thank you
Al
Hi
No, EM does not support it. You need to use an application to do it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vul" <aaa@.optonline.net> wrote in message
news:%23skF6VWDGHA.3920@.tk2msftngp13.phx.gbl...
> Access allows to insert manually an image into OLE Object type field very
> easy. I was wondering if there is a simple way to insert an image into
> image
> type field in SQL Server using Enterprise Manager (not programmatically)
> Thank you
> Al
>

Insert performance/nvarchar

hello,
i wrote a performance test for sequential inserts with ado.net on a P4 2GHz
512 Meg Ram machine
and got the following scores:
insert 10000 ints 1:30 mins
insert 10000 reals 1:20 mins
inserting 10000 nvarchars
first 10000: 1:30
second 10000: 4:11
third 10000: 6:50
fourth 10000: 9:30
fifth 10000: 12:12
sixth 10000: 15:00
seventh 10000 18:20
so the times gets worse and worse.
i would expect, that the convergate but they don't
Is this normal?
If yes we will have problems, because we expect a couple of millions entries
in this
table where this strings are stored.
all tables for the performance test have the same stucture and indices
except of
the datatype which is tested, which is
id
value
Can you give me a hint how to speed this?
thanks mikeDo you have your databases auto-growing during these tests, or did you set
the files to a large enough size before the tests to ensure that they
wouldn't grow?
Do you have the columns indexed? Are the inserts causing page splits?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> hello,
> i wrote a performance test for sequential inserts with ado.net on a P4
2GHz
> 512 Meg Ram machine
> and got the following scores:
> insert 10000 ints 1:30 mins
> insert 10000 reals 1:20 mins
> inserting 10000 nvarchars
> first 10000: 1:30
> second 10000: 4:11
> third 10000: 6:50
> fourth 10000: 9:30
> fifth 10000: 12:12
> sixth 10000: 15:00
> seventh 10000 18:20
> so the times gets worse and worse.
> i would expect, that the convergate but they don't
> Is this normal?
> If yes we will have problems, because we expect a couple of millions
entries
> in this
> table where this strings are stored.
> all tables for the performance test have the same stucture and indices
> except of
> the datatype which is tested, which is
> id
> value
> Can you give me a hint how to speed this?
> thanks mike|||Hello Adam,
yes its auto-growing
yes columns are indexed
most inserts are NOT causing page splits
thanks mike
"Adam Machanic" wrote:
> Do you have your databases auto-growing during these tests, or did you set
> the files to a large enough size before the tests to ensure that they
> wouldn't grow?
> Do you have the columns indexed? Are the inserts causing page splits?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > hello,
> >
> > i wrote a performance test for sequential inserts with ado.net on a P4
> 2GHz
> >
> > 512 Meg Ram machine
> >
> > and got the following scores:
> >
> > insert 10000 ints 1:30 mins
> > insert 10000 reals 1:20 mins
> >
> > inserting 10000 nvarchars
> > first 10000: 1:30
> > second 10000: 4:11
> > third 10000: 6:50
> > fourth 10000: 9:30
> > fifth 10000: 12:12
> > sixth 10000: 15:00
> > seventh 10000 18:20
> >
> > so the times gets worse and worse.
> > i would expect, that the convergate but they don't
> > Is this normal?
> >
> > If yes we will have problems, because we expect a couple of millions
> entries
> > in this
> > table where this strings are stored.
> >
> > all tables for the performance test have the same stucture and indices
> > except of
> > the datatype which is tested, which is
> > id
> > value
> >
> > Can you give me a hint how to speed this?
> >
> > thanks mike
>
>|||You'll get more consistent results if you grow the file first...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> Hello Adam,
> yes its auto-growing
> yes columns are indexed
> most inserts are NOT causing page splits
> thanks mike
>
> "Adam Machanic" wrote:
> > Do you have your databases auto-growing during these tests, or did you
set
> > the files to a large enough size before the tests to ensure that they
> > wouldn't grow?
> >
> > Do you have the columns indexed? Are the inserts causing page splits?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > hello,
> > >
> > > i wrote a performance test for sequential inserts with ado.net on a P4
> > 2GHz
> > >
> > > 512 Meg Ram machine
> > >
> > > and got the following scores:
> > >
> > > insert 10000 ints 1:30 mins
> > > insert 10000 reals 1:20 mins
> > >
> > > inserting 10000 nvarchars
> > > first 10000: 1:30
> > > second 10000: 4:11
> > > third 10000: 6:50
> > > fourth 10000: 9:30
> > > fifth 10000: 12:12
> > > sixth 10000: 15:00
> > > seventh 10000 18:20
> > >
> > > so the times gets worse and worse.
> > > i would expect, that the convergate but they don't
> > > Is this normal?
> > >
> > > If yes we will have problems, because we expect a couple of millions
> > entries
> > > in this
> > > table where this strings are stored.
> > >
> > > all tables for the performance test have the same stucture and indices
> > > except of
> > > the datatype which is tested, which is
> > > id
> > > value
> > >
> > > Can you give me a hint how to speed this?
> > >
> > > thanks mike
> >
> >
> >|||hello adam,
nope,
i dropped the old database, created a new one with fixed size
600 meg (for data and tranlog).
The behaviour is still the same.
The insert times are growing endless.
greetings mike
"Adam Machanic" wrote:
> You'll get more consistent results if you grow the file first...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > Hello Adam,
> >
> > yes its auto-growing
> > yes columns are indexed
> >
> > most inserts are NOT causing page splits
> >
> > thanks mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > Do you have your databases auto-growing during these tests, or did you
> set
> > > the files to a large enough size before the tests to ensure that they
> > > wouldn't grow?
> > >
> > > Do you have the columns indexed? Are the inserts causing page splits?
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > hello,
> > > >
> > > > i wrote a performance test for sequential inserts with ado.net on a P4
> > > 2GHz
> > > >
> > > > 512 Meg Ram machine
> > > >
> > > > and got the following scores:
> > > >
> > > > insert 10000 ints 1:30 mins
> > > > insert 10000 reals 1:20 mins
> > > >
> > > > inserting 10000 nvarchars
> > > > first 10000: 1:30
> > > > second 10000: 4:11
> > > > third 10000: 6:50
> > > > fourth 10000: 9:30
> > > > fifth 10000: 12:12
> > > > sixth 10000: 15:00
> > > > seventh 10000 18:20
> > > >
> > > > so the times gets worse and worse.
> > > > i would expect, that the convergate but they don't
> > > > Is this normal?
> > > >
> > > > If yes we will have problems, because we expect a couple of millions
> > > entries
> > > > in this
> > > > table where this strings are stored.
> > > >
> > > > all tables for the performance test have the same stucture and indices
> > > > except of
> > > > the datatype which is tested, which is
> > > > id
> > > > value
> > > >
> > > > Can you give me a hint how to speed this?
> > > >
> > > > thanks mike
> > >
> > >
> > >
>
>|||Can you post the table definitions, including constraints and indexes?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> hello adam,
> nope,
> i dropped the old database, created a new one with fixed size
> 600 meg (for data and tranlog).
> The behaviour is still the same.
> The insert times are growing endless.
> greetings mike
>
> "Adam Machanic" wrote:
> > You'll get more consistent results if you grow the file first...
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > Hello Adam,
> > >
> > > yes its auto-growing
> > > yes columns are indexed
> > >
> > > most inserts are NOT causing page splits
> > >
> > > thanks mike
> > >
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > Do you have your databases auto-growing during these tests, or did
you
> > set
> > > > the files to a large enough size before the tests to ensure that
they
> > > > wouldn't grow?
> > > >
> > > > Do you have the columns indexed? Are the inserts causing page
splits?
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
message
> > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > hello,
> > > > >
> > > > > i wrote a performance test for sequential inserts with ado.net on
a P4
> > > > 2GHz
> > > > >
> > > > > 512 Meg Ram machine
> > > > >
> > > > > and got the following scores:
> > > > >
> > > > > insert 10000 ints 1:30 mins
> > > > > insert 10000 reals 1:20 mins
> > > > >
> > > > > inserting 10000 nvarchars
> > > > > first 10000: 1:30
> > > > > second 10000: 4:11
> > > > > third 10000: 6:50
> > > > > fourth 10000: 9:30
> > > > > fifth 10000: 12:12
> > > > > sixth 10000: 15:00
> > > > > seventh 10000 18:20
> > > > >
> > > > > so the times gets worse and worse.
> > > > > i would expect, that the convergate but they don't
> > > > > Is this normal?
> > > > >
> > > > > If yes we will have problems, because we expect a couple of
millions
> > > > entries
> > > > > in this
> > > > > table where this strings are stored.
> > > > >
> > > > > all tables for the performance test have the same stucture and
indices
> > > > > except of
> > > > > the datatype which is tested, which is
> > > > > id
> > > > > value
> > > > >
> > > > > Can you give me a hint how to speed this?
> > > > >
> > > > > thanks mike
> > > >
> > > >
> > > >
> >
> >
> >|||Hello Adam,
here comes the table
create table LogStringTable
(
ID int identity (1,1) not null,
stringValue nvarchar(400) not null,
attributeTypeId int not null,
logItemId int not null
constraint FKATIhasStringValues
foreign key ( attributeTypeId )
references LogAttributeType (Id),
constraint FKItemHasStringvalues
foreign key (LogItemId )
references LogItem ( Id )
) on primary
there are four indices
primary key index on Id (clustered)
and an the other columns (not unique)
thank you mike
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hello Adam,
I dropped all indices and tried again
-> nothing principaly changed.
The times are shorter, but they are still growing endless,
with each 10000 insert.
When I have 100.000 entries in that table than the performance is reduce to
about
15 inserts/second compared with 100 inserts/second when starting with a
blank table.
And the performance goes down and down.
Greets mike
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||hello adam,
i know now, that it is not a problem if the sql server.
it has to do with ado.net.
currently i don't know what it is, but now I inserted 10.000 nvarchars with
the
query analyzer and it lasts about 5 seconds
regardless how many records are in the table.
so i have to look into the ado.net stuff.
thank you for your help
I'll let you know what is is, when i know it.
thank you very much
greets michael
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hello Adam,
I got it.
There is an option at the data adapter called
refresh the dataset
This was set to true.
I set it to false and now my world is perpendicular again.
The insert times for 10.000 stings are now about 5-7 seconds.
Unfortunatly, I even didn't use a dataset, so this option is useless even
when set to true.
I think this is worthy a microsoft call.
thank you for your help.
mike.
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Thanks for all of the updates; this is good to know! Can you post an
abbreviated version of the code you were using? How does a dataset option
affect you when you're not using a dataset? And how did you turn it off
without using a dataset?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:EBAADD96-9F27-4A71-AC21-B8487352237C@.microsoft.com...
> Hello Adam,
> I got it.
> There is an option at the data adapter called
> refresh the dataset
> This was set to true.
> I set it to false and now my world is perpendicular again.
> The insert times for 10.000 stings are now about 5-7 seconds.
> Unfortunatly, I even didn't use a dataset, so this option is useless even
> when set to true.
> I think this is worthy a microsoft call.
> thank you for your help.
> mike.
>
> "Adam Machanic" wrote:
> > Can you post the table definitions, including constraints and indexes?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > > hello adam,
> > >
> > > nope,
> > >
> > > i dropped the old database, created a new one with fixed size
> > > 600 meg (for data and tranlog).
> > >
> > > The behaviour is still the same.
> > >
> > > The insert times are growing endless.
> > >
> > > greetings mike
> > >
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > You'll get more consistent results if you grow the file first...
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
message
> > > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > > Hello Adam,
> > > > >
> > > > > yes its auto-growing
> > > > > yes columns are indexed
> > > > >
> > > > > most inserts are NOT causing page splits
> > > > >
> > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > > > > "Adam Machanic" wrote:
> > > > >
> > > > > > Do you have your databases auto-growing during these tests, or
did
> > you
> > > > set
> > > > > > the files to a large enough size before the tests to ensure that
> > they
> > > > > > wouldn't grow?
> > > > > >
> > > > > > Do you have the columns indexed? Are the inserts causing page
> > splits?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Adam Machanic
> > > > > > SQL Server MVP
> > > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > > --
> > > > > >
> > > > > >
> > > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> > message
> > > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > > hello,
> > > > > > >
> > > > > > > i wrote a performance test for sequential inserts with ado.net
on
> > a P4
> > > > > > 2GHz
> > > > > > >
> > > > > > > 512 Meg Ram machine
> > > > > > >
> > > > > > > and got the following scores:
> > > > > > >
> > > > > > > insert 10000 ints 1:30 mins
> > > > > > > insert 10000 reals 1:20 mins
> > > > > > >
> > > > > > > inserting 10000 nvarchars
> > > > > > > first 10000: 1:30
> > > > > > > second 10000: 4:11
> > > > > > > third 10000: 6:50
> > > > > > > fourth 10000: 9:30
> > > > > > > fifth 10000: 12:12
> > > > > > > sixth 10000: 15:00
> > > > > > > seventh 10000 18:20
> > > > > > >
> > > > > > > so the times gets worse and worse.
> > > > > > > i would expect, that the convergate but they don't
> > > > > > > Is this normal?
> > > > > > >
> > > > > > > If yes we will have problems, because we expect a couple of
> > millions
> > > > > > entries
> > > > > > > in this
> > > > > > > table where this strings are stored.
> > > > > > >
> > > > > > > all tables for the performance test have the same stucture and
> > indices
> > > > > > > except of
> > > > > > > the datatype which is tested, which is
> > > > > > > id
> > > > > > > value
> > > > > > >
> > > > > > > Can you give me a hint how to speed this?
> > > > > > >
> > > > > > > thanks mike
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Friday, February 24, 2012

Insert Query - Please help

Insert Query - Please help

Insert Query - Please help

Insert Query - Basic Question

Insert Query

Insert Query

Insert Query

insert query

Insert Query

Insert querry

Insert produces error

Insert procedure in two tables with Foreign Key relation ship

Insert procedure from Excel

Insert Proc With Both Select And Values

Insert Problem.

Insert Problem.

Insert problem with single quotes

Insert problem with linked server

Insert Problem in MS SQL

INSERT problem

INSERT problem

insert problem

INSERT Problem

Insert Problem

INSERT problem

Insert Primary Keys to all tables in a database

Insert picture

Insert performance/nvarchar

Insert performance/nvarchar

Insert performance/nvarchar

insert or replace

Blog Archive

About Me