SQL Statement

[Mischamel]

I need an SQL Statement that Selects from source table into target. I did the following :

 

SELECT * INTO TargTable FROM SoTable Where TID = 'value'

 

It just works fine for a non existing table. But i´ll have to do it several times in an existing table.

 

Any ideas ??

[PlausiblyDamp]

Have a look in the help for Insert Select

 

IIRC

Insert INTO

select * from

Edited November 14, 2003 by PlausiblyDamp

[Mischamel]

what do you mean ?? Is there a tutorial or something else, where i can look for ???

[PlausiblyDamp]

If you have SQL installed check the books online for the INSERT ... SELECT topic.

[Robby]

Check these out...

http://www.w3schools.com/sql/sql_insert.asp

http://www.w3schools.com/sql/sql_select_into.asp

[mocella]

From your example, the syntax is:

INSERT INTO TargTable

SELECT * FROM SoTable

Where TID = 'value'

 

This of course assumes that TargTable and SoTable have the same layout, otherwise, you'd have to replace "*" with the list of columns you want the data from. In fact, you may have to do that anyway, I haven't tried this in a while.

[Robby]

I'm pretty sure that SQL Server you cannot use the * for an insert

[mocella]

Just tried it, you can use "*" if the layout is the same, although select "*" is never a best-practice. I just used it here since I didn't have a column listing in his example.

[Robby]

I just tried it and in did not work.

[mocella]

And your tables had the same field layout? I used two tables with a single Char(10) column and put one record into Table1, then ran:

insert into dbo.Table2

select * from dbo.Table1

 

 

and checked and found one row in Table2. Weird that it didn't work for you - maybe it depends on column types for this?

[Robby]

I grabed a table made a copy the did the Insert.

 

It works only when you remove any Identity from a column ( which I had)

[mocella]

Ah, that makes sense then.

 

I actually had an issue yesterday with a DTS pack not working because of an Identity column. Gave a lovely "Unspecified Error" message when it didn't work. So I had to figure out why one out of 5 tables wouldn't load and that's what it came down to. Damn Identities!

[Robby]

Especially when it's a PK/FK in one of the DTS items.