How to copy for test data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to copy for test data

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-12-08 : 11:48:58

I have a demo database but i need to get more robust data from the live database. I want to copy transactions data and just change the customer id once its over.
How to make this basic copy from one database to the other and at the same time change the customer id?

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:55:14

[code]
INSERT INTO dbo.TestDatabase
(
MyID, Col1, Col2, Col3, ...
)
SELECT MyID = ... some formula ...,
Col1, Col2, Col3, ...
FROM dbo.LiveDatabase
[/code]
We change all Customer Email addresses to be CustomerID@OurTestDomain.com too - if we generate emails (by accident say) they will come to our email server, not the customer!

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-12-08 : 16:14:30

HI I am having a problem getting this to run:

insert into KPI.Transactions
(customerid, TransactionId, DepartmentId, ItemId, CategoryId, Quantity, Cost, DateCreated, InvoiceNumber, DataSource, DataSourceId, ImportId, LastUpdate)
select customerid = 104, TransactionId, DepartmentId, ItemId, CategoryId, Quantity, Cost, DateCreated, InvoiceNumber, DataSource, DataSourceId, ImportId, LastUpdate,
from SpendAnalyzer.transactions

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 16:21:12

You've got a trailing comma on the end of the line:

... ImportId, LastUpdate,

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-12-08 : 23:00:18

I am trying to copy the rows from the tables in database spendanalyzer to database KPI to put then in customer id in KPI from customerid 7 in Spendanalyzer - the following code is getting an error" Msg 102, Level 15, State 1, Line 4Incorrect syntax near '1'."SET IDENTITY_INSERT [KPI].[dbo].[Transactions] ONGOinsert into KPI.dbo.Transactions (customerid = 1, TransactionId, DepartmentId, ItemId, CategoryId, Quantity, Cost, DateCreated, InvoiceNumber, DataSource, DataSourceId, ImportId, LastUpdate)select customerid = 7, TransactionId, DepartmentId, ItemId, CategoryId, Quantity, Cost, DateCreated, InvoiceNumber, DataSource, DataSourceId, ImportId, LastUpdatefrom SpendAnalyzer.dbo.transactions

quote:
Originally posted by Kristen

You've got a trailing comma on the end of the line:

... ImportId, LastUpdate,

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 02:30:34

You cannot have an assignment in a list of column names in an INSERT statement

insert into KPI.dbo.Transactions (customerid ~~= 1~~, TransactionId,

bhawana
Starting Member

4 Posts

Posted - 2010-12-09 : 04:42:51

Instead OF
select customerid = 1
Use
select Customerid as 1

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 05:16:23

There is nothing wrong with using:

select customerid = 1

as an alternative to

select 1 as Customerid

(I expect you did not mean to write "select Customerid as 1")

and that is not the source of the error here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources