Copy SP

Question

Copy SP

yaip

SSCrazy

Points: 2656
More actions
December 15, 2004 at 6:51 pm

#87246

I have a table tbl1 that has the following fields:
id (pk)
fname
lname
ref#
I also have a second table - tbl2 that has following fields:
id (pk)
fname
lname
I want a simple SP that will create records in tbl2 based on the records selected in tbl1 (say, ref#='123').
How would I do that?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Have a look at INSERT INTO...SELECT in BOL. If the PK has the IDENTITY property defined on the column, you might need to use SET IDENTITY_INSERT ON, which is also explained in BOL.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

yaip SSCrazy Points: 2656 More actions · Answer 2

I have

INSERT tbl2

SELECT id,fname,lname from tbl1

WHERE ref# = '123'

This works fine but what if I just wanted to insert fname and leave lname out (which would insert NULLS in that column).

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

INSERT INTO tbl2 (fname) SELECT fname FROM tbl1 WHERE ref#='123'

Btw, the # sign isn't really good to be used as a column name. I have seen several threads here, where this caused more or less severe problems.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

yaip SSCrazy Points: 2656 More actions · Answer 4

I tried that but it didn't like it. I don't have the error with me right now but I'll post the error I got.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

If your column don't allow for NULLs, you have to change them to allow for NULLs, or insert a dummy value. But what are you really after???

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

yaip SSCrazy Points: 2656 More actions · Answer 6

Nothing. I was just trying to understand "nuances" of sql server. I have tried this on DB/2 and have been successful in past. I guess it must be the syntax. (Or I must be doing something really stupid - which won't surprise me)