insert statement help

[soni29]

hi,
i have a small question regarding sql, there are two tables that i
need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more
columns
Table2:
name
what i need help with is that table2 contains about 200 distinct names
that i need to insert into table1, i'm using sql server, is there a
way to insert them into table1?? i'm not sure how to write a query
within the insert statment to get them inserted into table1?
something like:
insert into table(id, name, street, zip, phone, fax, ...)
values(newid(), (select distinct name from table2), null, null,
null....)
and is there a way to do it without all the nulls having to be put in,
there are about 20 more columns in table1, and id in table1 is unique.

[Erland Sommarskog]

Re: insert statement help

[posted and mailed, please reply in news]

soni29 (soni29@hotmail .com) writes:[color=blue]
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null....)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.[/color]

Your question is a bit vague, and since I don't see the tables, nor do
I see the data, I have to guess.

If all you want to is to insert the disctinct names in table2 into table1,
without providing any values for the other columns, save the id column,
this is the statement:

INSERT table1 (id, name)
SELECT disctint newid(), name FROM table2

Thus, you do need to list a column in the column list of the INSERT
statement, if you wish to set it to NULL or its default value.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: insert statement help

Hi

Check out the insert syntax in books online (use the Go/URL menus!):

mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ac d
ata.chm::/ac_8_md_03_1kz8 .htm

If the columns are nullable and don't have a value or if the are not
nullable and take the default then you do not have to mention them in the
select statement. If they are nullable then the DEFAULT keyword can be used.

To create a default for your id column then it can be declare with a default
see:

mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ts q
lref.chm::/ts_na-nop_4pt0.htm

i.e.

CREATE TABLE cust
(
id uniqueidentifie r NOT NULL
DEFAULT newid(),
.....

)
GO

Therefore you can do something like:

insert into table(name, street, zip, phone, fax)
select distinct name, street, zip, phone, fax from table2

If you can not get distinct from this then you may need a subquery such as:

mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ac d
ata.chm::/ac_8_qd_11_3smm .htm

John

"soni29" <soni29@hotmail .com> wrote in message
news:cad7a075.0 312140855.7c8b2 475@posting.goo gle.com...[color=blue]
> hi,
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null....)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.[/color]

[Simon Hayes]

Re: insert statement help


"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns9451C9D BAF97CYazorman@ 127.0.0.1...[color=blue]
> [posted and mailed, please reply in news]
>
> soni29 (soni29@hotmail .com) writes:[color=green]
> > i have a small question regarding sql, there are two tables that i
> > need to work with on this, one has fields like:
> > Table1:
> > (id, name, street, city, zip, phone, fax, etc...) about 20 more
> > columns
> > Table2:
> > name
> > what i need help with is that table2 contains about 200 distinct names
> > that i need to insert into table1, i'm using sql server, is there a
> > way to insert them into table1?? i'm not sure how to write a query
> > within the insert statment to get them inserted into table1?
> > something like:
> > insert into table(id, name, street, zip, phone, fax, ...)
> > values(newid(), (select distinct name from table2), null, null,
> > null....)
> > and is there a way to do it without all the nulls having to be put in,
> > there are about 20 more columns in table1, and id in table1 is unique.[/color]
>
> Your question is a bit vague, and since I don't see the tables, nor do
> I see the data, I have to guess.
>
> If all you want to is to insert the disctinct names in table2 into table1,
> without providing any values for the other columns, save the id column,
> this is the statement:
>
> INSERT table1 (id, name)
> SELECT disctint newid(), name FROM table2
>
> Thus, you do need to list a column in the column list of the INSERT
> statement, if you wish to set it to NULL or its default value.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

A minor correction - the syntax above will generate a new uniqueidentifie r
for each row in the source table before applying the DISTINCT, so you will
get all the values from the source table anyway. Something like this should
work correctly:

insert into table1 (id, name)
select newid(), name from
(
select distinct name
from table2 ) dt

Although as you pointed out, without seeing data and DDL, it's not at all
clear what 'correctly' means here, so my version may not be what the poster
wants either.

Simon

[Erland Sommarskog]

Re: insert statement help

Simon Hayes (sql@hayes.ch) writes:[color=blue]
> A minor correction - the syntax above will generate a new uniqueidentifie r
> for each row in the source table before applying the DISTINCT, so you will
> get all the values from the source table anyway.[/color]

Oops!

Thanks for the correction, Simon!




--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp