Copy SP
-
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?
-
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] -
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).
-
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] -
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.
-
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] -
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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply