Creating a table from data in a SQL server db

I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!
Thanks in advance,
Chrisyou could create a dataset/datatable at the front end with the samestructure as the actual table in the db. You can then use thedataset/datatable and do all the modifications and push the entiretable back to the sql db. check out articles about datasets and youwould find enough info.
sql

Creating a loop

Hi

I need to create an SQL table and automatically populate it with 100,000 records (just one column).

How can I achieve this? The create table part is straight forward enough but how can I get all those rows in there using a single script?

I imagine the statement will require Loop and While.

This is for testing purposes.declare @.i int
set @.i=100000
while @.i>0
begin
insert into table_name values (@.i)
set @.i=@.i-1
end|||Much appreciated!
I'm running this against a table I've already created and I'm getting..
"Server: Msg 213, Level 16, State 4, Line 5
Insert Error: Column name or number of supplied values does not match table definition." :confused:|||can u paste the ddl for the table? how many columns does the table have?
replace the insert with:
insert into table_name (column_name) values (@.i)|||No worries - I got it to work in the end - I created a new table with just one column and everything is fine.

Thanks for your help amigo/amiga..|||hi Harshal
I'm hoping you can help with a problem leading on from this. The purpose of this exercise was to measure how long two scenarios take to create the tables and insert records.

Scenario A : Stand alone desktop
Scenario B : Server with dual processor Xeon

The database on each is identical - yet the Desktop took 01:07 to insert compared to the server's 06:48 !! Nearly 7 minutes!

Any ideas what could be causing this??

Cheers

Samsara

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||Your welcomes. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a 3.1 SDF from MDB

I am trying to populate a new SDF database to be deployed with my CF application, and am totally bummed to find out I have to manually code my own custom app just to import the data from the existing desktop DB with ado.net code.

I don't want to write code so that I can write code!

Custom apps to support the development of custom apps!

Jordan

I wrote a commercial application for this (you can try it for free for 30 days, though). You can download it from my company's website (look for Data Port Wizard).|||

Jo?o Paulo Figueira wrote:

I wrote a commercial application for this (you can try it for free for 30 days, though). You can download it from my company's website (look for Data Port Wizard).

I did try this app, (I was really lazy and desperately trying not to have to write any code :), but it kept saying it wasn't able to open the file. I did check "local", because the SDF was on my PC, not the PDA. I finally came to the conclusion that it did not support SQL Server CE 3.x, because 2.x was the only one referenced.

So I gave up and wrote a really quick and dirty app to transfer the data over. However, I've got to do a lot more transferring to do, and rather than modifying my data xfer app, I'd rather use your utility if I can get it to work.

Jordan