By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,839 Members | 2,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,839 IT Pros & Developers. It's quick & easy.

insert statement help

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
[posted and mailed, please reply in news]

soni29 (so****@hotmail.com) writes:
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.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
Hi

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

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
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%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_na-nop_4pt0.htm

i.e.

CREATE TABLE cust
(
id uniqueidentifier 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%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_11_3smm.htm

John

"soni29" <so****@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...
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.

Jul 20 '05 #3

P: n/a

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...
[posted and mailed, please reply in news]

soni29 (so****@hotmail.com) writes:
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.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


A minor correction - the syntax above will generate a new uniqueidentifier
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
Jul 20 '05 #4

P: n/a
Simon Hayes (sq*@hayes.ch) writes:
A minor correction - the syntax above will generate a new uniqueidentifier
for each row in the source table before applying the DISTINCT, so you will
get all the values from the source table anyway.


Oops!

Thanks for the correction, Simon!


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.