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,
[posted and mailed, please reply in news] soni29 (soni29@hotmail.com) writes: [quoted text, click to view] > 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, sommar@algonet.se Books Online for SQL Server SP3 at
Hi Check out the insert syntax in books online (use the Go/URL menus!): mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\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%20Server\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%20Server\80\Tools\Books\acd ata.chm::/ac_8_qd_11_3smm.htm John [quoted text, click to view] "soni29" <soni29@hotmail.com> wrote in message
news:cad7a075.0312140855.7c8b2475@posting.google.com...
> 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.
[quoted text, click to view] "Erland Sommarskog" <sommar@algonet.se> wrote in message news:Xns9451C9DBAF97CYazorman@127.0.0.1... > [posted and mailed, please reply in news] > > soni29 (soni29@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, sommar@algonet.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/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
Simon Hayes (sql@hayes.ch) writes: [quoted text, click to view] > 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, sommar@algonet.se Books Online for SQL Server SP3 at
Don't see what you're looking for? Try a search.
|
